You fail to insert data in the Product table because the application tries to insert data in a different date format
Suppose you have data in a table in the format YYYY-MM-DD hh:mm: ss. You have a daily Sales report, and in that, you want data group by date. You want to have data in the report in format YYYY-MM-DD
We do face many such scenarios when we do not have a date format as per our requirement. We cannot change table properties to satisfy each requirement. In this case, we need to use the built-in functions in SQL Server to give the required date format.
Data Types for Date and Time
We have the following SQL convert date and Time data types in SQL Server.
Date type
Format
hh:mm:ss[.nnnnnnn]
YYYY-MM-DD
SmallDateTime
YYYY-MM-DD hh:mm:ss
DateTime
YYYY-MM-DD hh:mm:ss[.nnn]
DateTime2
YYYY-MM-DD hh:mm:ss[.nnnnnnn]
DateTimeOffset
YYYY-MM-DD hh:mm:ss[.nnnnnnn] [+|-]hh:mm
In SQL Server, we have used built-in functions such as SQL
GETDATE()
and
GetUTCDate()
to provide server date and format in various formats.
SYSDATETIME()
: To returns the server’s date and time
SYSDATETIMEOffset()
: It returns the server’s date and time, along with UTC offset
GETUTCDATE()
: It returns date and GMT (Greenwich Mean Time ) time
GETDATE()
: It returns server date and time
Execute the following queries to get output in respective formats.
SQL Convert Date Formats
As highlighted earlier, we might need to format a date in different formats as per our requirements. We can use the SQL
CONVERT()
function in SQL Server to format DateTime in various formats.
Syntax for the SQ:
CONVERT()
function is as follows.
Data_Type:
We need to define data type along with length. In the date function, we use Varchar(length) data types
Date
: We need to specify the date that we want to convert
DateFormatCode
: We need to specify
DateFormatCode
to convert a date in an appropriate form. We will explore more on this in the upcoming section
Let us explore various date formats using SQL convert date functions.
First, we declare a variable to hold current DateTime using the SQL
GETDATE()
function with the following query.
We can see various date formats in the following table. You can keep this table handy for reference purpose in the format of Date Time columns.
DATEADD
We can use the SQL
DATEADD
function to add a particular period to our date. Suppose we have a requirement to add 1 month to current date. We can use the SQL DATEADD function to do this task.
The syntax for SQL DATEADD function is as following
Interval:
We can specify an interval that needs to be added in the specified date. We can have values such as year, quarter, month, day, week, hour, minute etc.
Number:
It specifies the number of the interval to add. For example, if we have specified interval as Month and Number as 2, it means 2 months needs to be added in date.
In the following query, we want to add 2 months in the current date.
We can combine the SQL
DATEADD
and
CONVERT
functions to get output in desired DateTime formats. Suppose, in the previous example; we want a date format in of
MMM DD, YYYY.
We can use the format code 107 to get output in this format.
Execute the following code to get New date and ConvertedDate.
In this article, we explored various
SQL convert date
formats. It allows getting a date in required format with Covert function easily. You can use this article to take a reference for all date formats and use in your queries.
Hi! I am Rajendra Gupta, Database Specialist and Architect, helping organizations implement Microsoft SQL Server, Azure, Couchbase, AWS solutions fast and efficiently, fix related issues, and Performance Tuning with over 14 years of experience.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server
Always On Availability Groups
.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious
Best Author of the Year
continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Hi! I am Rajendra Gupta, Database Specialist and Architect, helping organizations implement Microsoft SQL Server, Azure, Couchbase, AWS solutions fast and efficiently, fix related issues, and Performance Tuning with over 14 years of experience.
I am the author of the book "
DP-300 Administering Relational Database on Microsoft Azure
". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server
Always On Availability Groups
.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious
Best Author of the Year
continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog:
https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at
[email protected]
View all posts by Rajendra Gupta
