SQL Date Format, SQL Date Functions Examples, Date difference in SQL, different date format in MS SQL

SQL datetime functions example

In this tutorial you will SQL DateTime functions, There are some built-in methods to get different date and time format in SQL.

Sql date data type

When you develop any application with MS SQL database, you come across different date format, sometimes before inserting into SQL table you need to make sure that the date format is SQL compatible, if not then you need to change it to a format that SQL accept, otherwise the query will produce error.

Even while reading data from SQL you may need to produce date in different format for report or date comparison. Here you will learn how to work with different Date and Time Format in SQL.

In MS SQL server we have following date and time data types (with default values).

  • sql date
    2020-04-26
    
  • sql datetime
    2020-04-26 18:25:08.733
    
  • sql datetime2(7)
    2020-04-26 18:25:08.7330000
    
  • sql datetimeoffset(7)
    2020-04-26 18:25:08.7330000 +00:00
    
  • time
    18:25:08.7330000
    
  • sql smalldatetime
    2020-04-26 18:25:00
    
  • sql timestamp
    0x00000000000007D2
    

Now to understand how sql date format will look like, let us create a table with all above format and insert some data to see the format. so we know how to use Date in SQL query.

CREATE TABLE [dbo].[tbDateExample](
	[id] [int] NOT NULL,
	[d1] [date] NULL,
	[d2] [datetime] NULL,
	[d3] [datetime2](7) NULL,
	[d4] [datetimeoffset](7) NULL,
	[d5] [time](7) NULL,
	[d6] [smalldatetime] NULL,
	[d7] [timestamp] NULL
) ON [PRIMARY]
GO

Now insert a single row to see how the data in each column will look like, notice in every field i have inserted the current date time using getdate function.

INSERT INTO [dbo].[tbDateExample]
           ([id] ,[d1] ,[d2] ,[d3] ,[d4], [d5],[d6])
     VALUES
           (1, getdate() ,getdate(),getdate()
		    ,getdate(), getdate(),getdate())
GO


Select d1 from [tbDateExample]
Select d2 from [tbDateExample]
Select d3 from [tbDateExample]
Select d4 from [tbDateExample]
Select d5 from [tbDateExample]
Select d6 from [tbDateExample]

Now, in above insert query if you notice, I have not inserted any value in timestamp field, still some values has been inserted (though the field was allowing null value), the field is not empty. timestamp is used as a mechanism for version-stamping table rows, learn more about SQL timestamp datatype.

In SQL query analyzer, you can see the current timestamp by using CURRENT_TIMESTAMP like following query

Select CURRENT_TIMESTAMP

To get the current date in sql database, use getdate() function. this will get the date and time of the system.

Select getdate()

// result : 2020-04-26 07:46:12.700

To insert date in SQL table, use the format MM/DD/YYYY like example below.

INSERT INTO [dbo].[tbDateExample]
           ([id] ,[d1] )
     VALUES (1, '01/20/2020')

INSERT INTO [dbo].[tbDateExample]
           ([id] ,[d1] )
     VALUES (2, '01-20-2020')

Sql date format

Default date format in SQL is MM/DD/YYYY. We also can use hyphen as separator like MM-DD-YYYY. another often used format YYYY/MM/DD

Date difference function in sql

MS SQL has built in function called DATEDIFF to find the difference between two dates in sql, the difference between two dates can be negative also.

We can get the difference value in year, month, day, hour etc. let’s look at the examples below.

--difference in year
SELECT DATEDIFF(YEAR, '08-25-2010', getdate() ) AS DateDiffYear;

--difference in day
SELECT DATEDIFF(DAY, '08-25-2010', getdate() ) AS DateDiffDay;

--difference in month
SELECT DATEDIFF(MONTH, '08-25-2010', getdate() ) AS DateDiffMonth;

--difference in hour
SELECT DATEDIFF(HOUR, '08-25-2010', getdate() ) AS DateDiffHour;

--difference in minute
SELECT DATEDIFF(MINUTE, '08-25-2010', getdate() ) AS DateDiffMinute;

--difference in second
SELECT DATEDIFF(SECOND, '08-25-2010', getdate() ) AS DateDiffSecond;
 
Hire Remote SQL Developer
Joins in SQL Database

Course in Demand
SQL Joins Example
MS SQL Examples | Join MS SQL Course