Alan Warren
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:
Respectfully,
Alan
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
Would like an end-date like: 2012-12-31 23:59:59.999Respectfully,
Alan
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:
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
Alan ";0)
ASKER
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
Alan
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you both, 997 ms is close enough for the task at hand.
Alan
Alan
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