INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login

Navigation

Join Us!
Keyword Search
Browse Forums
Jobs
Start A Group
Tell A Friend
Donate
Whitepapers

Come Join Us!

Talk With Other Members
Be Notified Of Responses
To Your Posts
Keyword Search
Turn Off Ad Banners
One-Click Access To Your
Favorite Forums
Automated Signatures
On Your Posts
Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Partner With Us!

"Best Of Breed" Forums Add Stickiness To Your Site

(Download This Button Today!)

Member Feedback

"...I have tons of books, have book marked tons of tutorials, which have helped, but this forum has answered those "impossible to find" solutions. I am thrilled with this site..."

More...

Geography

Where in the world do Tek-Tips members come from?

Click Here To Find Out!

Home > Forums > Programmers > DBMS Packages > Microsoft SQL Server: Programming > FAQs

Microsoft SQL Server: Programming FAQ

Date and time Tips and tricks

Yet another date FAQ, part I: simple tips & tricks

faq183-5842

Posted: 29 Apr 05 (Edited 14 Jul 05)

Date/time manipulations are often tricky. This FAQ is an attempt to cover some of more common (aka: frequently asked in forum183) tips and tricks - in no particular order.

1. How to add/subtract: forgotten ways

In some situations +/- operators have certain advantages over DATEADD() function. Expressions might get shorter, and data type of added value is not limited to integer:

CODE

-- two days from now
SELECT GETDATE() + 2
-- 15 hours, 23 minutes and 4 seconds ago:
SELECT GETDATE() - (15*3600+23*60+4)/86400.
SELECT GETDATE() - '15:23:04'

Of course variable-length intervals (months, years) cannot be added/subtracted this way. At least not without clunky math that DATEADD() already handles internally.

One date added to another produces rather meaningless value because of zero date offset (Jan 1st 1900):

CODE

-- somewhere in 22nd century....
SELECT GETDATE() + GETDATE()

Result given by subtraction is composed from zero date and duration between two datetimes, which can be useful in some calculations:

CODE

-- somewhere in early 20th century...
SET DATEFORMAT DMY
SELECT GETDATE() - '20/05/2005'

On the other hand, combination of +/- operators is rarely useful except in cases like:

CODE

DECLARE @dob1 smalldatetime; SET @dob1 = '19730105'
DECLARE @dob2 smalldatetime; SET @dob2 = '19791125'
-- on this date, 2nd person will be as much as old as 1st person now:
SELECT GETDATE() - @dob1 + @dob2

2. DATEDIFF(): other ways

Simple question: there are two datetime variables: @date1 and @date2. How many hours elapsed between these two values? First thing that comes to mind is:

CODE

SELECT DATEDIFF(hh, @date1, @date2)

Wrong! DATEDIFF() returns number of crossed interval boundaries, not distance measured in interval units. To remember visually how it works, imagine you are driving a car from point A to point B. Every mile or so a new milepost (milestone) appears. DATEDIFF() returns total number of mileposts encountered. When we need to calculate a distance (duration) between two points... well, here are some examples:

CODE

SET DATEFORMAT DMY
SELECT DATEDIFF( hh, '20/05/2005 13:00:00', '20/05/2005 14:00:00' )
SELECT DATEDIFF( hh, '20/05/2005 13:59:59', '20/05/2005 14:00:00' )
SELECT DATEDIFF( hh, '20/05/2005 13:00:00', '20/05/2005 14:59:59' )

This behaviour is by-design. In fact, it becomes handy for business apps in which dates are relevant but times often aren't. But again, DATEDIFF() (mis)used to calculate duration fails in range (-interval, +interval). That said, smaller interval = smaller error margin. By using minutes or seconds instead error margin will likely become negligible:

CODE

SELECT DATEDIFF(ss, @date1, @date2)/3600

As described in tip #1, one date subtracted from another returns duration + zero date offset. DATEDIFF() between this value and zero date produces result we want and no visible error margin at all:

CODE

SELECT DATEDIFF( hh, 0, @date2-@date1 )

Please take note that any duration "measured" in variable-length units (month, year) is mathematically inaccurate. 1 month interval sometimes equals 28 days and sometimes 31, depending on location on datetime/calendar axis. Such values should have only presentational purpose (e.g. Joe Smith is 34 years, 11 months and 2 days old).

3. Retrieving rows within date range [from - to]

This is very common real-life demand: user specifies @dateFrom and @dateTo, server must pull out data within that inclusive range based on dateColumn. Assuming that dateColumn never has time fraction, simple BETWEEN condition should be enough:

CODE

... WHERE dateColumn BETWEEN @dateFrom and @dateTo

Unfortunately, SQL Server knows no separated date type, and appearance of time fraction in data is real possibility. When that happens, BETWEEN will ignore all values during @dateTo past midnight. To prevent such problems the safest and still mathematically correct expression is based on half-opened (aka: semi-inclusive) interval:

CODE

... WHERE dateColumn >= @dateFrom AND dateColumn < @dateTo + 1

4. UNIX/SQL2k conversions

UNIX date/time is represented as number of seconds since New Year 1970. Therefore:

CODE

-- from UNIX to SQL2k
SELECT DATEADD(ss, <UNIXvalue>, '1970')
-- from SQL2k to UNIX
SELECT DATEDIFF(ss, '1970', <SQL2kValue> )

Be aware that 32-bit signed integer overflows on UNIX dates past January 2038.

5. Beginning of year, month...

... without string manipulation, CAST()/CONVERT() and spaghetti mess of function calls?

CODE

SELECT DATEADD(<datepart>, DATEDIFF(<datepart>, 0, GETDATE()), 0)

Use any <datepart> you need - yy for year, mm for month etc. Even qq and wk are potentially useful. This trick is based on a fact that DATEDIFF() returns integer, basically acting as FLOOR() function for dates. Combined with complementary DATEADD() and reference point of choice (0 - zero SQL2k date), it returns beginning of interval.

6. End of year, month...

Use previously mentioned technique. Add one interval. Subtract one day. Voila. End of current month is:

CODE

SELECT DATEADD(mm, 1 + DATEDIFF(mm, 0, GETDATE()), 0) - 1

7. How to extract date value from date/time?

We can only reset time fraction to 00:00AM. One possible way: convert to unseparated ISO format, take 8 leftmost characters(yyyymmdd), convert back if necessary:

CODE

SELECT CONVERT(smalldatetime, CONVERT(varchar(8), GETDATE(), 112))

If we take a look back at tip #5, solution is simple:

CODE

SELECT DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)

8. How to extract time value?

Same as for date, SQL2k knows no separated time type. Until Yukon comes out, choose between time string and number of seconds:

CODE

-- to hh:mm:ss
SELECT CONVERT(varchar(8), GETDATE(), 108)
-- to number of seconds
SELECT DATEDIFF(ss, 0, CONVERT(varchar(8), GETDATE(), 108))

First method is OK for displaying purposes only, second for everything else. Integers are faster, have no format and can store time values greater than 24 hours without any trouble.

9. How to compose date for given year, month and day?

Assuming that values are @y, @m and @d respectively:

CODE

-- with date string. Format is forced (120) to prevent internationalization issues
SELECT CONVERT(smalldatetime, CONVERT(varchar(4), @y) + '-' + CONVERT(varchar(2), @m) + '-' + CONVERT(varchar(2), @d), 120)
-- with pure date arithmetics:
SELECT DATEADD(dd, @d-1, DATEADD(mm, @m-1, DATEADD(yy, @y-1900, 0)))

10. How to calculate age (years elapsed)

Take DATEDIFF(yy), subtract 1 if birthdate falls later within a year:

CODE

DECLARE @dob smalldatetime; SET @dob = '19730105'
DECLARE @today smalldatetime; SET @today = GETDATE()
SELECT DATEDIFF(yy, @dob, @today) -
CASE WHEN MONTH(@dob) > MONTH(@today) OR (MONTH(@dob) = MONTH(@today) AND DAY(@dob) > DAY(@today))
THEN 1 ELSE 0 END

There are many ways to substitute ugly CASE from above (and quietly miss a day or two thanks to leap years), though principles are always the same.

---------
Special thanks to: donutman, ESquared, JamesLean (yyyymmdd ), SQLBill and r937.

Back to Microsoft SQL Server: Programming FAQ Index
Back to Microsoft SQL Server: Programming Forum
My FAQ Archive

Email This FAQ To A Friend

My Archive

My FAQ Archive