Posted by Erland Sommarskog on 12/02/06 23:39

Amy Smith (amoura00@hotmail.com) writes:
> the end time after mindnight will be always 6:00:00AM so from 00:00:00
> AM to 6:00:00 AM , it should go to the pervious day's shift 2 . Again my
> issue is when finding the that the start time fall withing that time how
> do I pretend the date as the previous date? Do I subtract the date ? Is
> there a function to do that ? thanks Roy

convert(char(8), dateadd(HOUR, -6, EndTime), 112)

The dateadd() functions brings the EndTime six hours back in time. The
convert to char(8) with code 112 strips of the time portion.

You can read more about dateadd and other datetime functions in Books
Online. You can also read about convert() under the topic CAST and CONVERT.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]