SQL datetime functions example

As a sql developer, we often write query based on various date time parameter, in this tutorial you will SQL DateTime functions, There are some built-in methods to get different date and time format in SQL.

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.

Sql date data type

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')

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

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;

Simply run the above query in your sql query manager to see the result.