Date

Calendar

Why should I consider using an auxiliary calendar table?

../../misc/howto/microsoft/why-should-i-consider-using-an-auxiliary-calendar-table.pdf

Copy of an article from aspfaq.

I wrote this function to return a table variable containing a range of dates:

CREATE FUNCTION getCalendarTable(
    @start_date DATETIME,
    @end_date DATETIME)
    RETURNS @calendar_table TABLE (cal_date SMALLDATETIME)
AS
BEGIN
    DECLARE @dt SMALLDATETIME
    SET @dt = @start_date
    WHILE @dt < @end_date
    BEGIN
        INSERT @calendar_table(cal_date)
        SELECT DATEADD([day], 0, DATEDIFF([day], 0, @dt))
        SET @dt = @dt + 1
    END
    RETURN
END

Note: If you read the article above, you will read about performance issues with this algorithm. For now, I want to keep it simple. To use this function:

Create a test table and insert some data:

CREATE TABLE messages (
  user_initials VARCHAR(3),
  received_date DATETIME
)

INSERT messages(user_initials, received_date)
VALUES('PJK', '2008-06-02')
INSERT messages(user_initials, received_date)
VALUES('DN', '2008-06-02')
INSERT messages(user_initials, received_date)
VALUES('AK', '2008-06-04')
INSERT messages(user_initials, received_date)
VALUES('BK', '2008-06-06')
INSERT messages(user_initials, received_date)
VALUES('MK', '2008-06-06')
INSERT messages(user_initials, received_date)
VALUES('SW', '2008-06-06')

This query will use the calendar function to display all the dates:

SELECT calendar.cal_date, COUNT(messages.user_initials)
  FROM getCalendarTable('2008-06-01', '2008-06-07') calendar
  LEFT OUTER JOIN messages
    ON messages.received_date = calendar.cal_date
  GROUP BY calendar.cal_date
  ORDER BY calendar.cal_date

Note: For a more robust way of writing this method, see

Transact-SQL, Cursors, Transact-SQL.

DATEDIFF

It is not possible to part the datepart as a parameter, so a function like this is needed:

CREATE FUNCTION dateDiffParam(
    @date_part VARCHAR(10),
    @start_date DATETIME,
    @end_date DATETIME)
    RETURNS INT
AS
BEGIN
    DECLARE @result INT
    SET @result = (
        SELECT
            CASE
                WHEN @date_part = 'SECOND' THEN DATEDIFF(second, @start_date, @end_date)
                WHEN @date_part = 'MINUTE' THEN DATEDIFF(minute, @start_date, @end_date)
                WHEN @date_part = 'HOUR' THEN DATEDIFF(hour, @start_date, @end_date)
                WHEN @date_part = 'DAY' THEN DATEDIFF(day, @start_date, @end_date)
                WHEN @date_part = 'WEEK' THEN DATEDIFF(week, @start_date, @end_date)
                WHEN @date_part = 'MONTH' THEN DATEDIFF(month, @start_date, @end_date)
                WHEN @date_part = 'YEAR' THEN DATEDIFF(year, @start_date, @end_date)
                ELSE DATEDIFF(DAY, @start_date, @end_date)
            END
        )
    RETURN @result
END

Date Only

To remove the time part from a DATETIME column (from SQL Server Central Forums:

DATEADD(day, 0, DATEDIFF(day, 0, sent_datetime)) AS sent_date_only

Format

Displaying Dates and Times in Different Formats

RIGHT(CAST(100 + DATEPART(day, @appointmentDate) AS CHAR(3)), 2) + '/'
    + RIGHT(CAST(100 + DATEPART(month, @appointmentDate) AS CHAR(3)), 2) + '/'
    + CAST(DATEPART(year, @appointmentDate) AS CHAR(4))

First Day in Week

Getting the first day in a week with T-SQL

SELECT DATEADD([week], DATEDIFF([week], 0, GETDATE()), 0)

This is quite a nice combination:

DECLARE @first_day_of_last_week DATETIME
DECLARE @first_day_of_this_week DATETIME
SELECT @first_day_of_this_week = DATEADD([week], DATEDIFF([week], 0, GETDATE()), 0)
SELECT @first_day_of_last_week = DATEADD([week], -1, @first_day_of_this_week)
SELECT @first_day_of_last_week, @first_day_of_this_week

Function to calculate a persons Age in T-SQL

From http://www.wisesoft.co.uk/,

::: ../../misc/howto/microsoft/fAgeCalc.doc

create function dbo.fAgeCalc(@DOB datetime,@Date datetime)
returns smallint
as
----------------------------------------------------
-- * Created By David Wiseman, Updated 03/11/2006
-- * http://www.wisesoft.co.uk
-- * This function calculates a persons age at a
-- * specified date from their date of birth.
-- * Usage:
-- * select dbo.fAgeCalc('1982-04-18',GetDate())
-- * select dbo.fAgeCalc('1982-04-18','2006-11-03')
----------------------------------------------------
begin
return (
  select case when month(@DOB)>month(@Date) then datediff(yyyy,@DOB,@Date)-1
      when month(@DOB)<month(@Date) then datediff(yyyy,@DOB,@Date)
      when month(@DOB)=month(@Date) then
        case when day(@DOB)>day(@Date)
          then datediff(yyyy,@DOB,@Date)-1
        else datediff(yyyy,@DOB,@Date) end
      end)
end

To call this function for this date_of_birth field:

dbo.fAgeCalc(date_of_birth, GetDate())

One Day

To select all transactions for a date:

WHERE (received_datetime BETWEEN '2008-06-01 00:00:00.000' AND '2008-06-01 23:59:59.999')

Yesterday

Idea based on First Day in Week (see above):

DECLARE @start_of_today DATETIME
DECLARE @start_of_yesterday DATETIME
SELECT @start_of_today = DATEADD([day], DATEDIFF([day], 0, GETDATE()), 0)
SELECT @start_of_yesterday = DATEADD([day], -1, @start_of_today)
SELECT @start_of_today, @start_of_yesterday