[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
Solved

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

Posted on 2012-09-03
Medium Priority
762 Views
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)

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.999

Respectfully,
Alan
0
Question by:Alan Warren
• 4
• 3

LVL 143

Expert Comment

ID: 38360592
>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

LVL 26

Author Comment

ID: 38360640
Hi angelIII,
I got a bunch of these local variables all hang off the one I posted.
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)

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)
0

LVL 26

Author Comment

ID: 38360681
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
-- 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)
-- 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)
-- Returns:
-- 2012-01-01 00:00:00.000	2012-12-31 23:59:59.997
``````
Alan
0

LVL 143

Accepted Solution

Guy Hengel [angelIII / a3] earned 1600 total points
ID: 38360692
>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

LVL 26

Author Comment

ID: 38360738
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

LVL 143

Expert Comment

ID: 38360750
yes, date/time internally is stored as a decimal, and as you found the 997 is the highest value indeed
0

LVL 75

Assisted Solution

Anthony Perkins earned 400 total points
ID: 38361595
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

LVL 26

Author Closing Comment

ID: 38361689
Thank you both, 997 ms is close enough for the task at hand.

Alan
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
###### Suggested Courses
Course of the Month17 days, 23 hours left to enroll