Differences

This shows you the differences between two versions of the page.

--- dates [2018/05/13 14:45] – created david
+++ dates [2025/03/27 15:42] (current) – external edit 127.0.0.1
@@ Line 4: / Line 4: @@
 ====== SQL Server ======
+Pages here:
+  * [[Creating Date Ranges]]
+Other links:
   * [[http://david-halliday.co.uk/wiki/doku.php?id=databases:sql_server:date_formatting|Date Formatting]]
   * [[http://david-halliday.co.uk/wiki/doku.php?id=databases:sql_server:date_selecting|Date Selecting]]
 <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(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(YEAR,  DateAdd(s, -1, DateAdd(mm, DateDiff(m, 0, GetDate()), 0))) AS last_month_year_number,
@@ Line 19: / Line 22: @@
 </code>
+Getting whole days, and adding/subtracting days. Below, a selection of ways of getting to whole days.
+<code sql>
+SELECT GetDate()                                                               AS [Now_datetime],
+     --2005 (and after)
+       DateAdd(DAY,   DateDiff(DAY,   0, GetDate()), 0)                        AS [today_whole_date],
+       DateAdd(DAY,   -1, DateAdd(DAY,   DateDiff(DAY,   0, GetDate()), 0))    AS [yesterday],
+       DateAdd(DAY,    1, DateAdd(DAY,   DateDiff(DAY,   0, GetDate()), 0))    AS [tomorrow],
+       DateAdd(MONTH, DateDiff(MONTH, 0, GetDate()), 0)                        AS [start of month],
+       DateAdd(DAY,   -1, DateAdd(MONTH, DateDiff(MONTH, 0, GetDate()), 0))    AS [end of last month],
+       DateAdd(MONTH, -1, DateAdd(MONTH, DateDiff(MONTH, 0, GetDate()), 0))    AS [start of last month],
+       DateAdd(MONTH, -3, DateAdd(MONTH, DateDiff(MONTH, 0, GetDate()), 0))    AS [start of 3 months ago],
+     --2008 (and after)
+       EOMonth(GetDate())                                                      AS [EOM end of this month],
+       EOMonth(GetDate(),-1)                                                   AS [EOM end of last month],
+       DateAdd(DAY,1,EOMONTH(GetDate(),-1))                                    AS [EOM start of this month],
+     --2014 (and after) -- At last we have DATE as datatype (whole day)
+       CAST(GetDate()AS DATE)                                                  AS [today_whole_date_as_date],
+       CAST(GetDate() - 1 AS DATE)                                             AS [date_cast_yesterday],
+	   CAST(DateAdd(DAY,   -1, GetDate()) AS DATE)                             AS [date_cast_yesterday_v2],
+	   CAST(DateAdd(MONTH,  1, GetDate()) AS DATE)                             AS [date_cast_one_month_ahead],
+       DateFromParts(2017, 01, 01)                                             AS [DFP code a date],
+       DateAdd(MONTH, -1, DateFromParts(YEAR(GetDate()), MONTH(GetDate()), 1)) AS [DFP Start of last month],
+       DateAdd(MONTH, -2, DateFromParts(YEAR(GetDate()), MONTH(GetDate()), 1)) AS [DFP Start of month before],
+       DateAdd(DAY,   -1, DateFromParts(YEAR(GetDate()), MONTH(GetDate()), 1)) AS [DFP End of last month]
+;
+</code>
 ====== Oracle ======
 Links of interest: