Calculate current year end datetime to the millisecond from GetDate()

Trying to calculate the end datetime for the current calendar year, using getdate()
This is what I have:
declare @dt datetime
set @dt = GETDATE()
declare @Calendar_Year_Start datetime
declare @Calendar_Year_End datetime

select
 @Calendar_Year_Start = DATEADD(yyyy, DATEDIFF(yyyy, 0, @dt), 0) 
,@Calendar_Year_End = DATEADD(second,-1, DATEADD(year, 1, @Calendar_Year_Start))


select @Calendar_Year_Start as Calendar_Year_Start, @Calendar_Year_End as Calendar_Year_End

Returns:
2012-01-01 00:00:00.000	2012-12-31 23:59:59.000

Open in new window

Would like an end-date like: 2012-12-31 23:59:59.999

Respectfully,
Alan
LVL 26
Alan WarrenApplications DeveloperAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
>it's a sql server limitation, 997 being the maximum millisecond value, else it gets rounded up to the nearest second.
indeed. hence I never use that kind of "value", but always the >= and < method.

for the record, datetime2 (coming from sql 2008 R2/sql 2010 and higher) has a higher accuracy (100 nanoseconds):
http://msdn.microsoft.com/en-us/library/bb677335.aspx
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>Would like an end-date like: 2012-12-31 23:59:59.999
why?
presuming you want to get the date range 2012. you do better like this:

where your_date_field >= convert(datetime, '2012-01-01', 120)
  and your_date_field < convert(datetime, '2013-01-01', 120)

much easier to calculate, and you are sure that whatever the precision is, you will get the correct results.
http://www.experts-exchange.com/Database/Miscellaneous/A_1499-DATE-and-TIME-don%27t-be-scared-and-do-it-right-the-first-time.html
0
 
Alan WarrenApplications DeveloperAuthor Commented:
Hi angelIII,
I got a bunch of these local variables all hang off the one I posted.
Not questioning your logic:
where your_date_field >= convert(datetime, '2012-01-01', 120)
  and your_date_field < convert(datetime, '2013-01-01', 120)

But my '2012-01-01' is '2012-12-31 23:59:59.000' and if I convert it to a 120 pattern:
declare @dt datetime
set @dt = GETDATE()
declare @Calendar_Year_Start datetime
declare @Calendar_Year_End datetime

select
 @Calendar_Year_Start = DATEADD(yyyy, DATEDIFF(yyyy, 0, @dt), 0) 
,@Calendar_Year_End = convert(datetime, DATEADD(second,-1, DATEADD(year, 1, @Calendar_Year_Start)), 120) 


select @Calendar_Year_Start as Calendar_Year_Start, @Calendar_Year_End as Calendar_Year_End

-- Still Returns:
-- 2012-01-01 00:00:00.000	2012-12-31 23:59:59.000

Open in new window

Alan ";0)
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
Alan WarrenApplications DeveloperAuthor Commented:
I got it now, thanks for the link to DATE and TIME ... don't be scared, and do it right (the first time), it's a sql server limitation, 997 being the maximum millisecond value, else it gets rounded up to the nearest second.
select
@Calendar_Year_End = DATEADD(ms,999, DATEADD(second,-1, DATEADD(year, 1, @Calendar_Year_Start)))
-- Returns:
-- 2012-01-01 00:00:00.000	2013-01-01 00:00:00.000

select
 @Calendar_Year_Start = DATEADD(yyyy, DATEDIFF(yyyy, 0, @dt), 0) 
,@Calendar_Year_End = DATEADD(ms,998, DATEADD(second,-1, DATEADD(year, 1, @Calendar_Year_Start)))
-- Returns:
-- 2012-01-01 00:00:00.000	2012-12-31 23:59:59.997

select
 @Calendar_Year_Start = DATEADD(yyyy, DATEDIFF(yyyy, 0, @dt), 0) 
,@Calendar_Year_End = DATEADD(ms,997, DATEADD(second,-1, DATEADD(year, 1, @Calendar_Year_Start)))
-- Returns:
-- 2012-01-01 00:00:00.000	2012-12-31 23:59:59.997

Open in new window

Alan
0
 
Alan WarrenApplications DeveloperAuthor Commented:
Hi angelIII,
I am manufacturing this upper date range; once I get the highest millisecond value for the given date, then I can use your  >= and < method, we have no difference on that part.

Possibly a better title for this question:
What is the highest millisecond value I can add to datetime value without it rolling over to the next day?

I suspect that Sql Server converts datetime values to decimal for comparison, so what is the highest decimal millisecond part I can add to a datetime value without it rolling over to the next day, regardless of how it is displayed in the sql server UI?

Alan
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
yes, date/time internally is stored as a decimal, and as you found the 997 is the highest value indeed
0
 
Anthony PerkinsConnect With a Mentor Commented:
A couple of points.  You are confusing accuracy with precision.  

1. SQL Server cannot be more accurate than the CPU and the last time I checked the CPU depends on clock ticks which cannot give you the millisecond accuracy you are looking for.
2. If you need better precision than you need to switch to datetime2 instead of datetime.
0
 
Alan WarrenApplications DeveloperAuthor Commented:
Thank you both, 997 ms is close enough for the task at hand.

Alan
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.