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