end of the day - SQL Server Forums

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

end of the day

Author

Topic

lamujerdetuhermano10
Yak Posting Veteran

75 Posts

Posted - 2008-09-11 : 19:54:15

i want to get the end of the time of the day and store it in a "datetime" field
input:
2008-01-01

result:
2008-01-01 23:59:59.999

This doesnt work -- convert(varchar(10), getdate(),101) + '23:59:59.999'

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-09-11 : 20:45:24

why do you need to do this ?

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-09-11 : 20:51:49

quote from BOL

quote:
datetime and smalldatetime
Date and time data types for representing date and time of day.

datetime

Date and time data from January 1, 1753 through December 31, 9999, to an accuracy of one three-hundredth of a second (equivalent to 3.33 milliseconds or 0.00333 seconds). Values are rounded to increments of .000, .003, or .007 seconds, as shown in the table.

You will not get the 23:59:59.999 due to the accuracy of the datetime data type
see


declare @date datetime

select	@date = convert(datetime, '2008-09-12 23:59:59.999')

select	@date

/* RESULT
2008-09-13 00:00:00.000
*/

So why do you need to get the end of day ?

if it for selection of data containing date & time, you should use

where datecolumn >= BEGINOFDAY
and datecolumn < NEXTDAY


where datecolumn >= dateadd(day, datediff(day, 0, getdate()), 0)
and   datecolumn <  dateadd(day, datediff(day, 0, getdate()), 1)

KH
[spoiler]Time is always against us[/spoiler]

lamujerdetuhermano10
Yak Posting Veteran

75 Posts

Posted - 2008-09-12 : 02:20:21

How about 23:59:59?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-09-12 : 02:24:07

quote:
Originally posted by lamujerdetuhermano10

How about 23:59:59?

What about 23:59:59 ? if you have record with 23:59:59.001 you will missed that record. Use the method i suggested. If that is what your intention.

KH
[spoiler]Time is always against us[/spoiler]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-12 : 02:24:53

Didn't khtans suggestion of

where datecolumn >= dateadd(day, datediff(day, 0, getdate()), 0)
and   datecolumn <  dateadd(day, datediff(day, 0, getdate()), 1)

ring a bell?

SELECT	dateadd(day, datediff(day, 0, getdate()), 0),
	dateadd(day, datediff(day, 0, getdate()), '23:59:59')

E 12°55'05.63"
N 56°04'39.26"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-09-12 : 02:30:14

quote:
Originally posted by lamujerdetuhermano10

i want to get the end of the time of the day and store it in a "datetime" field
input:
2008-01-01

result:
2008-01-01 23:59:59.999

This doesnt work -- convert(varchar(10), getdate(),101) + '23:59:59.999'

select dateadd(millisecond,-2,dateadd(day,datediff(day,0,getdate()),1))

Why do you want to store this value?

Madhivanan

Failing to plan is Planning to fail

lamujerdetuhermano10
Yak Posting Veteran

75 Posts

Posted - 2008-09-12 : 12:24:10

thanks it worked
SELECT dateadd(day, datediff(day, 0, getdate()), 0),
dateadd(day, datediff(day, 0, getdate()), '23:59:59')

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-09-15 : 03:17:34

quote:
Originally posted by lamujerdetuhermano10

thanks it worked
SELECT dateadd(day, datediff(day, 0, getdate()), 0),
dateadd(day, datediff(day, 0, getdate()), '23:59:59')

So, do you think dateadd(millisecond,-2,dateadd(day,datediff(day,0,getdate()),1)) is not the last date?

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources