Retrieve midnight date and time
I need to retrieve todays date from sql server 2000 database but instead of current time I need midnight. How can I do that?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Yes can see how that might be argued, and as a good intellectual debate, try this... if you add 2 seconds to that 00:00:00.000 time and it is midnight, then would you expect the date to roll over say from the 1st to the 2nd ?
select dateadd(ss,2, convert(datetime,'01 Aug 2008 00:00:00.000'))
That's why I say it does not really exist ! where as :
select dateadd(ss,2, convert(datetime,'01 Aug 2008 23:59:59.99'))
does roll over to the next day. and get this, milliseconds whilst three digit, you should get in the habit of only using two. e.g. will only allow you to specify up to .997 because .999 is really midnight - e.g.
select dateadd(ss,0, convert(datetime,'01 Aug 2008 23:59:59.999'))
select dateadd(ss,2, convert(datetime,'01 Aug 2008 00:00:00.000'))
That's why I say it does not really exist ! where as :
select dateadd(ss,2, convert(datetime,'01 Aug 2008 23:59:59.99'))
does roll over to the next day. and get this, milliseconds whilst three digit, you should get in the habit of only using two. e.g. will only allow you to specify up to .997 because .999 is really midnight - e.g.
select dateadd(ss,0, convert(datetime,'01 Aug 2008 23:59:59.999'))
ASKER