[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
8
Medium Priority
?
762 Views
Last Modified: 2012-09-03
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
0
Comment
Question by:Alan Warren
  • 4
  • 3
8 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
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

by:Alan Warren
ID: 38360640
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
 
LVL 26

Author Comment

by:Alan Warren
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
@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
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
LVL 143

Accepted Solution

by:
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

by:Alan Warren
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

by:Guy Hengel [angelIII / a3]
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

by:Anthony Perkins
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

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

Alan
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question