SQL Date Functions

As long as your data contains only the date portion, your queries will work as expected. However, if a time portion is involved, it gets complicated.

Before talking about the complications of querying for dates, we will look at the most important built-in functions for working with dates.

MySQL Date Functions

The following table lists the most important built-in date functions in MySQL:

Function Description
NOW() Returns the current date and time
CURDATE() Returns the current date
CURTIME() Returns the current time
DATE() Extracts the date part of a date or date/time expression
EXTRACT() Returns a single part of a date/time
DATE_ADD() Adds a specified time interval to a date
DATE_SUB() Subtracts a specified time interval from a date
DATEDIFF() Returns the number of days between two dates
DATE_FORMAT() Displays date/time data in different formats

 

SQL Server Date Functions

The following table lists the most important built-in date functions in SQL Server:

Function Description
GETDATE() Returns the current date and time
DATEPART() Returns a single part of a date/time
DATEADD() Adds or subtracts a specified time interval from a date
DATEDIFF() Returns the time between two dates
CONVERT() Displays date/time data in different formats

 

SQL Date Data Types

MySQL comes with the following data types for storing a date or a date/time value in the database:

  • DATE – format YYYY-MM-DD
  • DATETIME – format: YYYY-MM-DD HH:MM:SS
  • TIMESTAMP – format: YYYY-MM-DD HH:MM:SS
  • YEAR – format YYYY or YY

SQL Server comes with the following data types for storing a date or a date/time value in the database:

  • DATE – format YYYY-MM-DD
  • DATETIME – format: YYYY-MM-DD HH:MM:SS
  • SMALLDATETIME – format: YYYY-MM-DD HH:MM:SS
  • TIMESTAMP – format: a unique number

Note: The date types are chosen for a column when you create a new table in your database!

No comments yet.

Leave a Reply

*