| Next revisionBoth sides next revision |
| dates [2018/05/13 14:45] – created david | dates [2018/05/13 14:46] – [SQL Server] david |
|---|
| * [[http://david-halliday.co.uk/wiki/doku.php?id=databases:sql_server:date_selecting|Date Selecting]] | * [[http://david-halliday.co.uk/wiki/doku.php?id=databases:sql_server:date_selecting|Date Selecting]] |
| <code sql> | <code sql> |
| SELECT GetDate() AS now_date_time, | SELECT GetDate() AS now_date_time, |
| --CAST(FLOOR(CAST(GetDate() AS FLOAT)) AS DATETIME) AS now_date, -- Best method pre 2008 | --CAST(FLOOR(CAST(GetDate() AS FLOAT)) AS DATETIME) AS now_date, -- Best method pre 2008 |
| CAST(GetDate() AS DATE) AS now_date, -- The correct way (new since Sql Server 2008) | CAST(GetDate() AS DATE) AS now_date, -- The correct way (new since Sql Server 2008) |
| DateAdd(mm, DateDiff(m, 0, GetDate()), 0) AS this_month_first_day, -- now - how far through the month we are | DateAdd(mm, DateDiff(m, 0, GetDate()), 0) AS this_month_first_day, -- now - how far through the month we are |
| DateAdd(s, -1, DateAdd(mm, DateDiff(m, 0, GetDate()), 0)) AS last_month_last_day, -- this_month_first_day - 1 second | DateAdd(s, -1, DateAdd(mm, DateDiff(m, 0, GetDate()), 0)) AS last_month_last_day, -- this_month_first_day - 1 second |
| DateAdd(mm, -2, GetDate()) AS two_months_ago_date_time, | DateAdd(mm, -2, GetDate()) AS two_months_ago_date_time, |
| DateAdd(mm, -2, DATEADD(mm, DATEDIFF(m,0,GetDate()),0)) AS two_months_ago_start_date | DateAdd(mm, -2, DATEADD(mm, DATEDIFF(m,0,GetDate()),0)) AS two_months_ago_start_date |
| DatePart(MONTH, DateAdd(s, -1, DateAdd(mm, DateDiff(m, 0, GetDate()), 0))) AS last_month_month_number, | DatePart(MONTH, DateAdd(s, -1, DateAdd(mm, DateDiff(m, 0, GetDate()), 0))) AS last_month_month_number, |
| DatePart(YEAR, DateAdd(s, -1, DateAdd(mm, DateDiff(m, 0, GetDate()), 0))) AS last_month_year_number, | DatePart(YEAR, DateAdd(s, -1, DateAdd(mm, DateDiff(m, 0, GetDate()), 0))) AS last_month_year_number, |