TO_DATE and TO_TIMESTAMP for parsing in PostgreSQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to convert the string '2024-06-15' to a date using TO_DATE.

PostgreSQL

SELECT TO_DATE('2024-06-15', [1]);

Drag options to blanks, or click blank then click option'

A'YYYY-MM-DD'

B'MM-DD-YYYY'

C'DD-MM-YYYY'

D'YY-MM-DD'

Attempts:

3 left

💡 Hint

Common Mistakes

Using the wrong order of year, month, and day in the format string.

Using two-digit year 'YY' instead of four-digit 'YYYY'.

The format string 'YYYY-MM-DD' matches the input string '2024-06-15' for correct date parsing.

2fill in blank

medium

Complete the code to convert the string '15/06/2024 14:30:00' to a timestamp using TO_TIMESTAMP.

PostgreSQL

SELECT TO_TIMESTAMP('15/06/2024 14:30:00', [1]);

Drag options to blanks, or click blank then click option'

A'DD-MM-YYYY HH24:MI:SS'

B'DD/MM/YYYY HH24:MI:SS'

C'MM/DD/YYYY HH12:MI:SS'

D'YYYY/MM/DD HH24:MI:SS'

Attempts:

3 left

💡 Hint

Common Mistakes

Using dashes '-' instead of slashes '/' in the format string.

Using 12-hour format 'HH12' without AM/PM indicator.

The format string 'DD/MM/YYYY HH24:MI:SS' matches the input string with day, month, year separated by slashes and 24-hour time.

3fill in blank

hard

Fix the error in the code to correctly parse '2024-06-15 09:45:00' as a timestamp.

PostgreSQL

SELECT TO_TIMESTAMP('2024-06-15 09:45:00', [1]);

Drag options to blanks, or click blank then click option'

A'YYYY-MM-DD HH24:MI:SS'

B'YYYY-MM-DD HH12:MI:SS'

C'YYYY/MM/DD HH24:MI:SS'

D'YY-MM-DD HH24:MI:SS'

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'HH12' without AM/PM indicator causes wrong parsing.

Using slashes '/' instead of dashes '-' in the date format.

The input uses 24-hour time format, so 'HH24' is needed instead of 'HH12'. Also, the date separator is a dash '-'.

4fill in blank

hard

Fill all three blanks to parse '06-15-2024 11:59 PM' as a timestamp correctly.

PostgreSQL

SELECT TO_TIMESTAMP('06-15-2024 11:59 PM', [1] || ' ' || [2] || ' ' || [3]);

Drag options to blanks, or click blank then click option'

A'MM-DD-YYYY'

B'HH12:MI'

C'PM'

D'HH24:MI'

Attempts:

3 left

💡 Hint

Common Mistakes

Using 24-hour format 'HH24' when the input has 'PM'.

Forgetting to include the 'PM' meridian indicator in the format string.

The date part is 'MM-DD-YYYY', the time part is 'HH12:MI', and the meridian indicator is 'PM'. The full format string 'MM-DD-YYYY HH12:MI PM' matches the input exactly.

5fill in blank

hard

Fill all three blanks to create a query that parses '2024/06/15 23:59:59' as a timestamp.

PostgreSQL

SELECT TO_TIMESTAMP('2024/06/15 23:59:59', [1] || ' ' || [2] || ':' || [3]);

Drag options to blanks, or click blank then click option'

A'YYYY/MM/DD'

B'HH24'

C'MI:SS'

D'HH12'

Attempts:

3 left

💡 Hint

Common Mistakes

Using 12-hour format 'HH12' without AM/PM.

Separating minutes and seconds incorrectly.

The date uses slashes with year/month/day, the hour is 24-hour format 'HH24', and minutes and seconds are 'MI:SS'.