Useing servertime to set shift/rundate

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

SQL Server 2000 Forums

SQL Server Development (2000)

Useing servertime to set shift/rundate

Author

Topic

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2003-08-12 : 13:29:07

I need to adjust a run date based upon the time of day(at the server) and the shift that is worked.

I am having a little trouble getting the “between” times statement correct.

It should go something like this.

Rundate = Case When (current server time is between 12:00:01 AM and 4:00:00 AM) And Shift = ‘2’ Then
convert(varchar(10),getdate(),101) – 1

Case When (current server time is between 4:00:00 PM and 12:00:00 PM) And Shift = ‘3’ Then
convert(varchar(10),getdate(),101) + 1

Else
convert(varchar(10),getdate(),101)
End

Please Help Brain still on vacation.

Jim
Users <> Logic

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-08-12 : 13:38:31

Well current server time would be GETDATE(). GETDATE() is not based upon the client. I don't see what part that you are having problems with.

Tara

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2003-08-12 : 13:46:24

I can’t seem to figure out the syntax for the brown statement.

I have never done any statements in SQL based on time.

In VB it would look something like this.

If (Eval("[shift]=2 And [ServerTime] Between #0:0:1# And #4:00:0#")) Then
[Run Date] = date - 1
End If

Jim
Users <> Logic

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-08-12 : 13:57:33

Something like this:


select Rundate = Case When (convert(varchar(50), getdate(), 108) between '12:00:01' and '04:00:00') Then
convert(varchar(10),getdate() - 1, 101) 

When (convert(varchar(50), getdate(), 108) between '16:00:00' and '23:59:59') Then
convert(varchar(10),getdate() + 1, 101)

Else
convert(varchar(10),getdate(), 101)
End

Tara

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2003-08-12 : 14:15:17

Thanks Tara

I was close once, didint know to ' ' the time ('12:00:01').

Works superb, as always when the princess helps me out.

Thanks Again

Jim
Users <> Logic

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources