SQL DATE TIME Statement

Introduction

 
In this chapter, we will learn how to use  SQL Date and Time Data with various options.  
 

SQL DATE statement

 
When working with SQL Date the most difficult part  is to be sure that the format of the date you are trying to insert, matches the format of the date column in the database in SQL Server
 
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 more complicated in SQL. 
 
The SQL Date defines a date that is combined with a time of day with fractional seconds that is based on a 24-hour clock
 
Note
 
Use the time date datetime2 and datetimeoffset data types for new work. These types align with the SQL standard
 
They are more portable times;  datetime2 and  datetimeoffset provide more seconds; precisiondatetimeoffset provides time zone support for globally deployed applications
 

SQL Date data types

 
SQL Server - 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
The date types are chosen for a column when you create a new table in your database 
 

Working with Dates in SQL

 
You can compare two dates easily if there is no time component involved
 
We have the following OrderDetails  table 
 
 
Syntax 
  1. Select * from OrderDetails     
Example
 
 
Now we want to select the records with an OrderDate of 2019-04-11 from the table above
 
We use the following Select statement
 
Syntax 
  1. SELECT FROM OrderDetails  WHERE OrderDate='2019-04-11'         
Example 
 
  

Converting date and time data in SQL

 
Converting date and time data is when you convert to date and time data types, SQL Server rejects all values.
 
It doesn't recognize as dates or times for information about using the cast and convert functions with date and time data, see the cast and convert in SQL.
 

Converting date to other date and time types

 
This section describes what occurs when you convert a date data type to other date and time data types.
 
When the conversion is to time(n), the conversion fails, and error message 206 is raised and there is an operand type clash: date is incompatible with time. 
 
If the conversion is to datetime, the date is copied. The following code shows the results of converting a date value to a datetime value
 
Syntax 
  1. DECLARE @date date = '12-21-16';        
  2. DECLARE @datetime datetime = @date;        
  3. SELECT @datetime AS '@datetime', @date AS '@date';   
Example
 
 
The datetime is defined when the conversion is from time(n), the time component is copied, and the date component is set to '1900-01-01'.
 
When the fractional precision of the time(n) value is greater than three digits, the value will be truncated to fit.
 
The following example shows the results of converting a time(4)value to a datetime value.
 
Syntax 
  1. DECLARE @time time(4) = '12:10:05.1237';        
  2. DECLARE @datetime datetime = @time;        
  3. SELECT @datetime AS '@datetime', @time AS '@time';   
Example
 
 
The SQL date time conversion is from smalldatetime the hours and minutes are copied. The seconds and fractional seconds are set to 0.
 
The following code shows the results of converting a smalldatetime value. 
 
Syntax
  1. DECLARE @smalldatetime smalldatetime = '12-01-19 12:32';        
  2. DECLARE @datetime datetime = @smalldatetime;        
  3. SELECT @datetime AS '@datetime', @smalldatetime AS '@smalldatetime';    
Example  
 
 
The SQL date-time is defined as when the conversion is from datetimeoffset(n)the date and time components are copied.
 
The time zone is truncated. When the fractional precision of the datetimeoffset(n)value is greater than three digits, the value will be truncated.
 
The following example shows the results of converting a datetimeoffset(4) value to a datetime value.
 
Syntax  
  1. DECLARE @datetimeoffset datetimeoffset(4) = '1968-10-23 12:45:37.1234 +10:0';    
  2. DECLARE @datetime datetime = @datetimeoffset;    
  3.     
  4. SELECT @datetime AS '@datetime', @datetimeoffset AS '@datetimeoffset';    
Example
 
 
The Datetime is defined as when the conversion is from datetime2(n), the date and time are copied. when the fractional precision of the datetime2(n) value is greater than three digits, the value will be truncated.
 
The Datetime is defined as when the conversion is from datetime2(n), the date and time are copied.
 
When the fractional precision of the datetime2(n) value is greater than three digits, the value will be truncated. 
 
The following example shows the results of converting a datetime2(4)value to a datetimevalue.
 
The following example shows datetime and datetime2 in SQL server 
 
Syntax
  1. DECLARE @datetime2 datetime2(4) = '1968-10-23 12:45:37.1237';    
  2. DECLARE @datetime datetime = @datetime2;    
  3. SELECT @datetime AS '@datetime', @datetime2 AS '@datetime2'; 
Example 
 
 
The following example compares the results of casting a string to each date and data type. 
 
Syntax
  1. SELECT         
  2.      CAST'2019-05-18 12:35:29. 1234567 +12:15' AS  "keyword">time(7)) AS 'time'         
  3.     ,CAST'2019-05-18 12:35:29. 1234567 +12:15' AS date AS 'date'         
  4.     ,CAST('2019-05-18 12:35:29.123' >AS smalldatetime) AS         
  5.         'smalldatetime'         
  6.     ,CAST'2019-05-18 12:35:29.123' AS datetime) >AS 'datetime'         
  7.     ,CAST('2019-05-18 12:35:29. 1234567 +12:15'  >AS datetime2(7)) AS         
  8.         'datetime2'        
  9.     ,CAST'2019-05-18 12:35:29.1234567 +12:15' AS datetimeoffset(7)) AS         
  10.         'datetimeoffset';    
Example
 
 

Summary

 
In this chapter, we learned how to use  SQL Aggregate statements with various options.
Author
Naresh Beniwal
Naresh Beniwal
Tech Writter 7.1k 1.6m