How to redefine a day SQL 2000

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to redefine a day SQL 2000

Author

Topic

vua_rua
Starting Member

14 Posts

Posted - 2011-12-09 : 12:12:10

Hi everybody

I have a question and need your help.

I define a day (start at 6Am of first day and end at 05:59AM of next day)
For example:
I defined a day 2011-01-01:
Start: 2011-01-01 06:00:00
End: 2011-01-02 05:59:00

Now i have some data
No MTDate MTTime
1 | 2011-01-01 2011-01-01 07:00:00
2 | 2011-01-02 2011-01-02 05:00:00
3 | 2011-01-01 2011-01-01 08:00:00
4 | 2011-01-02 2011-01-02 05:50:00

How can I update all data have Start <= MTTime <= End
as below

No MTDate MTTime
1 | 2011-01-01 |2011-01-01 07:00:00
2 | 2011-01-01 |2011-01-02 05:00:00
3 | 2011-01-01 |2011-01-01 08:00:00
4 | 2011-01-01 |2011-01-02 05:50:00

I'm newbie so thank you so much

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-09 : 12:24:48

[code]
UPDATE t
SET t.MTDate =CASE WHEN DATEPART(hh,MTTime)>=6 THEN t.MTDate ELSE DATEADD(dd,-1,t.MTDate) END
FROM table t
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

vua_rua
Starting Member

14 Posts

Posted - 2011-12-09 : 12:39:35

quote:
Originally posted by visakh16

UPDATE t
SET t.MTDate =CASE WHEN DATEPART(hh,MTTime)>=6 THEN t.MTDate ELSE DATEADD(dd,-1,t.MTDate) END
FROM table t

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Thank you so much. I did.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-10 : 07:42:27

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources