SQL SELECT DATE

SQL SELECT DATE is used to retrieve a date from a database. If you want to find a particular date from a database, you can use this statement.

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.


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

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() Extract the date part of a date/or date time expression
EXTRACT() Returns a single part of a date/time
DATE_ADD() Add a specific time interval from 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 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:MI:SS
  • TIMESTAMP - format: YYYY-MM-DD HH:MI: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:MI:SS
  • SMALLDATETIME - format: YYYY-MM-DD HH:MI:SS
  • TIMESTAMP - format: a unique number
Note: The date types are chosen for a column when you create a new table in your database!

					

For example: let's see the query to get all the records after '2013-12-12'.

  1. SELECT * FROM
  2. table-name WHERE your date-column >= '2013-12-12'

Let's see the another query to get all the records after '2013-12-12' and before '2013-12-13' date.

  1. SELECT* FROM
  2. table-name where your date-column < '2013-12-13' and your date-column >= '2013-12-12'

If you want to compare the dates within the query, you should use BETWEEN operator to compare the dates.

  1. SELECT * FROM
  2. table_name WHERE yourdate BETWEEN ?2012-12-12? and ?2013-12-12?

Or if you are looking for one date in particular you can use. You should change the date parameter into the acceptable form.

  1. SELECT* FROM
  2. table_name WHERE cast (datediff (day, 0, yourdate) as datetime) = ?2012-12-12?