Question & Answer
Question
What are some examples of date and timestamp functions?
Answer
Assuming now() to have been executed at '2006-08-24 09:17:59', you can expect the following values when working with Date Functions:
| Description | SQL Operation | OUTPUT |
| Year | date_part('year',now()) | 2006 |
| Month | date_part('month',now()) | 8 |
| Day | date_part('day',now()) | 24 |
| Month of year | to_char(now(),'month') | August |
| day of week | to_char(now(),'Day') | Thursday |
| Convert timestamp to date | date(now()) | 2006-08-24 |
| Convert timestamp to time | time(now()) | 09:17:59 |
| Convert char to date | to_date('20051121','YYYYMMDD') | 2005-11-21 |
| Truncate timestamp to year | date_trunc('year',now()) | 2006-01-01 00:00:00 |
| Truncate timestamp to month | date_trunc('month',now()) | 2006-08-01 00:00:00 |
| Truncate timestamp to day | date_trunc('day',now()) | 2006-08-24 00:00:00 |
| Truncate timestamp to hour | date_trunc('hour',now()) | 2006-08-24 09:00:00 |
| Date add to timestamp | now() + interval ’23 hours’ | 2006-08-25 08:28:22 |
| Date add to timestamp timestamp | '2001-08-28 01:00' + interval '23 hours' | 2001-08-29 00:00 |
| Date add to date column | date '2002-09-29' + interval '1 hour' | 2002-09-29 01:00 |
| Date and time to a timestamp | timestamp(date '1999-03-24',time '23:07') | 1998903-24 23:07:00 |
[{"Product":{"code":"SSULQD","label":"IBM PureData System"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Component":"Database","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"1.0.0","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]
Historical Number
NZ652323
Was this topic helpful?
Document Information
Modified date:
17 October 2019
UID
swg21567378