CURRENT_DATE and CURRENT_TIMESTAMP in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

What is the output of CURRENT_DATE?

Consider the SQL query:

SELECT CURRENT_DATE;

What type of value does this query return?

SQL

SELECT CURRENT_DATE;

A The current date and time as a timestamp (e.g., '2024-06-15 14:30:00')

B A string showing the day of the week (e.g., 'Monday')

C The current time only (e.g., '14:30:00')

D The current date as a date value without time (e.g., '2024-06-15')

💡 Hint

CURRENT_DATE returns only the date part, no time included.

CURRENT_DATE returns the current date without any time information. It is a date type value.

❓ query_result

intermediate

What does CURRENT_TIMESTAMP return?

Look at this SQL query:

SELECT CURRENT_TIMESTAMP;

What kind of value will this return?

SQL

SELECT CURRENT_TIMESTAMP;

A The current date and time as a timestamp (e.g., '2024-06-15 14:30:00')

B The current time only (e.g., '14:30:00')

C The current date only (e.g., '2024-06-15')

D The current year as a number (e.g., 2024)

💡 Hint

CURRENT_TIMESTAMP includes both date and time.

CURRENT_TIMESTAMP returns the current date and time as a timestamp value.

🧠 Conceptual

advanced

Difference between CURRENT_DATE and CURRENT_TIMESTAMP

Which statement correctly describes the difference between CURRENT_DATE and CURRENT_TIMESTAMP in SQL?

A Both return the same value but in different formats.

B CURRENT_DATE returns date and time, while CURRENT_TIMESTAMP returns only the date.

C CURRENT_DATE returns only the date, while CURRENT_TIMESTAMP returns date and time.

D CURRENT_DATE returns the time only, CURRENT_TIMESTAMP returns date only.

💡 Hint

Think about what parts of the date/time each function includes.

CURRENT_DATE returns only the date part without time. CURRENT_TIMESTAMP returns both date and time.

📝 Syntax

advanced

Which SQL query correctly uses CURRENT_TIMESTAMP to insert current date and time?

You want to insert the current date and time into a table named 'events' with a column 'event_time'. Which query is correct?

A INSERT INTO events (event_time) VALUES (CURRENT_TIMESTAMP);

B INSERT INTO events (event_time) VALUES CURRENT_TIMESTAMP;

C INSERT INTO events (event_time) VALUES 'CURRENT_TIMESTAMP';

D INSERT INTO events (event_time) VALUES (CURRENT_DATE);

💡 Hint

Remember to use parentheses around function calls in VALUES.

Option A correctly uses CURRENT_TIMESTAMP as a function call inside VALUES with parentheses.

🔧 Debug

expert

Why does this query cause an error?

Given the query:

SELECT CURRENT_DATE + CURRENT_TIMESTAMP;

Why does this cause an error?

SQL

SELECT CURRENT_DATE + CURRENT_TIMESTAMP;

A The query is missing a FROM clause.

B You cannot add a date and a timestamp directly; they are different types.

C CURRENT_DATE and CURRENT_TIMESTAMP are not valid SQL functions.

D The plus sign (+) is not allowed in SQL queries.

💡 Hint

Think about the data types and what operations are allowed between them.

You cannot add a date and a timestamp directly because they are different data types. This causes a type mismatch error.

Practice

1. What does the SQL function CURRENT_DATE return?

easy

A. The current date without the time part

B. The current time without the date part

C. The current date and time together

D. The date and time of the last database update

Solution

1. Step 1: Understand the function purpose

   CURRENT_DATE is designed to return only the date part of the current moment.

2. Step 2: Compare with other functions

   CURRENT_TIMESTAMP returns date and time, but CURRENT_DATE excludes time.

3. Final Answer:

   The current date without the time part -> Option A

4. Quick Check:

   CURRENT_DATE = date only [OK]

Hint: CURRENT_DATE returns only date, no time included [OK]

Common Mistakes:

• Confusing CURRENT_DATE with CURRENT_TIMESTAMP
• Expecting time part in CURRENT_DATE
• Thinking CURRENT_DATE returns last update time

2. Which of the following is the correct syntax to get the current date and time in SQL?

easy

A. SELECT CURRENT_DATE();

B. SELECT CURRENT_TIMESTAMP;

C. SELECT NOW();

D. SELECT CURRENT_TIME;

Solution

1. Step 1: Identify correct function syntax

   CURRENT_TIMESTAMP is a standard SQL function and does not require parentheses.

2. Step 2: Check other options

   CURRENT_DATE() is invalid syntax because CURRENT_DATE does not use parentheses; NOW() is valid in some databases but not standard SQL; CURRENT_TIME returns only time.

3. Final Answer:

   SELECT CURRENT_TIMESTAMP; -> Option B

4. Quick Check:

   CURRENT_TIMESTAMP syntax correct without parentheses [OK]

Hint: CURRENT_TIMESTAMP has no parentheses in standard SQL [OK]

Common Mistakes:

• Adding parentheses to CURRENT_DATE or CURRENT_TIMESTAMP
• Using NOW() assuming it's standard SQL
• Confusing CURRENT_TIME with date and time

3. Given the SQL query:

SELECT CURRENT_DATE, CURRENT_TIMESTAMP FROM dual;

What will be the output format of CURRENT_TIMESTAMP?

medium

A. Only the date in YYYY-MM-DD format

B. A Unix timestamp number

C. Only the time in HH:MM:SS format

D. Date and time in YYYY-MM-DD HH:MM:SS format

Solution

1. Step 1: Understand CURRENT_TIMESTAMP output

   CURRENT_TIMESTAMP returns both date and time, typically in the format YYYY-MM-DD HH:MM:SS.

2. Step 2: Compare with other options

   CURRENT_DATE returns only date; time-only or Unix timestamp are not returned by CURRENT_TIMESTAMP.

3. Final Answer:

   Date and time in YYYY-MM-DD HH:MM:SS format -> Option D

4. Quick Check:

   CURRENT_TIMESTAMP = date + time [OK]

Hint: CURRENT_TIMESTAMP always includes date and time [OK]

Common Mistakes:

• Expecting only date from CURRENT_TIMESTAMP
• Thinking CURRENT_TIMESTAMP returns Unix timestamp
• Confusing CURRENT_TIMESTAMP with CURRENT_TIME

4. You wrote this SQL query:

SELECT CURRENT_DATE(), CURRENT_TIMESTAMP() FROM users;

But it gives an error. What is the problem?

medium

A. CURRENT_DATE and CURRENT_TIMESTAMP should not have parentheses

B. The table name 'users' is missing quotes

C. CURRENT_DATE and CURRENT_TIMESTAMP are not valid SQL functions

D. You must use NOW() instead of CURRENT_TIMESTAMP

Solution

1. Step 1: Check function syntax

   In standard SQL, CURRENT_DATE and CURRENT_TIMESTAMP are special functions without parentheses.

2. Step 2: Validate other parts

   The table name 'users' does not require quotes; both functions are valid; NOW() is optional but not required.

3. Final Answer:

   CURRENT_DATE and CURRENT_TIMESTAMP should not have parentheses -> Option A

4. Quick Check:

   No parentheses with CURRENT_DATE/TIMESTAMP [OK]

Hint: Remove parentheses from CURRENT_DATE and CURRENT_TIMESTAMP [OK]

Common Mistakes:

• Adding parentheses to CURRENT_DATE or CURRENT_TIMESTAMP
• Thinking table names need quotes
• Replacing CURRENT_TIMESTAMP with NOW() unnecessarily

5. You want to insert a new record into a table events with a column created_at that stores the exact date and time of insertion. Which SQL statement correctly uses CURRENT_TIMESTAMP for this purpose?

hard

A. INSERT INTO events (name, created_at) VALUES ('Launch', CURRENT_DATE);

B. INSERT INTO events (name, created_at) VALUES ('Launch', CURRENT_TIME);

C. INSERT INTO events (name, created_at) VALUES ('Launch', CURRENT_TIMESTAMP);

D. INSERT INTO events (name, created_at) VALUES ('Launch', NOW());

Solution

1. Step 1: Identify correct timestamp function

   CURRENT_TIMESTAMP returns the full date and time, perfect for recording insertion time.

2. Step 2: Compare other options

   CURRENT_DATE lacks time; NOW() may work but is not standard SQL; CURRENT_TIME returns only time, missing date.

3. Final Answer:

   INSERT INTO events (name, created_at) VALUES ('Launch', CURRENT_TIMESTAMP); -> Option C

4. Quick Check:

   Use CURRENT_TIMESTAMP for full date and time [OK]

Hint: Use CURRENT_TIMESTAMP to store date and time on insert [OK]

Common Mistakes:

• Using CURRENT_DATE instead of CURRENT_TIMESTAMP
• Using CURRENT_TIME missing the date part
• Assuming NOW() is always standard SQL