Essential SQL Date and Time Functions for Analytics Quiz

Explore key SQL date and time handling concepts, including extracting components, formatting, arithmetic, and filtering, with practical examples inspired by Snowflake SQL usage. This quiz covers common interview topics for data analysts and engineers on manipulating dates and timestamps using SQL.

1. Getting the Current Date

   Which SQL function returns the current date in Snowflake, useful for filtering recent records?

   1. CURRENT_DATE
   2. NOW_DATE
   3. CUR_DATE
   4. TODAY()

   Explanation: The CURRENT_DATE function provides the current date in SQL and is widely used for time-based filtering. The option NOW_DATE is not a recognized function; CUR_DATE is common in other systems but not in standard SQL or Snowflake. TODAY() is not a valid built-in function in Snowflake SQL.

2. Extracting Year from a Date

   What is the correct way to extract the year from a date column called signup_date in SQL?

   1. EXTRACT(YEAR FROM signup_date)
   2. GETYEAR(signup_date)
   3. YEAR(signup_date, 'YYYY')
   4. PART(YEAR, signup_date)

   Explanation: EXTRACT(YEAR FROM signup_date) is the correct syntax in SQL for extracting the year component. GETYEAR is not a valid function in standard or Snowflake SQL. YEAR with two arguments is incorrect syntax. PART() is not a supported function for this purpose.

3. Adding Days to a Date

   Which function and arguments would you use to add 7 days to an order_date in SQL?

   1. DATEADD(DAY, 7, order_date)
   2. ADD_DAYS(order_date, 7)
   3. DATEPLUS(order_date, 7)
   4. PLUS_DAYS(7, order_date)

   Explanation: DATEADD takes the unit (DAY), amount (7), and base date (order_date) as parameters in order. ADD_DAYS and DATEPLUS are not standard in Snowflake. PLUS_DAYS also does not follow standard SQL or Snowflake function names.

4. Subtracting Months from the Current Date

   How would you subtract one month from the current date using SQL functions?

   1. DATEADD(MONTH, -1, CURRENT_DATE)
   2. SUB_MONTH(CURRENT_DATE, 1)
   3. DATEADD(MONTHS, -1, CURRENT_DATE)
   4. MINUS_MONTH(1, CURRENT_DATE)

   Explanation: The DATEADD function with 'MONTH' and a negative value (-1) subtracts one month from the date provided. SUB_MONTH and MINUS_MONTH are not valid functions, and MONTHS is not the correct unit name—it should be singular.

5. Finding the Number of Days Between Two Dates

   Which SQL function calculates the difference in days between shipped_date and delivered_date?

   1. DATEDIFF(DAY, shipped_date, delivered_date)
   2. DATE_DIFFERENCE(shipped_date, delivered_date, 'DAY')
   3. DAYS_BETWEEN(shipped_date, delivered_date)
   4. DATEDAY(shipped_date, delivered_date)

   Explanation: DATEDIFF with 'DAY' specified calculates the number of days between two dates in SQL and Snowflake. DATE_DIFFERENCE and DAYS_BETWEEN might seem logical but are not actual function names in Snowflake. DATEDAY is not a standard function.

6. Converting a String to a Date

   What is the correct syntax to convert the string '2024-11-01' into a date using the format 'YYYY-MM-DD'?

   1. TO_DATE('2024-11-01', 'YYYY-MM-DD')
   2. CAST_DATE('2024-11-01', 'YYYY-MM-DD')
   3. DATEFORMAT('2024-11-01', 'YYYY-MM-DD')
   4. CONVERT_DATE('2024-11-01', 'YYYY-MM-DD')

   Explanation: TO_DATE with the string and its matching format is the correct way to parse dates from text in SQL. CAST_DATE, DATEFORMAT, and CONVERT_DATE are not recognized functions in Snowflake SQL for this operation.

7. Formatting a Date for Display

   How do you format a date column order_date to display as 'YYYY-MM' in SQL?

   1. TO_VARCHAR(order_date, 'YYYY-MM')
   2. FORMAT_DATE(order_date, 'YYYY-MM')
   3. CONVERT(order_date, 'YYYY-MM')
   4. SHOWDATE(order_date, 'YYYY-MM')

   Explanation: TO_VARCHAR with the specified format is used for formatting dates as strings, such as 'YYYY-MM'. FORMAT_DATE, CONVERT, and SHOWDATE do not provide this functionality in Snowflake SQL.

8. Grouping Records by Month

   Which SQL function helps group sales data by the beginning of each month from the order_date column?

   1. DATE_TRUNC('MONTH', order_date)
   2. MONTH_START(order_date)
   3. TRUNC_MONTH(order_date)
   4. GROUP_BY_MONTH(order_date)

   Explanation: DATE_TRUNC with the 'MONTH' argument returns the first day of the month for a given date, making it ideal for grouping. MONTH_START, TRUNC_MONTH, and GROUP_BY_MONTH are not standard or recognized Snowflake SQL functions.

9. Filtering for the Last 30 Days

   Which SQL condition filters records from the last 30 days based on order_date?

   1. order_date >= DATEADD(DAY, -30, CURRENT_DATE)
   2. order_date > DATE_SUB(CURRENT_DATE, 30)
   3. order_date BETWEEN CURRENT_DATE AND 30
   4. order_date = LAST30DAYS()

   Explanation: Comparing order_date to the result of DATEADD(DAY, -30, CURRENT_DATE) accurately filters the last 30 days. DATE_SUB is not used in Snowflake. BETWEEN and LAST30DAYS() are incorrect in this context.

10. Extracting the Month from a Date

    What is the correct way to extract the month from a signup_date column in SQL?

    1. EXTRACT(MONTH FROM signup_date)
    2. MONTHNAME(signup_date)
    3. GETMONTH(signup_date)
    4. MONTHOFYEAR(signup_date)

    Explanation: EXTRACT(MONTH FROM signup_date) provides the numeric month. MONTHNAME returns the month's name if available, while GETMONTH and MONTHOFYEAR are not valid in Snowflake SQL.

11. Extracting the Day from a Date

    How do you extract the day component from a signup_date in SQL?

    1. EXTRACT(DAY FROM signup_date)
    2. DAYPART(signup_date)
    3. DAYOFMONTH(signup_date)
    4. DAY_NAME(signup_date)

    Explanation: EXTRACT(DAY FROM signup_date) returns the day's number. DAYPART is not a standard function, DAYOFMONTH is unsupported in Snowflake SQL, and DAY_NAME typically returns a weekday name, not the day number.

12. Filtering Records for the Current Year

    Which SQL condition finds records where order_date is in the current year?

    1. EXTRACT(YEAR FROM order_date) = EXTRACT(YEAR FROM CURRENT_DATE)
    2. order_date = YEAR(CURRENT_DATE)
    3. YEAR(order_date) BETWEEN THISYEAR AND NEXTYEAR
    4. order_date > STARTOFYEAR(CURRENT_DATE)

    Explanation: Comparing the year part of order_date to the current year ensures records are from the same year. The other options use functions or keywords that are not part of standard or Snowflake SQL.

13. Getting the Current Timestamp

    Which function returns both the current date and time in SQL, useful for timestamping events?

    1. CURRENT_TIMESTAMP
    2. NOW_DATE
    3. DATE_TIME()
    4. TIME_NOW()

    Explanation: CURRENT_TIMESTAMP gives the current date and time. NOW_DATE, DATE_TIME(), and TIME_NOW() are not valid functions in Snowflake SQL for this purpose.

14. Using Interval Arithmetic for Reports

    Which SQL statement finds all sales records from the previous month based on today's date?

    1. order_date >= DATEADD(MONTH, -1, CURRENT_DATE) AND order_date < CURRENT_DATE
    2. order_date = LAST_MONTH(CURRENT_DATE)
    3. order_date BETWEEN CURMONTH AND LASTMONTH
    4. order_date > MINUS_MONTH(CURRENT_DATE, 1)

    Explanation: Using DATEADD(MONTH, -1, CURRENT_DATE) gives the same day of last month. The provided range captures all days from last month up to but excluding today. The other options reference incorrect or unsupported functions and keywords.

15. Converting Timestamps to Date

    What is the correct function to convert a timestamp value to a date value in SQL?

    1. CAST(timestamp_column AS DATE)
    2. TIMESTAMP_TO_DATE(timestamp_column)
    3. DATECAST(timestamp_column)
    4. CONVERT_DATEONLY(timestamp_column)

    Explanation: CAST(timestamp_column AS DATE) safely converts timestamps, discarding the time portion. TIMESTAMP_TO_DATE, DATECAST, and CONVERT_DATEONLY are not standard functions in Snowflake for this operation.

16. Formatting Output for Month and Year Trend

    How would you display a transaction_date as a three-letter month and four-digit year, like 'Jan-2024', in SQL?

    1. TO_VARCHAR(transaction_date, 'Mon-YYYY')
    2. FORMAT(transaction_date, 'MMM-YYYY')
    3. DATE_TO_STRING(transaction_date, 'MON-YYYY')
    4. MONTHYEAR_FORMAT(transaction_date)

    Explanation: TO_VARCHAR with 'Mon-YYYY' formats the date as desired. FORMAT, DATE_TO_STRING, and MONTHYEAR_FORMAT are not valid formatting functions in Snowflake SQL.