Azure SQL Server Date and Time DataType Conversions and Functions

 This document will explain the types of Date and Time  Data Type in Sql Server and related funtions.

In SQL Server Their are six Date and Time Data Types.

1- Time

2- Date

3-SmallDateTime

4-DateTime

5-DateTime2

6-DateTimeOffSet

Time 

The fractional second scale specifies the number of digits for the fractional part of the seconds. The fractional second scale ranges from 0 to 7. By default, the fractional second scale is 7 if you don’t explicitly specify it.

Format-        hh:mm:ss[.nnnnnnn]

Range-        nano seconds upto 100

Query- To create a table  with Time Column

CREATE TABLE dizsweb(
    dizswebid int

    Name varchar(50)    

    start_at TIME(0),

);

 

 The following INSERT statement adds a row to the table

INSERT INTO dizsweb (
    dizswebid ,
    Name ,
    start_at 
)
VALUES
    (   '1'
        'John Doe',
        '09:30:00'
    );

 

 

Date
To store the date data in the database, you use the SQL Server DATE data type

Format-        YYYY-MM-DD

Range-        1 Day

Query to Create a Table with Date Data Type

Create Table Dizsweb (DizswebID int, Name varchar(50), DOB Date)

Insert the data into table

Insert into Dizsweb values( 1,'SQL Azure','1994-07-28')

 

 

CREATE TABLE dizsweb (
    product_id INT NOT NULL,
    valid_from DATE NOT NULL,
    valid_to DATE NOT NULL,
    amount DEC (10, 2) NOT NULL,
    PRIMARY KEY (
        product_id,
        valid_from,
        valid_to
    ),
    FOREIGN KEY (product_id) 
    REFERENCES production.products (product_id)
);

 

INSERT INTO dizsweb (
    product_id,
    valid_from,
    valid_to,
    amount
)
VALUES
    (
        1,
        '2019-01-01',
        '2019-12-31',
        400
    );

 

SmallDateTime

Format-        YYYY-MM-DD hh:mm:ss

Range-        1 Minute

CREATE TABLE [dbo].[delivers](

  [productid] [tinyint] NOT NULL,

  [date] [nvarchar](100) NULL,

CONSTRAINT [PK_delivers] PRIMARY KEY CLUSTERED

(

  [productid] ASC

)WITH (PAD_INDEX = OFF, 

 STATISTICS_NORECOMPUTE = OFF,

  IGNORE_DUP_KEY = OFF,  

ALLOW_ROW_LOCKS = ON, 

 ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

INSERT [dbo].[delivers] ([productid], [date]) VALUES (1, N'02-03-2005')

INSERT [dbo].[delivers] ([productid], [date]) VALUES (2, N'03-05-2006')

INSERT [dbo].[delivers] ([productid], [date]) VALUES (3, N'04-05-2011')

 

DateTime

Format-        YYYY-MM-DD hh:mm:ss[.nnn]

Range-        0.33 Seconds

 

CREATE TABLE Customer
(First_Name char(50),
Last_Name char(50),
Address char(50),
City char(50),
Country char(25),
Birth_Date datetime);

 

CREATE TABLE Customer
(First_Name char(50),
Last_Name char(50),
Address char(50),
City char(50),
Country char(25) default 'United States',
Birth_Date datetime); 

DateTime2

Format-        YYYY-MM-DD hh:mm:ss[.nnnnnnn]

Range-        100 nano Seconds

    DECLARE @nowDateTime 

    DATETIME = GETDATE(),

    DateTime2 DATETIME2(3)= SYSDATETIME() 

    SELECT DATALENGTH(DateTime) 'DateTime Storage Size', 

    DATALENGTH(@nowDateTime2) 'DateTime2(3) Storage Size'

DateTimeOffSet

Format-        YYYY-MM-DD hh:mm:ss[.nnnnnnn]

Range-        100 nano seconds and as par the time Zone

 

 

CREATE TABLE dizsweb (

    dizsweb DATETIMEOFFSET(7)

);

INSERT INTO dizsweb (dizsweb)
VALUES(
       CAST('2019-02-28 01:45:00.0000000 -08:00' AS DATETIMEOFFSET));