deeejay

SQL - Date/Time Functions

Nov 10th, 2014 (edited)
644
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
T-SQL 11.49 KB | None | 0 0
  1. Useful SQL Date/Time Functions by Database Vendor
  2. Keywords: sql dates, dates
  3.  
  4. SQL Server
  5. ===
  6. CONVERT(datetimeoffset, CT.CASE_TIME_DT_TM AT TIME ZONE 'UTC' AT TIME ZONE 'Pacific Standard Time') AS CASE_TIME_DT_TM
  7.     --Takes care of PST-8/PDT-7 offset
  8. submit_time AT TIME ZONE 'UTC' AT TIME ZONE 'Pacific Standard Time' AS submit_time_local
  9. GETDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'Pacific Standard Time'
  10. sc.sched_start_dt_tm >= CAST(SYSDATETIMEOFFSET() AT TIME ZONE 'Pacific Standard Time' AS DATE)
  11. CT.CASE_TIME_DT_TM >= CAST(DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) - 2, 0) AS DATETIME)  -- Last 2 years
  12. CAST(DATEPART(YEAR, UPDT_DT_TM) AS VARCHAR(4)) + '-' + RIGHT('0' + CAST(DATEPART(MONTH, UPDT_DT_TM) AS VARCHAR(2)), 2) AS UPDATE_YRMO -- 2025-02
  13. CAST(FORMAT(UPDT_DT_TM, 'yyyyMMdd') AS INT) AS SUBSTR_RESULT -- 20250201
  14. SUBSTRING(CAST(FORMAT(UPDT_DT_TM, 'yyyyMMdd') AS VARCHAR), 1, 6) AS SUBSTR_RESULT -- 202502
  15. SUBSTRING(STR(DISCHARGE_DATE_ID), 1, 6) AS YEAR --2025
  16. FORMAT(UPDT_DT_TM, 'HH:mm') --00:01
  17. UPDT_DT_TM > DATEADD(DAY, -120, GETDATE()) --Last 120 days
  18. CONVERT(VARCHAR(10), UPDT_DT_TM, 120) = '2025-03-06'
  19. SELECT * FROM sys.time_zone_info
  20. RECEIVED_DATE_LOCAL >= DATEFROMPARTS(YEAR(DATEADD(MONTH, -4, GETDATE())), MONTH(DATEADD(MONTH, -4, GETDATE())), 1) --last 4 months
  21.  
  22.  
  23.  
  24. convert(varchar, CAST(sc.surg_start_dt_tm at time zone 'UTC' at time zone 'Pacific Standard Time' as DATE), 112) >= '20200101'
  25. POSTED_DATE_ID BETWEEN 20250101 AND CONVERT(VARCHAR, GETDATE() - 1, 112) --112
  26.  
  27. Style Code      Format                  Output Example (2025-03-19)
  28. 0 (Default)     Mon DD YYYY HH:MM:SS    Mar 19 2025 11:19:00 AM
  29. 101             MM/DD/YYYY              03/19/2025
  30. 102             YYYY.MM.DD              2025.03.19
  31. 103             DD/MM/YYYY              19/03/2025
  32. 104             DD.MM.YYYY              19.03.2025
  33. 105             DD-MM-YYYY              19-03-2025
  34. 106             DD Mon YYYY             19 Mar 2025
  35. 107             Mon DD, YYYY            Mar 19, 2025
  36. 108             HH:MM:SS                11:19:00
  37. 110             MM-DD-YYYY              03-19-2025
  38. 111             YYYY/MM/DD              2025/03/19
  39. 112             YYYYMMDD                20250319
  40.  
  41.  
  42.  
  43. Oracle - SQL Developer (Tools > Preferences > Database > NLS)
  44. ===
  45. FROM_TZ(TO_TIMESTAMP(AH_TRACKING_EVENT.COMPLETE_DT_TM, 'DD-MON-YYYY HH24:MI:SS'), 'GMT') AT TIME ZONE 'US/Pacific' --Doesn't work in Tableau Desktop
  46. FROM_TZ(CAST(E.ARRIVE_DT_TM AS TIMESTAMP),'GMT') AT TIME ZONE 'US/Pacific' AS ARRIVAL_DATE --Takes care of PST/PDT
  47. CAST((FROM_TZ(CAST(E.ARRIVE_DT_TM AS TIMESTAMP),'GMT') AT TIME ZONE 'US/Pacific') AS DATE) AS ARRIVAL_DATE  --Handles PST/PDT - No TZ
  48. FROM_TZ(CAST(E.DISCH_DT_TM AS TIMESTAMP), 'GMT') AT TIME ZONE 'US/Pacific' AS DISCH_DT_TM
  49. EXTRACT(YEAR FROM DE.BEG_EFFECTIVE_DT_TM) = 2014
  50. FROM_TZ(TO_TIMESTAMP(ARRIVAL.COMPLETE_DT_TM), 'GMT')  AT TIME ZONE 'PST' AS ARRIVAL_DT_TM -- NOT WORK W/PDI/TDE Output - Drops time
  51. FROM_TZ(CAST(ARRIVAL.COMPLETE_DT_TM AS TIMESTAMP), 'GMT') AT TIME ZONE 'PST' AS ARRIVAL_DT_TM --Does't drop time in PDI/TDE Output
  52. CAST((FROM_TZ(CAST(E.ARRIVE_DT_TM AS TIMESTAMP),'GMT') AT TIME ZONE 'PST') AS DATE) AS ARRIVAL_DATE         --PST only
  53. TRUNC(SYSDATE, 'YEAR') - INTERVAL '1' YEAR --includes all of previous year
  54. TRUNC(MONTHS_BETWEEN(SYSDATE,DOB)/12) AS AGE
  55. TO_DATE('2014-11-11 08:00:00','YYYY-MM-DD HH24:MI:SS')
  56. TO_TIMESTAMP('01-JAN-2016 00:00:00', 'DD-MON-YYYY HH24:MI:SS')
  57. TO_CHAR(EA.ACTIVE_STATUS_DT_TM, 'YYYY-MM-DD HH24:MI:SS') > '2012-12-31 23:59:59'
  58. TO_CHAR(E.DISCH_DT_TM, 'YYYYMMDD') AS DISCHARGE_DATE_ID
  59. CAST(TO_CHAR(E.DISCH_DT_TM, 'YYYYMMDD') AS INT) AS DISCH_DATE_ID
  60. SYSDATE - INTERVAL '8' HOUR AS SYSDATE -- Doesn't account for PST/PDT switch
  61. SYSDATE - INTERVAL '2' DAY AS SYSDATE
  62. NEW_TIME(TO_DATE('2018-08-02 07:13:57','yyyy/mm/dd hh24:mi:ss'),'PST','GMT') --Does not account for PDT
  63. NEW_TIME(EA.ACTIVE_STATUS_DT_TM, 'GMT', 'PST') AS ACTIVE_STATUS_DT_TM --Does not account for PDT
  64. TRUNC(SYSDATE)-7
  65. TRUNC(E.ARRIVE_DT_TM) > '31-DEC-13'
  66. TRUNC(D.FULLDATE, 'MONTH') = '02-01-2006 00:00:00'
  67. SUBSTR(O.ORIG_ORDER_DT_TM, 8, 2) > '13'
  68. SUBSTR(CT.CASE_TIME_DT_TM, 8, 2) AS YEAR
  69. SUBSTR(O.ORIG_ORDER_DT_TM, 4, 6) = 'DEC-14'
  70. SUBSTR(TRUNC(E.ARRIVE_DT_TM), 4, 6) AS MONTH_YEAR
  71. 24*(E.DISCH_DT_TM - E.REG_DT_TM) AS LOS_HOURS -- Use case stmt and E.INPATIENT_ADMIT_DT_TM if E.REG_DT_TM is null
  72. CASE WHEN FCT_ENCOUNTER.DISCHARGE_DATE_ID = -1 THEN
  73.        NULL
  74.        ELSE TO_DATE(FCT_ENCOUNTER.DISCHARGE_DATE_ID, 'YYYYMMDD')
  75.        END DISCHARGE_DATE
  76. CASE WHEN FCT_ENCOUNTER.DISCHARGE_DATE_ID = -1 THEN
  77.        NULL
  78.        ELSE TO_DATE(FCT_ENCOUNTER.DISCHARGE_DATE_ID || ' ' || LPAD(FCT_ENCOUNTER.DISCHARGE_TIME_ID, 6, 0), 'YYYYMMDD HH24:MI:SS')
  79.        END AS DISCHARGE_DATE_TIME
  80. CASE SUBSTR(CT.CASE_TIME_DT_TM, 4, 3)
  81.   WHEN 'JAN' THEN 1
  82.   WHEN 'FEB' THEN 2
  83.   WHEN 'MAR' THEN 3
  84.   WHEN 'APR' THEN 4
  85.   WHEN 'MAY' THEN 5
  86.   WHEN 'JUN' THEN 6
  87.   WHEN 'JUL' THEN 7
  88.   WHEN 'AUG' THEN 8
  89.   WHEN 'SEP' THEN 9
  90.   WHEN 'OCT' THEN 10
  91.   WHEN 'NOV' THEN 11
  92.   WHEN 'DEC' THEN 12
  93.   ELSE 0
  94.   END AS MONTH
  95.  
  96. Oracle ELAPSED_TIME Examples:
  97. Query:
  98.  TO_CHAR(EXTRACT(DAY FROM NUMTODSINTERVAL(DISCHARGE_DATE-ORDER_DATE, 'DAY')), 'FM00')
  99.    || ':' ||
  100.  TO_CHAR(EXTRACT(HOUR FROM NUMTODSINTERVAL(DISCHARGE_DATE-ORDER_DATE, 'DAY')), 'FM00')
  101.    || ':' ||
  102.  TO_CHAR(EXTRACT(MINUTE FROM NUMTODSINTERVAL(DISCHARGE_DATE-ORDER_DATE, 'DAY')), 'FM00')
  103.    || ':' ||
  104.  TO_CHAR(EXTRACT(SECOND FROM NUMTODSINTERVAL(DISCHARGE_DATE-ORDER_DATE, 'DAY')), 'FM00')
  105. AS ELAPSED_TIME
  106.  
  107. Output:
  108. 00d 05h 50m 25s
  109.  
  110. Query:
  111. TO_CHAR(EXTRACT(DAY FROM NUMTODSINTERVAL(E.DISCH_DT_TM - E.ORIG_ORDER_DT_TM, 'DAY'))) || 'd ' ||
  112. TO_CHAR(EXTRACT(HOUR FROM NUMTODSINTERVAL(E.DISCH_DT_TM - E.ORIG_ORDER_DT_TM, 'DAY'))) || 'h ' ||
  113. TO_CHAR(EXTRACT(MINUTE FROM NUMTODSINTERVAL(E.DISCH_DT_TM - E.ORIG_ORDER_DT_TM, 'DAY'))) || 'm'
  114. AS ELAPSED_TIME
  115.  
  116. Output:
  117. 0d 5h 50m
  118.  
  119. (E.DISCH_DT_TM - E.ORIG_ORDER_DT_TM) DAY TO SECOND AS ELAPSED_TIME_LITERAL      
  120.  
  121. Output for 1d 8h 43m 13s:
  122. +01 08:43:13.000000
  123.  
  124. Calculating Age:
  125. ROUND(MONTHS_BETWEEN(TC.CHECKIN_DT_TM, P.BIRTH_DT_TM)/12) AS AGE_AT_ADMIT
  126.  
  127.  
  128. Impala
  129. ===
  130. cast(concat(concat_ws('-', substr(cast(discharge_date_id as string),1,4), substr(cast(discharge_date_id as string),5,2), substr(cast(discharge_date_id as string),7,2)),' ',concat_ws(':',substr(lpad(cast(encounter.discharge_time_id as string),6,'000000'),1,2),substr(lpad(cast(encounter.discharge_time_id as string),6,'000000'),3,2),substr(lpad(cast(encounter.discharge_time_id as string),6,'000000'),5,2))) as timestamp) as discharge_date
  131. CAST(CAST(last_updated AS TIMESTAMP) + interval 3 days AS STRING)
  132.  
  133. ,trunc((case when transaction_date_id = -1 then null else
  134. cast(concat(concat_ws('-', substr(cast(transaction_date_id as string),1,4), substr(cast(transaction_date_id as string),5,2),
  135. substr(cast(transaction_date_id as string),7,2)),' ',concat_ws(':',substr(lpad(cast(charge.transaction_time_id as string),6,'000000'),1,2),
  136. substr(lpad(cast(charge.transaction_time_id as string),6,'000000'),3,2),substr(lpad(cast(charge.transaction_time_id as string),6,'000000'),5,2))) as timestamp) end),'DDD') as service_day
  137.  
  138. , DATEDIFF(E.DISCH_DT_TM, E.ORIG_ORDER_DT_TM) AS ELAPSED_DAYS
  139. , DATEDIFF(E.DISCH_DT_TM, E.ORIG_ORDER_DT_TM) * 24 + COALESCE(HOUR(E.DISCH_DT_TM), 0) - COALESCE(HOUR(E.ORIG_ORDER_DT_TM), 0) AS ELAPSED_HOUR,
  140. ((DATEDIFF(E.DISCH_DT_TM,E.ORIG_ORDER_DT_TM) * 24 + COALESCE(HOUR(E.DISCH_DT_TM), 0)
  141. - COALESCE(HOUR(E.ORIG_ORDER_DT_TM), 0)) * 60 + COALESCE(MINUTE(E.DISCH_DT_TM), 0)
  142. - COALESCE(MINUTE(E.ORIG_ORDER_DT_TM), 0))
  143. AS ELAPSED_MIN
  144. --Output format: 1d 8h 43m
  145.  
  146. Day
  147. ---
  148. TRUNC(E.ADMIT_DATE, 'J') = '2017-01-01'
  149. TRUNC(E.ADMIT_DATE, 'DD') = '2017-01-01'
  150. TRUNC(E.ADMIT_DATE, 'DDD') = '2017-01-01'
  151. Start of Week
  152. ---
  153. TRUNC(E.ADMIT_DATE, 'D') = '2017-01-01'
  154. TRUNC(E.ADMIT_DATE, 'DAY') = '2017-01-01'
  155. TRUNC(E.ADMIT_DATE, 'DY') = '2017-01-01'
  156.  
  157. Vertica
  158. ===
  159. TO_DATE(SUBSTR(summaryYM, 6, 2) || '/' || '01' || '/' || SUBSTR(summaryYM, 1, 4), 'MM/DD/YYYY') AS summary_date
  160. , CASE MONTH(TO_DATE(SUBSTR(summaryYM, 6, 2) || '/' || '01' || '/' || SUBSTR(summaryYM, 1, 4), 'MM/DD/YYYY'))
  161.     WHEN 1 THEN 'Jan-' ||  SUBSTR(summaryYM, 3, 2)
  162.     WHEN 2 THEN 'Feb-' || SUBSTR(summaryYM, 3, 2)
  163.     WHEN 3 THEN 'Mar-' || SUBSTR(summaryYM, 3, 2)
  164.     WHEN 4 THEN 'Apr-' || SUBSTR(summaryYM, 3, 2)
  165.     WHEN 5 THEN 'May-' || SUBSTR(summaryYM, 3, 2)
  166.     WHEN 6 THEN 'Jun-' || SUBSTR(summaryYM, 3, 2)
  167.     WHEN 7 THEN 'Jul-' || SUBSTR(summaryYM, 3, 2)
  168.     WHEN 8 THEN 'Aug-' || SUBSTR(summaryYM, 3, 2)
  169.     WHEN 9 THEN 'Sep-' || SUBSTR(summaryYM, 3, 2)
  170.     WHEN 10 THEN 'Oct-' || SUBSTR(summaryYM, 3, 2)
  171.     WHEN 11 THEN 'Nov-' || SUBSTR(summaryYM, 3, 2)
  172.     WHEN 12 THEN 'Dec-' || SUBSTR(summaryYM, 3, 2)
  173.     END
  174. AS summary_mon_yr
  175.  
  176. PostgreSQL
  177. ===
  178. created_at at time zone 'utc' at time zone 'america/los_angeles'
  179.     created_at at time zone 'utc' at time zone 'US/Pacific'
  180.     source: https://popsql.com/learn-sql/postgresql/how-to-convert-utc-to-local-time-zone-in-postgresql/
  181. DATE_TRUNC('day', case_time_dt_tm) = '2015-03-05'
  182. DATE_TRUNC('month', case_time_dt_tm) = '2015-03-01' --Same as mo-yr
  183. DATE_PART('year', case_time_dt_tm) > 2012
  184. EXTRACT(HOUR FROM TIMESTAMP '2001-02-16 20:38:40') = 20
  185. EXTRACT(MONTH FROM INTERVAL '2 years 3 months') = 3
  186. EXTRACT(EPOCH FROM INTERVAL '2 years 3 months')/60 -- AS minutes
  187. surginet.room_in - '08:00:00'::interval AS room_in
  188. TO_DATE(datekey::varchar, 'YYYYMMDD') < TO_DATE(current_date::varchar, 'YYYYMMDD')
  189. TO_CHAR(CURRENT_DATE-1, 'YYYYMMDD')::DOUBLE PRECISION  -- returns yesterday's date as double precision, i.e. 19000101
  190. TO_TIMESTAMP(time_id::text, 'HH24MISS') -- returns "0001-01-01 10:00:00-07:52:58 BC"
  191. TO_TIMESTAMP(time_id::text, 'HH24MISS')::time AS transaction_time -- returns "10:00:00" - hours must be left padded with '0'
  192. TO_TIMESTAMP(LPAD(time_id::text, 6, '0'), 'HH24MISS')::time AS transaction_time
  193. CASE WHEN fct_charge_transaction.transaction_date_id != -1
  194.     THEN TO_DATE(fct_charge_transaction.transaction_date_id::text, 'YYYYMMDD')
  195.     ELSE '2100-01-01'::date
  196.     END
  197.     || ' '::text ||
  198.     TO_TIMESTAMP(LPAD(fct_charge_transaction.transaction_time_id::text, 6, '0'), 'HH24MISS')::time
  199.     AS transaction_date,
  200. "_background_tasks"."created_at" at time zone ('UTC') at time zone ('EST5EDT') AS "created_at"
  201. "_background_tasks"."created_at" at time zone ('UTC') at time zone ('PST8PDT') AS "created_at"
  202. AGE('now'::text::date::timestamp without time zone, e.birth) AS e_age,
  203.     result when e.birth = "1956-06-03 00:00:00" = "60 years 2 mons 2 days"
  204. DATE_PART('year', AGE('now'::text::date::timestamp without time zone, e.birth)) AS e_age,
  205.     result when e.birth = "1956-06-03 00:00:00" = "60"
  206. CURRENT_DATE
  207. CURRENT_TIME
  208. CURRENT_TIMESTAMP
  209. LOCALTIME
  210. LOCALTIMESTAMP
  211. NOW()
  212.  
  213. CCL
  214. ===
  215. CNVTLOOKBEHIND("1","W")
  216. O.ORIG_ORDER_DT_TM BETWEEN CNVTLOOKBEHIND("1,Y") AND CNVTDATETIME(CURDATE, CURTIME)
  217. O.ORIG_ORDER_DT_TM BETWEEN CNVTLOOKBEHIND("1,W") AND CNVTDATETIME(CURDATE, 235959)
  218. CNVTLOOKBEHIND("[number],[time period]") - options: D, W, M, Y
  219. CNVTDATETIME()
  220. CURDATE
  221. CURTIME
  222. CNVTAGE
  223. DATETIMEADD
  224. DATETIMEDIFF
  225. DATETIMECMP
  226. DATETIMEPART
  227. DATETIMETRUNC
  228. DATETIMEZONE
  229. DATETIMEZONEBYINDEX
  230. DATETIMEZONEBYNAME
  231. DATETIMEZONEFORMAT
  232. DATETIMEZONEUTC
  233. DAY
  234. HOUR
  235. JULIAN
  236. MINUTE
  237. MONTH
  238. WEEKDAY
  239. YEAR
  240.  
  241.  
  242.  
  243. MySQL
  244. ===
  245. SELECT * FROM `36_Action` WHERE DATE(`Time_Stamp`) = '2016-05-04'
  246.  
  247. DB2
  248. ===
  249. WHERE MACYR > YEAR(CURRENT_DATE)-5
  250.  
  251. Cerner
  252. ===
  253. Julian Date Conversion
  254. ===
  255. SELECT TO_DATE(17991229, 'YYYYMMDD') + 78675 AS activity_date FROM dual;  -- 5/26/2015
  256.  
  257. Julian Date Lookup
  258. ===
  259. OMF_DATE workaround
  260. SELECT * FROM omf_date WHERE dt_nbr = 78675; -- 5/26/2015
  261.     Postgres
  262.     ===
  263.     SELECT (current_date - '1-1-1800'::date) + 3; --this gives you today's date
  264.     SELECT (current_date - '1-1-1800'::date) + 1; --this gives you 2 days ago
  265.     Oracle
  266.     ===
  267.     SELECT TRUNC(SYSDATE) - TO_DATE('1-JAN-1800')) + 1 -- 2 days ago
Advertisement
Add Comment
Please, Sign In to add comment