Teradata String To Date/Timestamp

Pradeep

Teradata String To Date/Timestamp

Teradata support ANSI Format to convert date/timestamp strings into date / timestamp datatypes. Various character and character strings (Metacharacters) are available in Teradata which can be used along with TO_DATE / TO_TIMESTAMP functions to convert strings into date/timestamp datatypes.

Below tables show most of the metacharacters that can used with TO_DATE / TO_TIMESTAMP function.

MetaCharacters	Description / Functionality
YYYY	Convert year in four digits(example: 1987)
YY	Convert year in two digits(example: 87)
MM	Convert month of the year in number format(example: 12)
MON	Convert month in 3 characters format(example: Jun)
MONTH	Convert full month name(example: June) format
DD	Convert day of the month in two digits(example: 30)
DDD	Convert day of the year(example: 276)
HH	Convert hour of the time in two digits(example: 17)
MI	Convert minutes of the time in two digits(example: 59)
SS	Convert seconds of the time in two digits(example: 58)
DY	Convert day of the week in 3 characters(example: Wed)
DAY	Convert full name of week day(example: Wednesday)
AM	Convert string timestamp with AM/PM format

All the below examples can be used convert strings into DATE/Timestamp datatypes

Example 1: Convert date string which is not in irregular format


SYNTAX: SELECT  TO_DATE('1-Oct-19','DD-MON-YY');
OUTPUT: 2019-10-01

Example 2: Convert date string in DD-MM-YYYY format to date datatype


SYNTAX: SELECT  TO_DATE('1-12-2019','DD-MM-YYYY')
OUTPUT: 2019-12-01

Example 3: Convert date string in DD-MON-YY format to date datatype


SYNTAX: SELECT  TO_DATE('12-Oct-19','DD-MON-YY');
OUTPUT: 2019-10-12

Example 4: Convert date string in DD-MON-YYYY format to date datatype


SYNTAX: SELECT  TO_DATE('12-Oct-2019','DD-MON-YYYY');
OUTPUT: 2019-10-12

Example 5: Convert date string in DD-MONTH-YY format to date datatype


SYNTAX: SELECT  TO_DATE('12-October-19','DD-MONTH-YY');
OUTPUT: 2019-10-12

Example 6: Convert date string in DD-MON-YY DY format to date datatype


SYNTAX: SELECT TO_DATE('11-Oct-19 Fri','DD-MON-YY DY');
OUTPUT: 2019-10-11

Example 7: Convert date string in DD-MON-YY DAY format to date datatype


SYNTAX: SELECT TO_DATE('11-Oct-19 Friday','DD-MON-YY DAY');
OUTPUT: 2019-10-11

Example 8: Convert date string in DDD-MON-YY format to date datatype, DDD--> Day of year
```
SYNTAX: SELECT TO_DATE('278-Oct-2019','DDD-Mon-YYYY');
OUTPUT: 2019-10-05
```

Example 9: Convert date string in DD-MON-YY HH:MI:SS(12 Hour) format to timestamp datatype


SYNTAX: SELECT TO_TIMESTAMP('1-Oct-19 10:12:11','DD-MON-YY HH:MI:SS');
OUTPUT: 2019-10-01 10:12:11.000000

Example 10: Convert date string in DD-MON-YY HH:MI:SS(24 Hour) format to timestamp datatype


SYNTAX: SELECT TO_TIMESTAMP('01-Oct-19 14:12:11','DD-MON-YY HH24:MI:SS');
OUTPUT: 2019-10-01 14:12:11.000000

Example 11: Convert date string in DD-MM-YY HH:MI:SS(12 Hour with AM/PM) format to timestamp datatype


SYNTAX: SELECT TO_TIMESTAMP('01-10-19 10:12:11 PM','DD-MM-YY HH:MI:SS AM');
OUTPUT: 2019-10-01 22:12:11.000000

Example 12: Convert date string with formatted timestamp with complete name of the weekday.


SYNTAX: SELECT TO_TIMESTAMP('Wednesday October 03,2018 12:05:47','DAY MONTH DD,YYYY HH24:MI:SS');
OUTPUT: 2018-10-03 12:05:47.000000