SQL DATE Functions

Transact-SQL (TSQL) Quick Tips – SQL DATE Functions

In the following post, I demonstrate a collection of common SQL DATE Functions I use on a regular basis.  I encourage you to copy this code into your Query Window, then EXECUTE as-is.  The code includes a #temp table with data to illustrate the functionality.  You can modify to suit your needs.

The examples below use the following SQL DATE Functions:

  • Day ( date ) – Returns an integer representing the day (day of the month) of the specified date.
  • Month ( date ) – Returns an integer that represents the month part of a specified date.
  • Year ( date ) – Returns an integer that represents the year part of a specified date.
  • EOMONTH ( start_date [, month_to_add ] ) – Returns the last day of the month that contains the specified date, with an optional offset.
  • DateName ( datepart, date ) – Returns a character string that represents the specified datepart of the specified date.
  • DatePart (datepart, date ) – Returns an integer that represents the specified datepart of the specified date.
  • DateDiff ( datepart , startdate , enddate  ) – Returns the number of date or time datepart boundaries that are crossed between two specified dates.

SQL Code Examples

 

 

If you would like to post any additional examples, please add them to the comments below.

Additional Content

Check out more examples by visiting my Home Page

Here you will find topics covering  Qlikview  SQL Server  Excel VBA

 

2 thoughts on “SQL DATE Functions

    • Hello Jan,

      Perhaps this will be what you are looking for? I used as CASE statement instead:

      CREATE TABLE #temp (SampleDate date)
      INSERT INTO #temp
      SELECT ‘2/23/13’ UNION ALL
      SELECT ‘4/27/17’ UNION ALL
      SELECT ‘1/23/16’ UNION ALL
      SELECT ‘7/23/19′ UNION ALL
      SELECT ’10/7/18’ UNION ALL
      SELECT ‘1/30/15’ UNION ALL
      SELECT ‘1/31/17’

      select
      SampleDate
      , Case
      WHEN SampleDate < GetDate() THEN 'backlog' ELSE Cast(SampleDate as varchar) END AS BacklogFlag from #temp drop table #temp

Leave a Comment