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:
-- 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):
-- 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:
-- 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:
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:
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:
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:
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:
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:
... 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:
... 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:
-- 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?
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:
If we take a look back at tip #5, solution is simple:
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:
-- 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:
-- 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:
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.