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.
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:
-- http://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 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 next section as there are 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 (http://blog.sqlauthority.com)
Filed under: Database, Pinal Dave, PostADay, SQL, SQL Authority, SQL in Sixty Seconds, SQL Query, SQL Scripts, SQL Server, SQL Server Management Studio, SQL Tips and Tricks, T SQL, Technology, Video Tagged: Excel
