Table of Contents
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
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:
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
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:
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:
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:
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:
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:
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:
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
Didn't find what you were looking for? Perform a quick search across GoLinuxCloud