Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 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
Advertisement
Add Comment
Please, Sign In to add comment