SQL Date Functions Basics Quiz

1. What does the GETDATE() function return in SQL Server?

The current system date and time

The date from a specific table column

A formatted date string

The number of days since 1900

GETDATE() is a built-in SQL Server function that retrieves the current date and time from the system clock of the server where the SQL Server instance is running. This allows users to access real-time date and time information for various database operations, such as logging, timestamps, or time-based calculations.

Explanation

GETDATE() is a built-in SQL Server function that retrieves the current date and time from the system clock of the server where the SQL Server instance is running. This allows users to access real-time date and time information for various database operations, such as logging, timestamps, or time-based calculations.

2. Which function calculates the difference between two dates in SQL?

DATE_SUB()

DATEDIFF()

DATE_CALC()

SUBTRACT_DATES()

DATEDIFF() is a SQL function specifically designed to calculate the difference between two dates. It returns the number of days between the two specified date values, making it a straightforward choice for date comparisons and calculations in SQL queries. Other options listed do not serve this specific purpose.

Explanation

DATEDIFF() is a SQL function specifically designed to calculate the difference between two dates. It returns the number of days between the two specified date values, making it a straightforward choice for date comparisons and calculations in SQL queries. Other options listed do not serve this specific purpose.

3. In SQL Server, DATEDIFF(DAY, '2024-01-01', '2024-01-10') returns ____.

DATEDIFF calculates the difference between two dates based on the specified date part, which is DAY in this case. It counts the number of day boundaries crossed between '2024-01-01' and '2024-01-10'. Since there are 9 full days between these two dates, the result is 9.

Explanation

DATEDIFF calculates the difference between two dates based on the specified date part, which is DAY in this case. It counts the number of day boundaries crossed between '2024-01-01' and '2024-01-10'. Since there are 9 full days between these two dates, the result is 9.

Submit

4. What does DATEADD(MONTH, 3, '2024-01-15') do?

Subtracts 3 months from the date

Adds 3 months to the date

Returns the month value

Compares dates by month

DATEADD is a function used in SQL to manipulate date values. When it takes the arguments MONTH, 3, and '2024-01-15', it adds 3 months to the specified date. Thus, the resulting date would be '2024-04-15', demonstrating how the function effectively shifts the date forward by the specified number of months.

Explanation

DATEADD is a function used in SQL to manipulate date values. When it takes the arguments MONTH, 3, and '2024-01-15', it adds 3 months to the specified date. Thus, the resulting date would be '2024-04-15', demonstrating how the function effectively shifts the date forward by the specified number of months.

5. Which date part unit is NOT valid in DATEDIFF()?

YEAR

QUARTER

WEEK

DECADE

In the DATEDIFF() function, valid date part units include YEAR, QUARTER, and WEEK, which are commonly used to calculate the difference between two dates. However, DECADE is not a recognized unit in this context, as it is not typically used for date calculations in standard SQL practices.

Explanation

In the DATEDIFF() function, valid date part units include YEAR, QUARTER, and WEEK, which are commonly used to calculate the difference between two dates. However, DECADE is not a recognized unit in this context, as it is not typically used for date calculations in standard SQL practices.

6. The DATE_FORMAT() function in MySQL formats a date using a format string. What does '%Y' represent?

Two-digit year

Four-digit year

Day of year

Week number

In MySQL's DATE_FORMAT() function, '%Y' is used to format a date as a four-digit year. This allows for the representation of years in a full numeric format (e.g., 2023), ensuring clarity and avoiding confusion that can arise from using two-digit year formats.

Explanation

In MySQL's DATE_FORMAT() function, '%Y' is used to format a date as a four-digit year. This allows for the representation of years in a full numeric format (e.g., 2023), ensuring clarity and avoiding confusion that can arise from using two-digit year formats.

7. In SQL, EOMONTH() returns the ____ of a specified month.

EOMONTH() is a SQL function that calculates the last day of a specified month. It takes a date as input and returns the last date of that month, making it useful for financial calculations, reporting, and date manipulations where the end of the month is relevant.

Explanation

EOMONTH() is a SQL function that calculates the last day of a specified month. It takes a date as input and returns the last date of that month, making it useful for financial calculations, reporting, and date manipulations where the end of the month is relevant.

Submit

8. True or False: DATEADD() can add negative intervals to move backward in time.

True

False

DATEADD() is a SQL function that allows users to add or subtract time intervals from a date. By using negative values for the interval parameter, it effectively moves the date backward in time. This functionality makes it versatile for calculations involving both future and past dates.

Explanation

DATEADD() is a SQL function that allows users to add or subtract time intervals from a date. By using negative values for the interval parameter, it effectively moves the date backward in time. This functionality makes it versatile for calculations involving both future and past dates.

9. What does SYSDATETIME() return compared to GETDATE()?

A less precise datetime2 value

A more precise datetime2 value

The same result with different formatting

The system time zone offset

SYSDATETIME() returns the current date and time as a datetime2 data type, which has a higher precision (up to 100 nanoseconds) compared to GETDATE(), which returns a datetime value with a precision of only 3.33 milliseconds. This makes SYSDATETIME() more suitable for applications requiring finer time measurement.

Explanation

SYSDATETIME() returns the current date and time as a datetime2 data type, which has a higher precision (up to 100 nanoseconds) compared to GETDATE(), which returns a datetime value with a precision of only 3.33 milliseconds. This makes SYSDATETIME() more suitable for applications requiring finer time measurement.

10. Which function extracts the year from a date in most SQL databases?

GET_YEAR()

EXTRACT(YEAR FROM date)

YEAR(date) or EXTRACT(YEAR FROM date)

DATE_YEAR()

In most SQL databases, the functions YEAR(date) and EXTRACT(YEAR FROM date) are used to retrieve the year component from a date value. These functions allow users to isolate the year for analysis or reporting, making them essential for date manipulation in SQL queries.

Explanation

In most SQL databases, the functions YEAR(date) and EXTRACT(YEAR FROM date) are used to retrieve the year component from a date value. These functions allow users to isolate the year for analysis or reporting, making them essential for date manipulation in SQL queries.

11. CONVERT(VARCHAR, '2024-01-15', 101) in SQL Server formats the date as ____.

The CONVERT function in SQL Server is used to change the data type of an expression. When converting a date to VARCHAR with style 101, it formats the date in the 'MM/DD/YYYY' format, which is commonly used in the United States. Thus, '2024-01-15' is represented as '01/15/2024'.

Explanation

The CONVERT function in SQL Server is used to change the data type of an expression. When converting a date to VARCHAR with style 101, it formats the date in the 'MM/DD/YYYY' format, which is commonly used in the United States. Thus, '2024-01-15' is represented as '01/15/2024'.

Submit

12. True or False: DATEDIFF() always returns a positive value regardless of argument order.

True

False

DATEDIFF() calculates the difference between two dates and can return a negative value if the first date argument is later than the second. Therefore, the result depends on the order of the arguments, making the statement false.

Explanation

DATEDIFF() calculates the difference between two dates and can return a negative value if the first date argument is later than the second. Therefore, the result depends on the order of the arguments, making the statement false.