SQL Quiz: Date Timestamp Parsing
10 questions · exam conditions
0:00
Date Timestamp ParsingQuestion 1 of 10

An Oracle session is running in calendar year 2026. A legacy source supplies two-digit years, and the following expression is evaluated:

TO_CHAR(TO_DATE('49', 'RR'), 'YYYY') || '/' || TO_CHAR(TO_DATE('50', 'RR'), 'YYYY')

What value does the expression return under Oracle's RR year rules?

1949/1950, because both values are assigned to the previous century
2049/2050, because both values inherit the session's current century
2049/1950, because the RR pivot places the values in different centuries
1949/2050, because the RR pivot advances after the value 49
← Back to quizzes

SQL Quiz

SQL Quiz: Date Timestamp Parsing

Practice Date Timestamp Parsing in SQL with focused quiz questions that help you check what you know, review explanations, and build confidence with test-style prompts.

What this quiz covers

This quiz focuses on Date Timestamp Parsing, giving you a quick way to practice the rules, question types, and explanations that matter most for SQL.

How to use this quiz

Try each quiz question before looking at the correct answer. Use the explanations to review missed ideas, then come back to similar questions until the pattern feels familiar.

All questions

Question 1

An Oracle session is running in calendar year 2026. A legacy source supplies two-digit years, and the following expression is evaluated:

TO_CHAR(TO_DATE('49', 'RR'), 'YYYY') || '/' || TO_CHAR(TO_DATE('50', 'RR'), 'YYYY')

What value does the expression return under Oracle's RR year rules?

  1. 1949/1950, because both values are assigned to the previous century
  2. 2049/2050, because both values inherit the session's current century
  3. 2049/1950, because the RR pivot places the values in different centuries (correct answer)
  4. 1949/2050, because the RR pivot advances after the value 49
Explanation: Whenever you see Oracle's RR format, think of it as a pivot-based century selector, not a simple "inherit the current century" rule. The logic works like this: if the current year's last two digits are 00–49, then a two-digit input of 00–49 maps to the current century, and 50–99 maps to the previous century. Since the session is in 2026 (last two digits = 26, which falls in 00–49), the pivot point sits exactly at 50. This makes C correct: '49' falls below the pivot, so it stays in the current century → 2049. '50' hits the pivot threshold and flips to the previous century → 1950. The two values land on opposite sides of the cutoff, producing 2049/1950. A is wrong because it assumes both values go to the 1900s. Only '50' does; '49' is below the pivot and correctly resolves to 2049, not 1949. B is wrong because it assumes RR always inherits the current century, which is how YY behaves — not RR. The whole purpose of RR is to split centuries based on the pivot, and '50' triggers that split. D reverses the result: it puts '49' in the previous century and '50' in the current one, which is exactly backwards from how the pivot works. A reliable memory trick: RR with a current year in 00–49 means "50 and above go back, 49 and below stay forward." If you ever confuse RR with YY, remember that YY has no pivot — it always blindly uses the current century.

Question 2

A SQL Server query evaluates four character values using British/French date style 103:

13/02/2025 02/13/2025 29/02/2024 31/04/2025

It then computes COUNT(TRY_CONVERT(date, raw_text, 103)).

What count is returned?

  1. 1, because only the unambiguous non-leap-year value converts successfully
  2. 2, because one ordinary date and one valid leap-day date convert successfully (correct answer)
  3. 3, because style 103 accepts both day-first and month-first slash dates
  4. 4, because TRY_CONVERT normalizes invalid day and month combinations
Explanation: When working with TRY_CONVERT and date style codes, your job is to evaluate each value independently against the specified format rules — and remember that TRY_CONVERT returns NULL (not an error) for invalid values, which COUNT then silently ignores. Style 103 is the British/French format: DD/MM/YYYY. Let's walk through each value. 13/02/2025 parses as February 13, 2025 — a perfectly valid date, so it converts successfully. 02/13/2025 is interpreted as day=02, month=13 — but month 13 doesn't exist, so TRY_CONVERT returns NULL. 29/02/2024 means February 29, 2024. Since 2024 is a leap year (divisible by 4, not a century exception), this is valid and converts successfully. Finally, 31/04/2025 means April 31, 2025 — but April only has 30 days, making this invalid, so it also returns NULL. That leaves exactly two successful conversions, confirming answer B. Answer A is wrong because it assumes the leap-day value fails — but 2024 is genuinely a leap year, so 29/02/2024 is valid. Answer C is wrong because style 103 is strictly DD/MM/YYYY; it does not accept month-first ordering, so 02/13/2025 with an impossible month 13 fails rather than being reinterpreted. Answer D is wrong because TRY_CONVERT never normalizes or corrects bad data — it simply returns NULL for anything that doesn't parse cleanly. A useful rule of thumb: whenever you see TRY_CONVERT with COUNT, mentally filter each input through the format rules and calendar logic, and count only the NULLs it won't produce.

Question 3

An Oracle session has NLS_DATE_LANGUAGE set to French. A source column contains English month abbreviations such as 07-SEP-2025. The conversion must work without changing the session setting and must return an Oracle DATE.

Which expression most reliably performs the conversion?

  1. TO_DATE(raw_text, 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE=English') (correct answer)
  2. TO_DATE(raw_text, 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE=French')
  3. TO_DATE(raw_text, 'FXDD-MON-YYYY') using the session's current language
  4. TO_CHAR(raw_text, 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE=English')
Explanation: When working with TO_DATE in Oracle across multilingual environments, the key question is always: which language will Oracle use to interpret the month abbreviation? By default, Oracle uses the session's NLS_DATE_LANGUAGE setting — so if your session is French but your data contains English abbreviations like SEP, the conversion will fail or produce wrong results unless you explicitly override it. This is exactly what the three-argument form of TO_DATE is designed for. Answer A — TO_DATE(raw_text, 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE=English') — explicitly tells Oracle to interpret the month name using English, regardless of the session setting. This makes the conversion portable, predictable, and safe. It returns an Oracle DATE type, which is precisely what the question requires. Answer B uses NLS_DATE_LANGUAGE=French, which means Oracle will try to match SEP against French month abbreviations. Since SEP is not a valid French abbreviation (French uses SEPT), this will likely throw an ORA-01843: not a valid month error. Answer C uses the FX format modifier, which enforces exact format matching, but it still relies on the session's French language setting. It doesn't solve the core problem — Oracle will still fail to recognize English month names. Answer D is a fundamental mistake: TO_CHAR converts a date to a string, not a string to a date. Passing raw text to TO_CHAR is a type mismatch and will not return an Oracle DATE. As a study tip, remember: whenever you see locale-sensitive data conversions in Oracle, look for the NLS parameter argument in TO_DATE. Its presence — and its correct language value — is usually what separates the right answer from the traps.

Question 4

A BigQuery staging table contains dates in exactly two permitted forms: ISO YYYY-MM-DD and European DD/MM/YYYY. Invalid values must become NULL, and a slash-form value such as 04/03/2025 must mean 4 March 2025.

Which expression correctly converts the staging column raw_text to a DATE?

  1. COALESCE(SAFE.PARSE_DATE('%F', raw_text), SAFE.PARSE_DATE('%d/%m/%Y', raw_text)) (correct answer)
  2. COALESCE(SAFE.PARSE_DATE('%F', raw_text), SAFE.PARSE_DATE('%m/%d/%Y', raw_text))
  3. COALESCE(PARSE_DATE('%F', raw_text), SAFE.PARSE_DATE('%d/%m/%Y', raw_text))
  4. COALESCE(SAFE.FORMAT_DATE('%F', raw_text), SAFE.FORMAT_DATE('%d/%m/%Y', raw_text))
Explanation: When working with date parsing in BigQuery, you need to think about three separate concerns simultaneously: the right function family (PARSE_DATE vs FORMAT_DATE), the right format strings for each input pattern, and safe error handling for invalid values. PARSE_DATE converts a string into a DATE using a format pattern — that's what you need here. Its SAFE. prefix variant returns NULL instead of throwing an error when the input doesn't match the pattern. COALESCE then returns the first non-NULL result, letting you try ISO format first and fall back to the European slash format. Option A does exactly this: SAFE.PARSE_DATE('%F', raw_text) handles YYYY-MM-DD (since %F is shorthand for %Y-%m-%d), and SAFE.PARSE_DATE('%d/%m/%Y', raw_text) interprets the day first — correctly reading 04/03/2025 as March 4th, not April 3rd. That makes A the correct answer. Option B is the sneaky trap: it swaps %d and %m, using %m/%d/%Y for the slash format, which would interpret 04/03/2025 as April 3rd — a month/day/year (American) reading that directly violates the problem's European convention. Option C drops the SAFE. prefix on the first PARSE_DATE call. Without it, a value that fails the ISO pattern throws a runtime error rather than returning NULL and falling through to the next COALESCE branch. Option D uses FORMAT_DATE, which goes the opposite direction — it formats an existing DATE into a string. Passing raw text to it would cause a type error. Study tip: Always pair COALESCE with SAFE. prefixed parsing functions when handling multiple input formats — and double-check %d/%m/%Y vs %m/%d/%Y whenever a question specifies European versus American conventions.

Question 5

In PostgreSQL, the session time zone is UTC. On July 1, New York observes daylight-saving time with an offset of -04:00.

What does the following expression return?

to_char(timestamp '2025-07-01 09:00:00' AT TIME ZONE 'America/New_York', 'YYYY-MM-DD HH24:MI')

  1. 2025-07-01 05:00, because four hours are subtracted from the local timestamp
  2. 2025-07-01 09:00, because AT TIME ZONE changes only the displayed zone
  3. 2025-07-01 13:00, because New York local time is converted to a UTC instant (correct answer)
  4. 2025-07-01 14:00, because the standard-time offset of five hours is applied
Explanation: When you see AT TIME ZONE applied to a plain TIMESTAMP (no timezone info) in PostgreSQL, the key question to ask is: what direction does the conversion go? PostgreSQL treats the timestamp as a local time in the named zone and converts it outward to UTC, producing a TIMESTAMPTZ (a universal instant). Here, timestamp '2025-07-01 09:00:00' is treated as 9:00 AM New York local time. On July 1, New York is on EDT (UTC−4), so to express that moment in UTC you add 4 hours: 09:00 + 4 = 13:00 UTC. Since the session is already in UTC, to_char then formats that UTC instant as 2025-07-01 13:00, making C correct. A reverses the arithmetic — subtracting 4 hours would be the right move if you were converting from UTC into New York time, not the other way around. B reflects a common misconception that AT TIME ZONE is merely cosmetic; it actually performs a real arithmetic conversion, changing the underlying instant. D confuses daylight-saving time with standard time: New York in July uses EDT (−4), not EST (−5). Applying a 5-hour offset is only appropriate in winter months. A reliable mental model: when a bare TIMESTAMP meets AT TIME ZONE 'Zone', think "local → UTC, so add the zone's offset." If the zone is UTC−4, you add 4 hours. Memorize this direction flip — it's one of the most common traps in PostgreSQL timezone questions.

Question 6

In MySQL, the following expression parses a 12-hour timestamp and then formats it using a 24-hour clock:

DATE_FORMAT(STR_TO_DATE('2025-08-09 12:30 AM', '%Y-%m-%d %h:%i %p'), '%Y-%m-%d %H:%i')

What string does the expression return?

  1. 2025-08-09 00:30, because 12:30 AM is thirty minutes after midnight (correct answer)
  2. 2025-08-09 12:30, because the numeric hour remains unchanged for AM
  3. 2025-08-09 24:30, because midnight is represented by hour 24
  4. 2025-08-09 12:08, because %m and %i exchange month and minute values
Explanation: When working with MySQL date functions, the key is understanding how STR_TO_DATE parses a string into an internal datetime value, and how DATE_FORMAT then renders that value using format specifiers — these are two separate steps, and the conversion logic happens entirely in the first step. Here, STR_TO_DATE('2025-08-09 12:30 AM', '%Y-%m-%d %h:%i %p') uses %h (12-hour clock) combined with %p (AM/PM marker). In 12-hour convention, 12:30 AM means 30 minutes after midnight — that is, 00:30 in 24-hour time. MySQL correctly interprets this and stores the time internally as 00:30. Then DATE_FORMAT(..., '%Y-%m-%d %H:%i') uses %H (24-hour clock, zero-padded), which renders midnight's hour as 00, producing 2025-08-09 00:30 — making A correct. B is wrong because it assumes the 12 in 12:30 AM passes through unchanged. It doesn't — MySQL applies the 12-hour-to-24-hour conversion during parsing, and 12 AM becomes 00 in 24-hour time. C is wrong because 24:00 is not a valid hour in MySQL's datetime system; midnight is 00:00, not 24:00. D is wrong and describes a fictional behavior — %m (month) and %i (minutes) do not "swap" values; they are independent format tokens that each extract their respective fields without interfering with one another. A useful study tip: memorize the difference between %h/%H and always check whether %p is present. If you see %h without %p, AM/PM is ambiguous. When %p is present and the hour is 12 AM, the 24-hour output will always be 00.

Question 7

BigQuery initializes date fields omitted from a parsing format from 1970-01-01. Consider this expression:

COALESCE(SAFE.PARSE_DATE('%m-%d', '02-29'), DATE '2000-01-01')

What date does the expression return?

  1. 1970-02-28, because the invalid leap day is reduced to February's final day
  2. 1970-03-01, because the invalid leap day rolls into the following month
  3. The query raises an error because COALESCE cannot suppress a parsing failure
  4. 2000-01-01, because the implicit year makes parsing fail and the fallback is used (correct answer)
Explanation: When working with BigQuery's date parsing functions, you need to think carefully about two things: what the SAFE prefix does, and what happens when a format string is incomplete. PARSE_DATE('%m-%d', '02-29') uses a format that specifies only month and day, so BigQuery defaults the year to 1970. Here's the critical insight: 1970 is not a leap year, meaning February 29, 1970 does not exist. Because the SAFE prefix is used, instead of throwing an error, SAFE.PARSE_DATE silently returns NULL for the invalid date. COALESCE then receives NULL and falls back to its second argument, returning 2000-01-01. That makes D the correct answer. A is wrong because BigQuery doesn't silently clamp invalid dates to the nearest valid one — it fails and returns NULL under SAFE. No date like 1970-02-28 is ever produced. B is wrong for the same core reason: there's no "rollover" behavior where February 29 becomes March 1. The parse fails outright, not gracefully adjusts. C is wrong in a subtle but important way — it correctly identifies that parsing fails, but misunderstands COALESCE. COALESCE absolutely can handle a NULL returned by SAFE.PARSE_DATE; that's exactly the pattern SAFE is designed to enable. The error suppression already happens before COALESCE even evaluates. As a study tip, remember that SAFE.<function> converts errors into NULL — it doesn't fix bad data. Always ask yourself: is the implicit default (like a year of 1970) actually valid for the input provided?

Question 8

A Snowflake session evaluates the following expression:

TO_CHAR(CONVERT_TIMEZONE('UTC', TO_TIMESTAMP_TZ('2025-01-15 10:00:00 -0500', 'YYYY-MM-DD HH24:MI:SS TZHTZM')), 'YYYY-MM-DD HH24:MI')

What value is returned?

  1. 2025-01-15 05:00, because the five-hour offset is subtracted from the local time
  2. 2025-01-15 10:00, because conversion preserves the displayed wall-clock time
  3. 2025-01-15 15:00, because the parsed instant is converted from -05:00 to UTC (correct answer)
  4. 2025-01-16 03:00, because the offset is interpreted as five hours east of UTC
Explanation: Whenever you see nested timezone and timestamp functions in Snowflake, your job is to trace the instant in time through each transformation step-by-step, keeping the actual UTC moment fixed in your mind. Start by parsing the inner expression: TO_TIMESTAMP_TZ('2025-01-15 10:00:00 -0500', ...) produces a timestamp-with-timezone representing 10:00 AM at UTC−5, which is the same absolute instant as 15:00 UTC (add 5 hours to convert the negative offset to UTC). Next, CONVERT_TIMEZONE('UTC', ...) takes that absolute instant and re-expresses it in UTC — so the result is 2025-01-15 15:00:00 UTC. Finally, TO_CHAR(..., 'YYYY-MM-DD HH24:MI') formats that UTC timestamp as a string, giving you 2025-01-15 15:00. That confirms C is correct. Choice A claims the offset is subtracted from 10:00, yielding 05:00. This reverses the math — to go from local time to UTC with a −05:00 offset, you add 5 hours, not subtract. Choice B suggests conversion preserves the wall-clock display of 10:00. That would only be true if you converted to the same timezone, not a different one. Converting to UTC absolutely changes the displayed time. Choice D claims the offset is treated as +05:00 (east of UTC), which would produce 05:00 UTC, and then invents an impossible 24-hour jump. This misreads the negative sign entirely. Study tip: Always anchor on the absolute UTC instant first, then apply any timezone conversion. The sign of the offset tells you which direction to shift — a negative offset means the local clock is behind UTC, so UTC is ahead.

Question 9

A PostgreSQL report groups dates by ISO week. It formats DATE '2021-01-01' with to_char using ISO week-year fields rather than calendar-year fields.

Which expression and result correctly identify the ISO week containing this date?

  1. to_char(DATE '2021-01-01', 'YYYY-IW') returns 2021-53
  2. to_char(DATE '2021-01-01', 'IYYY-IW') returns 2020-53 (correct answer)
  3. to_char(DATE '2021-01-01', 'IYYY-WW') returns 2020-01
  4. to_char(DATE '2021-01-01', 'YYYY-WW') returns 2020-53
Explanation: When working with ISO week numbers in PostgreSQL's to_char, the critical distinction is between calendar-year fields (YYYY, WW) and ISO week-year fields (IYYY, IW). These must be paired correctly, and mixing them produces wrong results. January 1, 2021 falls on a Friday. Under ISO 8601 rules, a week belongs to the year containing its Thursday. The week of January 1, 2021 contains Thursday, December 31, 2020 — so that week belongs to ISO year 2020, and it's the 53rd week of that year. The correct format to capture this is IYYY-IW, which uses the ISO week-year for both the year and the week number, returning 2020-53. That makes B the correct answer. A is wrong because it mixes YYYY (calendar year, which gives 2021) with IW (ISO week number, which gives 53). Combining a calendar year with an ISO week number is semantically inconsistent and produces the misleading result 2021-53, implying the date belongs to week 53 of calendar year 2021 — which is false. C uses IYYY (ISO year, correctly 2020) but pairs it with WW, which is the calendar week-of-year counter, not the ISO week number. WW simply counts elapsed 7-day periods from January 1, so it returns 01 for January 1st — a different system entirely. D uses YYYY-WW, two purely calendar fields. This gives 2021-01, not 2020-53, so the result shown is also fabricated. The study tip here: always pair IYYY with IW. Mixing ISO and calendar format codes is one of the most common date-formatting traps in PostgreSQL.

Question 10

In PostgreSQL, a timestamptz value represents the instant 2025-07-01 13:00:00+00. While the session time zone is America/New_York, the value is formatted as text using YYYY-MM-DD HH24:MI:SS, which omits any offset. The session time zone is then changed to UTC, and that text is cast back to timestamptz.

How does the reparsed instant compare with the original instant?

  1. It is the same instant because formatting a timestamptz always preserves its original zone
  2. It is four hours earlier because the New York wall time is later interpreted as UTC (correct answer)
  3. It is four hours later because UTC parsing reapplies New York's daylight-saving offset
  4. It cannot be parsed because PostgreSQL requires an explicit offset for timestamptz input
Explanation: Whenever you work with timestamptz and text conversion in PostgreSQL, the critical concept is that the time zone context at formatting time and at parsing time both matter independently — and any mismatch creates a silent shift. Here's what happens step by step. The original instant is 2025-07-01 13:00:00 UTC. When you format it while the session is set to America/New_York (UTC−4 in summer), PostgreSQL first converts the instant to New York wall time: 09:00:00. The format mask YYYY-MM-DD HH24:MI:SS captures only that wall-clock string — 2025-07-01 09:00:00 — with no offset attached. Now the session switches to UTC, and you cast that bare string back to timestamptz. PostgreSQL sees no offset, so it assumes the session zone, UTC, and interprets 09:00:00 as 09:00:00 UTC — which is four hours earlier than the original 13:00:00 UTC. That confirms B is correct. A is wrong because formatting a timestamptz to a zone-free string does not preserve the original instant — the offset information is explicitly discarded by the format mask. C has the direction backwards; the reparsed time is earlier, not later, because a wall time that represented an earlier UTC hour (09:00) is now read as UTC itself. D is wrong because PostgreSQL happily accepts offset-free input for timestamptz — it simply assumes the current session time zone, which is exactly the source of the trap here. The study tip: treat any timestamptz→text→timestamptz round-trip as lossy by default unless your format string includes the offset (e.g., TZH:TZM or OF). Always verify that the session zone is consistent across both steps.