Date 23:59:59 - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Date 23:59:59

Author

Topic

luckylad
Starting Member

3 Posts

Posted - 2007-02-20 : 23:33:25

I need to add 30 days to the current date and have the hour and min come out as 23:59:59.

when I use dateadd I get the +30 days, but the hours and min are the time of day I ran the SQL. This is causing me to miss some of the data for the last day.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-20 : 23:42:08

[code]select dateadd(day, datediff(day, 0, getdate()), 30) + '23:59:59'[/code]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-20 : 23:43:25

or why not just add 31 days and check for "less than"


where  datecol < dateadd(day, datediff(day, 0, getdate()), 31)

luckylad
Starting Member

3 Posts

Posted - 2007-02-20 : 23:43:50

ty
still learning .... ALOT

luckylad
Starting Member

3 Posts

Posted - 2007-02-20 : 23:46:27

Tried the adding of 31 days, but mgr didnt want that. He wanted the adding of 30 days and in the requirements it was for the full day, not just part of it.

I will test it when I get to work tomorrow, Thanks for your help

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-20 : 23:57:27

[code]
select today = getdate(),
add_30days = dateadd(day, datediff(day, 0, getdate()), 30) + '23:59:59',
add_31days = dateadd(day, datediff(day, 0, getdate()), 31)
/*
today add_30days add_31days
------------------------- ------------------------ -----------------------
2007-02-21 12:52:26.250 2007-03-23 23:59:59.000 2007-03-24 00:00:00.000
*/
[/code]

Adding 31 days and checking for LESS THAN wil be a better solution as it includes the entire date 03-23 and exclude 03-24. If your date column is datetime, checking for <= '2007-03-23 23:59:59' might still missed out time 23:59:59.001 to 23:59:59.997

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-21 : 00:16:46

take a look at this


declare @table table
(
	datecol	datetime
)

insert into @table
select	'2007-03-23 20:00:00.000'	union all
select	'2007-03-23 23:59:59.003'	union all
select	'2007-03-24 01:00:00.000'	

select	today      = getdate(), datecol
from	@table
where	datecol	<= dateadd(day, datediff(day, 0, getdate()), 30) + '23:59:59'
/*
today                    datecol                                                
------------------------ -----------------------
2007-02-21 13:14:51.030  2007-03-23 20:00:00.000
*/

select	today      = getdate(), datecol
from	@table
where	datecol	< dateadd(day, datediff(day, 0, getdate()), 31)
/*
today                    datecol                                                
------------------------ -----------------------
2007-02-21 13:14:51.030  2007-03-23 20:00:00.000
2007-02-21 13:14:51.030  2007-03-23 23:59:59.003
*/

Marioi
Posting Yak Master

132 Posts

Posted - 2007-02-21 : 16:53:23

Instead of adding "23:59:59" in DateDiff, add "23:59:59.999"

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2007-02-21 : 17:12:52

[code]
Instead of adding "23:59:59" in DateDiff, add "23:59:59.999"
[/code]

Be very careful of the datetime types resolution... 3 milliseconds

[code]
Select cast('20010101 23:59:59.999' as datetime)
, cast('20010101 23:59:59.998' as datetime)
, cast('20010101 23:59:59.997' as datetime)
[/code]

DavidM

Production is just another testing cycle

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-02-21 : 17:19:02

quote:
Originally posted by Marioi

Instead of adding "23:59:59" in DateDiff, add "23:59:59.999"

Wrong, but it is a good example of why it is better to add one day, and look for less than.

See below for what actually happens when you add 23:59:59.999.


select
	Date,
	DatePlusTime = Date+Time
from
(
select	date = convert(datetime,'20070221'),
	Time = 	'23:59:59.999'
) a

Results:

Date                     DatePlusTime
------------------------ ------------------------
2007-02-21 00:00:00.000  2007-02-22 00:00:00.000

(1 row(s) affected)

CODO ERGO SUM

Marioi
Posting Yak Master

132 Posts

Posted - 2007-02-26 : 17:40:23

quote:
Be very careful of the datetime types resolution... 3 milliseconds

Thanks, I learned something.

Can one count on cast('20010101 23:59:59.998' as datetime) always resolving to .997 or that depends on some number of ticks since the beginning of (SQL) time?

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-02-26 : 18:10:38

quote:
Originally posted by Marioi

quote:
Be very careful of the datetime types resolution... 3 milliseconds

Thanks, I learned something.

Can one count on cast('20010101 23:59:59.998' as datetime) always resolving to .997 or that depends on some number of ticks since the beginning of (SQL) time?

What did you find when you ran a test to see what happens?

CODO ERGO SUM

Marioi
Posting Yak Master

132 Posts

Posted - 2007-02-26 : 18:48:13

quote:
What did you find when you ran a test to see what happens?

I always received the same result (.999 = .000 next day; .998 = .997). I ran the code below with different dates but not over a large enough sample of date ranges and over enough time to see whether ticks fit in a day without dither. How is it supposed to work?

DECLARE @sDate varchar(10)
SET @sDate = '2001-02-02'
Select cast(@sDate + ' 23:59:59.999' as datetime)
	, cast(@sDate + ' 23:59:59.998' as datetime)
	, cast(@sDate + ' 23:59:59.997' as datetime)

Kristen
Test

22859 Posts

Posted - 2007-02-27 : 05:18:53

Why not do LESS THAN TOMORROW instead of LESS-THAN-OR-EQUAL-TO-LAST-MILLISECOND-OF-TODAY ??

Maybe I'm missing something, but I just think the whole strategy of what you are being asked to do is flawed!

Kristen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-27 : 05:24:33

Fire the manager if he does not recognize the equal result you get using the two different suggestions.

Peter Larsson
Helsingborg, Sweden

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2007-02-28 : 00:14:42

I agree! Can't believe the manager doesn't understand that looking for less than 31 days is more effective than looking for 30 days plus time.

--Jeff Moden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources