Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Date and time Tips and tricks

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

by vongrunt Posted Apr 29, 2005 (Edited Jul 14, 2005)

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 [b]>=[/b] @dateFrom AND dateColumn [b]<[/b] @dateTo [b]+ 1[/b]

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.

Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.

Accept Learn more…

Back

Top