A very common requirement of developers is to format datetime to their specific need. Every geographic location has different need of the date formats. Some countries follow the standard of mm/dd/yy and some countries as dd/mm/yy. The need of developer changes as geographic location changes. In SQL Server there are various functions to aid this requirement. There is function CAST, which developers have been using for a long time as well function CONVERT which is a more enhanced version of CAST. In the latest version of SQL Server 2012 a new function FORMAT is introduced as well to display datetime in specific format.
In this SQL in Sixty Seconds video we cover two different methods to display the datetimeĀ in specific format. 1) CONVERT function and 2) FORMAT function.
Let me know what you think of this video. Here is the script which is used in the video:
-- https://blog.sqlauthority.com/ -- SQL Server 2000/2005/2008/2012 onwards -- Datetime SELECT CONVERT(VARCHAR(30),GETDATE()) AS DateConvert; SELECT CONVERT(VARCHAR(30),GETDATE(),10) AS DateConvert; SELECT CONVERT(VARCHAR(30),GETDATE(),110) AS DateConvert; SELECT CONVERT(VARCHAR(30),GETDATE(),5) AS DateConvert; SELECT CONVERT(VARCHAR(30),GETDATE(),105) AS DateConvert; SELECT CONVERT(VARCHAR(30),GETDATE(),113) AS DateConvert; SELECT CONVERT(VARCHAR(30),GETDATE(),114) AS DateConvert; GO -- SQL Server 2012 onwards -- Various format of Datetime SELECT CONVERT(VARCHAR(30),GETDATE(),113) AS DateConvert; SELECT FORMAT ( GETDATE(), 'dd mon yyyy HH:m:ss:mmm', 'en-US') AS DateConvert; SELECT CONVERT(VARCHAR(30),GETDATE(),114) AS DateConvert; SELECT FORMAT ( GETDATE(), 'HH:m:ss:mmm', 'en-US') AS DateConvert; GO -- Specific usage of Format function SELECT FORMAT(GETDATE(), N'"Current Time is "dddd MMMM dd, yyyy', 'en-US') AS CurrentTimeString;
This video discusses CONVERT and FORMAT in a simple manner, but the subject is much deeper and there are lots of information to cover along with it. I strongly suggest that you go over related blog posts in the next section as there are a wealth of knowledge discussed there.
Related Tips in SQL in Sixty Seconds:
- Get Date and Time From Current DateTime ā SQL in Sixty Seconds #025
- Retrieve ā Select Only Date Part From DateTime ā Best Practice
- Get Time in Hour:Minute Format from a Datetime ā Get Date Part Only from Datetime
- DATE and TIME in SQL Server 2008
- Function to Round Up Time to Nearest Minutes Interval
- Get Date Time in Any Format ā UDF ā User Defined Functions
- Retrieve ā Select Only Date Part From DateTime ā Best Practice ā Part 2
- Difference Between DATETIME and DATETIME2
- Saturday Fun Puzzle with SQL Server DATETIME2 and CAST
What would you like to see in the next SQL in Sixty Seconds video?
Reference:Ā Pinal Dave (https://blog.sqlauthority.com)
12 Comments. Leave new
Thank you – nice explanation
I’m afraid in order to show millisecond we should use fff rather than mmm
SELECT FORMAT ( GETDATE(), ‘dd mon yyyy HH:m:ss:mmm’, ‘en-US’ ) AS DateConvert;
Typo error.
Hi,
(SELECT CONVERT(VARCHAR(30),it.StartDate) )[start] will return date as Jun 17 2013 2:00PM and ( SELECT CONVERT(VARCHAR(30),it.EndDate,101))[end] will return date as 06/17/2013 but i need date format like 06/17/2013 2:00 PM. Can you please help me in this?
Hi,
( SELECT CONVERT(VARCHAR(30),it.EndDate,101))[end] will return date as 06/17/2013 and (SELECT CONVERT(VARCHAR(30),it.StartDate) )[start] will return date as Jun 17 2013 2:00PM and i need date in the following format 06/17/2013 2:00 PM(“/” is important) . Can you please help me on this
got the answer.
SELECT (CONVERT(VARCHAR(30),it.EndDate,101) +’ ‘ +
CONVERT(VARCHAR(30),it.EndDate,8)) AS end
Hi pinal
i want to have a date range list in a drop down for reports like as follows
Today
Yesterday
Before Two Days
Current Week
Last Week
Current Month
Last Month
Last 3 Months
Current Year
Last Year
First Quater
Second Quater
Third Quater
Fourth Quater
Hi Pinal
ITS VERY URGENT
I want to convert ’25-02-14′ (dd-mm-yy) format to ‘2014-02-25’ (yyyy-mm-dd).
I’m trying of many methods but I could not able to get through.
Waiting for your reply.
Thanks & Regards
Madhu.R
My table has filed with data type datetime…. when i update it it gives conversion error .. i update it via procedure.. i pass parameter to procedure.. and update table.. pls give solution.
SELECT CONVERT(DATE,STUFF(’25-02-14′,7,0,’20’), 105) AS [DATEFORMAT]
My table contains nvarchar(50) field which contains data like “11:30 AM “, i want to convert it to time
I have an SQL related Problem in My Stored Proc Where Do I Ask by attaching a Notepad Data?