How to return only Date from SQL Server DateTime?


Written By - Falguni Thakker
Advertisement

Overview of SQL DateTime datatype

SQL Server supports four date type datatypes DATE ,DATETIME ,SMALLDATETIME ,TIMESTAMP.  DATETIME datatype stores value in YYYY-MM-DD HH:MI:SS format. In some situation we need to extract only Date information from DateTime column value  to perform some date operations . There are four ways to retrieve date from SQL server DataTime datatype.

 

Different methods to get only date from DateTime datatype

  • Using CONVERT() method
  • Using CAST() method
  • Using combination DATEADD() and DATEDIFF()
  • Using TRY_CONVERT() method

 

Method-1: SQL get date using CONVERT() function

The CONVERT() function is used to converts a given value into a specified datatype.

 

Syntax of SQL CONVERT() function

CONVERT(data_type(length), expression, style)
CONVERT(Date , DateTime_column_name / DateTime_Expression);

Here,

  • data_type : It is a required argument, specified the datatype to convert expression or column value . Can be  int, numeric, money, float, real,date, datetime, smalldatetime, char, varchar, text, nchar, nvarchar, ntext, binary, varbinary, or image
  • (length) : It is an optional argument. The length of the resulting data type (for char, varchar, nchar, nvarchar, binary and varbinary)
  • expression : It is a required argument. The value to convert to another data type
  • style : It is an optional argument . It specifies the format used to convert between data types, such as a date or string format. Can be one of the following values:

Consider student table of result management database to perform practical examples

How to return only Date from SQL Server DateTime?

 

Example-1

Example 1: Write SQL query to retrieve date from current DateTime of system

SELECT  CONVERT(date, GETDATE(), 101) AS 'Todays date'
  • In the above query, SQLCONVERT function is applied with getdate() function which return current datetime of system.
  • The first date argument of CONVERT() function represent the datatype value to be retrieved

OUTPUT:

How to return only Date from SQL Server DateTime?

 

Example-2

Example 2: Write SQL query to retrieved date of admission from admissiondate column

SELECT  CONVERT(date, admissiondate, 101) AS 'Date of admission'
FROM   tblstudent;
  • In the above query, SQL CONVERT() function applied on admissiondate column values to extract date value from DateTime.
  • The first argument date is the datatype value to be retrieved
  • Second argument is the column name
  • Third argument is the style type code to retrieve date value in mm/dd/yyyy format

OUTPUT:

Advertisement

How to return only Date from SQL Server DateTime?

 

Method-2: SQL get date using CAST() function

SQL CAST() function is used to cast value from one data type to another data type. Using SQL CAST() function we can return only the Date from a SQL Server DateTime datatype by converting value into date datatype.

 

Syntax of SQL CAST() function

CAST(expression AS datatype(length))
CAST(DateTime_column_name/ Expression as date) 

Here,

  • column_name/expression : It is a required argument . We can give column name of DateTime datatype or expression value
  • datatype : It is a required argument . The datatype to convert expression or column value  to. it can be  int, bit, decimal, numeric, float, real, date, datetime, smalldatetime, char, varchar, text, nchar, nvarchar, ntext, binary, varbinary,
  • (length) : It is an optional argument . The length of the resulting data type (for char, varchar, nchar, nvarchar, binary and varbinary)

 

Example-1

Example 3: Write SQL query to retrieve date from current DateTime of system

SELECT  CAST(GETDATE() AS date) AS 'Todays Date'
  • In the above query , SQL CAST() function is applied with getdate() function to extract date from current system DateTime value
  • The value return by GETDATE() function we be converted to date datatype.

OUTPUT:

How to return only Date from SQL Server DateTime?

 

Example-2

Example 4: Write SQL query to retrieve date from admission DateTime  column value

SELECT  CAST(admissiondate AS date) AS 'Admisssion date'
FROM tblstudent;
  • In the above query, SQL CAST() function is applied in admissiondate column which is of DateTime datatype to retrieve date from date and time value.
  • SQL CAST() function applied with one argument of admissiondate column

OUTPUT:

How to return only Date from SQL Server DateTime?

 

Method-3: SQL get date using combination of DATEADD() and DATEDIFF() function

SQL DATEDIFF() function is used to calculate difference between two dates . the difference will be calculate based on interval value specified as the first argument in the function. To find the difference in the dates ‘dd’ interval value will be specified.

SQL DATEADD() function is used to add a time/date interval to a date and then returns the date.

Advertisement

 

Syntax of SQL DATEADD() and SQL DATEDIFF() function

DATEADD ( interval, number, date)
DATEDIFF ( interval , startdate , enddate )

To perform date difference and date add based on date interval we need to specify ‘dd’ as interval value

DATEADD (‘dd’,0, DATEDIFF(‘dd’,0,DateTime column_name or expression);

 

Example-1

Example 5: Write SQL query to retrieve date from current system datetime value

select  DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE())) as 'Todays date'
  • In the above SQL query, SQL GETDATE () function is used to retrieve system date and time.
  • SQL DATEDIFF() function is used as the nested inside the DATEADD() function so SQL DATEDIFF() function will execute first and return difference between current system date and with 0 , and return integer value of difference
  • SQL DATEADD () function will add integer difference return by SQL DATEDIFF () with 0 and return date value.

OUTPUT:

How to return only Date from SQL Server DateTime?

 

Example-2

Example 6: Write SQL query to extract admission date from admissiondate column value

select  DATEADD(dd, 0, DATEDIFF(dd, 0, admissiondate)) as 'Todays date' from tblstudent;
  • In the above SQL query, admissiondate column name of student table is specified as the input in SQL DATEDIFF() function .
  • SQL DATEDIFF() function is used as the nested inside the DATEADD() function so SQL DATEDIFF() function will execute first and return difference between current system date and with 0 , and return integer value of difference
  • SQL DATEADD () function will add integer difference return by SQL DATEDIFF () with 0 and return date value.

OUTPUT:

How to return only Date from SQL Server DateTime?

 

Method-4: SQL get date using TRY_CONVERT() function

SQL TRY_CONVERT() function is used to convert a value to the specified data type if the cast succeeds; otherwise, returns null.

Advertisement

 

Syntax of SQL TRY_CONVERT() function

TRY_CONVERT ( data_type [ ( length ) ], expression [, style ] )
TRY_CONVERT(Date , DateTime_column_name / DateTime_Expression);

Note that syntax and arguments of SQL CONVERT and TRY_CONVERT() function is same

 

Difference between SQL CONVERT () and TRY_CONVERT() function

The main difference between SQL CONVERT() and TRY_CONVERT() function is TRY_CONVERT() returns NULL if the cast of one datatype to another fails whereas CONVERT() raises an error.

 

Example-1

Example 7: Write SQL query to retrieve today’s date from current date and time of system

SELECT  TRY_CONVERT(date, GETDATE(), 101) AS 'Todays date '
  • In the above query, SQL TRY_CONVERT() function is applied with GETDATE() function to retrieve date from system data and time value.
  • first argument is datatype date in which value to be converted
  • Second argument is GETDATE() function which retuen system current date and time value.
  • Third argument is style code in which value to be returned dd/mm/yyyy.

OUTPUT:

How to return only Date from SQL Server DateTime?

 

Example-2

Example 6: Write SQL query to extract date from string value’22 Nov 2022’

SELECT   TRY_CONVERT(date, '22 Nov 1990') AS 'Get Date'
  • In the above, SQL TRY_CONVERT () function is applied on string to convert to the date value.
  • argument is datatype date in which value to be converted
  • Second argument is string value contain date information.
  • Third argument is style code in which value to be returned dd/mm/yyyy.

OUTPUT:

How to return only Date from SQL Server DateTime?

 

Summary

In this article of SQL get Date from DateTime datatype, we have discuss overview of SQL DATETIME datatype, list out methods to getdate from DATETIME data type value- using SQL CONVERT(), CAST() , combination of SQL DATEDIFF() and DATEADD() function and SQL TRY_CONVERT() function. Explain each method with syntax and practical examples.

 

References

SQL datatypes
SQL DATE function

 

Read More

DATEDIFF
DATEADD
CAST and CONVERT
TRY_CONVERT

 

Categories SQL

Didn't find what you were looking for? Perform a quick search across GoLinuxCloud

If my articles on GoLinuxCloud has helped you, kindly consider buying me a coffee as a token of appreciation.

Buy GoLinuxCloud a Coffee

For any other feedbacks or questions you can either use the comments section or contact me form.

Thank You for your support!!

Leave a Comment