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.
In MS SQL server we have following date and time data types (with default values).
2020-04-26 18:25:08.7330000 +00:00
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
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
To get the current date in sql database, use
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
We also can use hyphen as separator like
MM-DD-YYYY. another often used format
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.