Useful SQL Date/Time Functions by Database Vendor
Keywords: sql dates, dates
SQL Server
===
CONVERT(datetimeoffset, CT.CASE_TIME_DT_TM AT TIME ZONE 'UTC' AT TIME ZONE 'Pacific Standard Time') AS CASE_TIME_DT_TM
--Takes care of PST-8/PDT-7 offset
submit_time AT TIME ZONE 'UTC' AT TIME ZONE 'Pacific Standard Time' AS submit_time_local
GETDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'Pacific Standard Time'
sc.sched_start_dt_tm >= CAST(SYSDATETIMEOFFSET() AT TIME ZONE 'Pacific Standard Time' AS DATE)
CT.CASE_TIME_DT_TM >= CAST(DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) - 2, 0) AS DATETIME) -- Last 2 years
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
CAST(FORMAT(UPDT_DT_TM, 'yyyyMMdd') AS INT) AS SUBSTR_RESULT -- 20250201
SUBSTRING(CAST(FORMAT(UPDT_DT_TM, 'yyyyMMdd') AS VARCHAR), 1, 6) AS SUBSTR_RESULT -- 202502
SUBSTRING(STR(DISCHARGE_DATE_ID), 1, 6) AS YEAR --2025
FORMAT(UPDT_DT_TM, 'HH:mm') --00:01
UPDT_DT_TM > DATEADD(DAY, -120, GETDATE()) --Last 120 days
CONVERT(VARCHAR(10), UPDT_DT_TM, 120) = '2025-03-06'
SELECT * FROM sys.time_zone_info
RECEIVED_DATE_LOCAL >= DATEFROMPARTS(YEAR(DATEADD(MONTH, -4, GETDATE())), MONTH(DATEADD(MONTH, -4, GETDATE())), 1) --last 4 months
convert(varchar, CAST(sc.surg_start_dt_tm at time zone 'UTC' at time zone 'Pacific Standard Time' as DATE), 112) >= '20200101'
POSTED_DATE_ID BETWEEN 20250101 AND CONVERT(VARCHAR, GETDATE() - 1, 112) --112
Style Code Format Output Example (2025-03-19)
0 (Default) Mon DD YYYY HH:MM:SS Mar 19 2025 11:19:00 AM
101 MM/DD/YYYY 03/19/2025
102 YYYY.MM.DD 2025.03.19
103 DD/MM/YYYY 19/03/2025
104 DD.MM.YYYY 19.03.2025
105 DD-MM-YYYY 19-03-2025
106 DD Mon YYYY 19 Mar 2025
107 Mon DD, YYYY Mar 19, 2025
108 HH:MM:SS 11:19:00
110 MM-DD-YYYY 03-19-2025
111 YYYY/MM/DD 2025/03/19
112 YYYYMMDD 20250319
Oracle - SQL Developer (Tools > Preferences > Database > NLS)
===
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
FROM_TZ(CAST(E.ARRIVE_DT_TM AS TIMESTAMP),'GMT') AT TIME ZONE 'US/Pacific' AS ARRIVAL_DATE --Takes care of PST/PDT
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
FROM_TZ(CAST(E.DISCH_DT_TM AS TIMESTAMP), 'GMT') AT TIME ZONE 'US/Pacific' AS DISCH_DT_TM
EXTRACT(YEAR FROM DE.BEG_EFFECTIVE_DT_TM) = 2014
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
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
CAST((FROM_TZ(CAST(E.ARRIVE_DT_TM AS TIMESTAMP),'GMT') AT TIME ZONE 'PST') AS DATE) AS ARRIVAL_DATE --PST only
TRUNC(SYSDATE, 'YEAR') - INTERVAL '1' YEAR --includes all of previous year
TRUNC(MONTHS_BETWEEN(SYSDATE,DOB)/12) AS AGE
TO_DATE('2014-11-11 08:00:00','YYYY-MM-DD HH24:MI:SS')
TO_TIMESTAMP('01-JAN-2016 00:00:00', 'DD-MON-YYYY HH24:MI:SS')
TO_CHAR(EA.ACTIVE_STATUS_DT_TM, 'YYYY-MM-DD HH24:MI:SS') > '2012-12-31 23:59:59'
TO_CHAR(E.DISCH_DT_TM, 'YYYYMMDD') AS DISCHARGE_DATE_ID
CAST(TO_CHAR(E.DISCH_DT_TM, 'YYYYMMDD') AS INT) AS DISCH_DATE_ID
SYSDATE - INTERVAL '8' HOUR AS SYSDATE -- Doesn't account for PST/PDT switch
SYSDATE - INTERVAL '2' DAY AS SYSDATE
NEW_TIME(TO_DATE('2018-08-02 07:13:57','yyyy/mm/dd hh24:mi:ss'),'PST','GMT') --Does not account for PDT
NEW_TIME(EA.ACTIVE_STATUS_DT_TM, 'GMT', 'PST') AS ACTIVE_STATUS_DT_TM --Does not account for PDT
TRUNC(SYSDATE)-7
TRUNC(E.ARRIVE_DT_TM) > '31-DEC-13'
TRUNC(D.FULLDATE, 'MONTH') = '02-01-2006 00:00:00'
SUBSTR(O.ORIG_ORDER_DT_TM, 8, 2) > '13'
SUBSTR(CT.CASE_TIME_DT_TM, 8, 2) AS YEAR
SUBSTR(O.ORIG_ORDER_DT_TM, 4, 6) = 'DEC-14'
SUBSTR(TRUNC(E.ARRIVE_DT_TM), 4, 6) AS MONTH_YEAR
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
CASE WHEN FCT_ENCOUNTER.DISCHARGE_DATE_ID = -1 THEN
NULL
ELSE TO_DATE(FCT_ENCOUNTER.DISCHARGE_DATE_ID, 'YYYYMMDD')
END DISCHARGE_DATE
CASE WHEN FCT_ENCOUNTER.DISCHARGE_DATE_ID = -1 THEN
NULL
ELSE TO_DATE(FCT_ENCOUNTER.DISCHARGE_DATE_ID || ' ' || LPAD(FCT_ENCOUNTER.DISCHARGE_TIME_ID, 6, 0), 'YYYYMMDD HH24:MI:SS')
END AS DISCHARGE_DATE_TIME
CASE SUBSTR(CT.CASE_TIME_DT_TM, 4, 3)
WHEN 'JAN' THEN 1
WHEN 'FEB' THEN 2
WHEN 'MAR' THEN 3
WHEN 'APR' THEN 4
WHEN 'MAY' THEN 5
WHEN 'JUN' THEN 6
WHEN 'JUL' THEN 7
WHEN 'AUG' THEN 8
WHEN 'SEP' THEN 9
WHEN 'OCT' THEN 10
WHEN 'NOV' THEN 11
WHEN 'DEC' THEN 12
ELSE 0
END AS MONTH
Oracle ELAPSED_TIME Examples:
Query:
TO_CHAR(EXTRACT(DAY FROM NUMTODSINTERVAL(DISCHARGE_DATE-ORDER_DATE, 'DAY')), 'FM00')
|| ':' ||
TO_CHAR(EXTRACT(HOUR FROM NUMTODSINTERVAL(DISCHARGE_DATE-ORDER_DATE, 'DAY')), 'FM00')
|| ':' ||
TO_CHAR(EXTRACT(MINUTE FROM NUMTODSINTERVAL(DISCHARGE_DATE-ORDER_DATE, 'DAY')), 'FM00')
|| ':' ||
TO_CHAR(EXTRACT(SECOND FROM NUMTODSINTERVAL(DISCHARGE_DATE-ORDER_DATE, 'DAY')), 'FM00')
AS ELAPSED_TIME
Output:
00d 05h 50m 25s
Query:
TO_CHAR(EXTRACT(DAY FROM NUMTODSINTERVAL(E.DISCH_DT_TM - E.ORIG_ORDER_DT_TM, 'DAY'))) || 'd ' ||
TO_CHAR(EXTRACT(HOUR FROM NUMTODSINTERVAL(E.DISCH_DT_TM - E.ORIG_ORDER_DT_TM, 'DAY'))) || 'h ' ||
TO_CHAR(EXTRACT(MINUTE FROM NUMTODSINTERVAL(E.DISCH_DT_TM - E.ORIG_ORDER_DT_TM, 'DAY'))) || 'm'
AS ELAPSED_TIME
Output:
0d 5h 50m
(E.DISCH_DT_TM - E.ORIG_ORDER_DT_TM) DAY TO SECOND AS ELAPSED_TIME_LITERAL
Output for 1d 8h 43m 13s:
+01 08:43:13.000000
Calculating Age:
ROUND(MONTHS_BETWEEN(TC.CHECKIN_DT_TM, P.BIRTH_DT_TM)/12) AS AGE_AT_ADMIT
Impala
===
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
CAST(CAST(last_updated AS TIMESTAMP) + interval 3 days AS STRING)
,trunc((case when transaction_date_id = -1 then null else
cast(concat(concat_ws('-', substr(cast(transaction_date_id as string),1,4), substr(cast(transaction_date_id as string),5,2),
substr(cast(transaction_date_id as string),7,2)),' ',concat_ws(':',substr(lpad(cast(charge.transaction_time_id as string),6,'000000'),1,2),
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
, DATEDIFF(E.DISCH_DT_TM, E.ORIG_ORDER_DT_TM) AS ELAPSED_DAYS
, 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,
((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)) * 60 + COALESCE(MINUTE(E.DISCH_DT_TM), 0)
- COALESCE(MINUTE(E.ORIG_ORDER_DT_TM), 0))
AS ELAPSED_MIN
--Output format: 1d 8h 43m
Day
---
TRUNC(E.ADMIT_DATE, 'J') = '2017-01-01'
TRUNC(E.ADMIT_DATE, 'DD') = '2017-01-01'
TRUNC(E.ADMIT_DATE, 'DDD') = '2017-01-01'
Start of Week
---
TRUNC(E.ADMIT_DATE, 'D') = '2017-01-01'
TRUNC(E.ADMIT_DATE, 'DAY') = '2017-01-01'
TRUNC(E.ADMIT_DATE, 'DY') = '2017-01-01'
Vertica
===
TO_DATE(SUBSTR(summaryYM, 6, 2) || '/' || '01' || '/' || SUBSTR(summaryYM, 1, 4), 'MM/DD/YYYY') AS summary_date
, CASE MONTH(TO_DATE(SUBSTR(summaryYM, 6, 2) || '/' || '01' || '/' || SUBSTR(summaryYM, 1, 4), 'MM/DD/YYYY'))
WHEN 1 THEN 'Jan-' || SUBSTR(summaryYM, 3, 2)
WHEN 2 THEN 'Feb-' || SUBSTR(summaryYM, 3, 2)
WHEN 3 THEN 'Mar-' || SUBSTR(summaryYM, 3, 2)
WHEN 4 THEN 'Apr-' || SUBSTR(summaryYM, 3, 2)
WHEN 5 THEN 'May-' || SUBSTR(summaryYM, 3, 2)
WHEN 6 THEN 'Jun-' || SUBSTR(summaryYM, 3, 2)
WHEN 7 THEN 'Jul-' || SUBSTR(summaryYM, 3, 2)
WHEN 8 THEN 'Aug-' || SUBSTR(summaryYM, 3, 2)
WHEN 9 THEN 'Sep-' || SUBSTR(summaryYM, 3, 2)
WHEN 10 THEN 'Oct-' || SUBSTR(summaryYM, 3, 2)
WHEN 11 THEN 'Nov-' || SUBSTR(summaryYM, 3, 2)
WHEN 12 THEN 'Dec-' || SUBSTR(summaryYM, 3, 2)
END
AS summary_mon_yr
PostgreSQL
===
created_at at time zone 'utc' at time zone 'america/los_angeles'
created_at at time zone 'utc' at time zone 'US/Pacific'
source: https://popsql.com/learn-sql/postgresql/how-to-convert-utc-to-local-time-zone-in-postgresql/
DATE_TRUNC('day', case_time_dt_tm) = '2015-03-05'
DATE_TRUNC('month', case_time_dt_tm) = '2015-03-01' --Same as mo-yr
DATE_PART('year', case_time_dt_tm) > 2012
EXTRACT(HOUR FROM TIMESTAMP '2001-02-16 20:38:40') = 20
EXTRACT(MONTH FROM INTERVAL '2 years 3 months') = 3
EXTRACT(EPOCH FROM INTERVAL '2 years 3 months')/60 -- AS minutes
surginet.room_in - '08:00:00'::interval AS room_in
TO_DATE(datekey::varchar, 'YYYYMMDD') < TO_DATE(current_date::varchar, 'YYYYMMDD')
TO_CHAR(CURRENT_DATE-1, 'YYYYMMDD')::DOUBLE PRECISION -- returns yesterday's date as double precision, i.e. 19000101
TO_TIMESTAMP(time_id::text, 'HH24MISS') -- returns "0001-01-01 10:00:00-07:52:58 BC"
TO_TIMESTAMP(time_id::text, 'HH24MISS')::time AS transaction_time -- returns "10:00:00" - hours must be left padded with '0'
TO_TIMESTAMP(LPAD(time_id::text, 6, '0'), 'HH24MISS')::time AS transaction_time
CASE WHEN fct_charge_transaction.transaction_date_id != -1
THEN TO_DATE(fct_charge_transaction.transaction_date_id::text, 'YYYYMMDD')
ELSE '2100-01-01'::date
END
|| ' '::text ||
TO_TIMESTAMP(LPAD(fct_charge_transaction.transaction_time_id::text, 6, '0'), 'HH24MISS')::time
AS transaction_date,
"_background_tasks"."created_at" at time zone ('UTC') at time zone ('EST5EDT') AS "created_at"
"_background_tasks"."created_at" at time zone ('UTC') at time zone ('PST8PDT') AS "created_at"
AGE('now'::text::date::timestamp without time zone, e.birth) AS e_age,
result when e.birth = "1956-06-03 00:00:00" = "60 years 2 mons 2 days"
DATE_PART('year', AGE('now'::text::date::timestamp without time zone, e.birth)) AS e_age,
result when e.birth = "1956-06-03 00:00:00" = "60"
CURRENT_DATE
CURRENT_TIME
CURRENT_TIMESTAMP
LOCALTIME
LOCALTIMESTAMP
NOW()
CCL
===
CNVTLOOKBEHIND("1","W")
O.ORIG_ORDER_DT_TM BETWEEN CNVTLOOKBEHIND("1,Y") AND CNVTDATETIME(CURDATE, CURTIME)
O.ORIG_ORDER_DT_TM BETWEEN CNVTLOOKBEHIND("1,W") AND CNVTDATETIME(CURDATE, 235959)
CNVTLOOKBEHIND("[number],[time period]") - options: D, W, M, Y
CNVTDATETIME()
CURDATE
CURTIME
CNVTAGE
DATETIMEADD
DATETIMEDIFF
DATETIMECMP
DATETIMEPART
DATETIMETRUNC
DATETIMEZONE
DATETIMEZONEBYINDEX
DATETIMEZONEBYNAME
DATETIMEZONEFORMAT
DATETIMEZONEUTC
DAY
HOUR
JULIAN
MINUTE
MONTH
WEEKDAY
YEAR
MySQL
===
SELECT * FROM `36_Action` WHERE DATE(`Time_Stamp`) = '2016-05-04'
DB2
===
WHERE MACYR > YEAR(CURRENT_DATE)-5
Cerner
===
Julian Date Conversion
===
SELECT TO_DATE(17991229, 'YYYYMMDD') + 78675 AS activity_date FROM dual; -- 5/26/2015
Julian Date Lookup
===
OMF_DATE workaround
SELECT * FROM omf_date WHERE dt_nbr = 78675; -- 5/26/2015
Postgres
===
SELECT (current_date - '1-1-1800'::date) + 3; --this gives you today's date
SELECT (current_date - '1-1-1800'::date) + 1; --this gives you 2 days ago
Oracle
===
SELECT TRUNC(SYSDATE) - TO_DATE('1-JAN-1800')) + 1 -- 2 days ago
Comments