Link to home
Start Free TrialLog in
Avatar of Alan Warren
Alan WarrenFlag for Philippines

asked on

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
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

>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.
https://www.experts-exchange.com/Database/Miscellaneous/A_1499-DATE-and-TIME-don%27t-be-scared-and-do-it-right-the-first-time.html
Avatar of Alan Warren

ASKER

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)
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
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
yes, date/time internally is stored as a decimal, and as you found the 997 is the highest value indeed
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thank you both, 997 ms is close enough for the task at hand.

Alan