未加星标

Format SQL Server Dates with FORMAT Function

字体大小 | |
[数据库(mssql) 所属分类 数据库(mssql) | 发布者 店小二04 | 时间 2018 | 作者 红领巾 ] 0人收藏点击收藏

By:Daniel Calbimonte | Last Updated: 2018-12-06 || Related Tips:More > Dates

Problem

SQL Server 2008 and earlier versions used the CONVERT functions to handle date formatting. In this tip, Date and Time Conversions Using SQL Server , we have a list of the available examples on how to use the CONVERT function to handle different date formats.

As you may know, the CONVERT function is not very flexible and we have limited date formats. In SQL Server 2012 and later, a new function FORMAT has been introduced which is much easier to use to format dates. This article shows different examples of using this new function to format dates.

Solution

Starting with SQL Server 2012, a new function to handle formatting dates was introduced which is similar to Oracle's to_date function. Many Oracle DBAs complained about the SQL Server CONVERT function and its poor flexibility and now we have a new way to format dates in SQL Server.

With the SQL Server FORMAT function we do not need to know theformat number to use to get the right date format we want, we can just specify the display format we want and we get that format.

How to format SQL Server dates with FORMAT function Use the FORMAT function to format the date and time To get DD-MM-YYYY use SELECT FORMAT (getdate(), 'dd/MM/yyyy ') as date To get MM-DD-YY use SELECT FORMAT (getdate(), 'MM-dd-yy') as date Check out more examples below

The syntax of the SQL Server FORMAT function is the following:

FORMAT (value,format[,culture])
GO SQL Server FORMAT Examples for Formatting Dates

Let's start with an example:

SELECT FORMAT (getdate(), 'dd-MM-yy') as date
GO

The format will be as follows:

dd - day number from 01-31 MM - month number from 01-12 yy - two digit year number

If this was run for March 21, 2018 the output would be: 21-03-18 .

Let's try another one:

SELECT FORMAT (getdate(), 'hh:mm:ss') as time
GO

The format will be as follows:

hh - hour of day from 01-12 mm - minutes of hour from 00-59 ss - seconds of minute from 00-59

The output will be: 02:48:42 .

SQL Server FORMAT with Culture

Another option for the FORMAT function is culture . With the culture option you can obtain regional formatting. Here is a list of culture codes to use with FORMAT .

For example in the USA, the format would be like:

SELECT FORMAT (getdate(), 'd', 'en-us') as date
GO

In the USA the format is month, day, year.

If this was run for March 21, 2018 the output would be: 3/21/2018

Another example where we will use the Spanish culture in Bolivia (es-bo):

SELECT FORMAT (getdate(), 'd', 'es-bo') as date
GO

In Bolivia the format is day, month, year.

If this was run for March 21, 2018 the output would be: 21/03/2018.

SQL Server FORMAT output examples

Below is a list of formats and an example of the output. The date used for all of these examples is "2018-03-21 11:36:14.840".

Query Sample output SELECT FORMAT (getdate(), 'dd/MM/yyyy ') as date 21/03/2018 SELECT FORMAT (getdate(), 'dd/MM/yyyy, hh:mm:ss ') as date 21/03/2018, 11:36:14 SELECT FORMAT (getdate(), 'dddd, MMMM, yyyy') as date Wednesday, March, 2018 SELECT FORMAT (getdate(), 'MMM dd yyyy') as date Mar 21 2018 SELECT FORMAT (getdate(), 'MM.dd.yy') as date 03.21.18 SELECT FORMAT (getdate(), 'MM-dd-yy') as date 03-21-18 SELECT FORMAT (getdate(), 'hh:mm:ss tt') as date 11:36:14 AM SELECT FORMAT (getdate(), 'd','us') as date 03/21/2018

As you can see, we used a lot of options for the date and time formatting, which are listed below.

dd - this is day of month from 01-31 dddd - this is the day spelled out MM - this is the month number from 01-12 MMM - month name abbreviated MMMM - this is the month spelled out yy - this is the year with two digits yyyy - this is the year with four digits hh - this is the hour from 01-12 HH - this is the hour from 00-23 mm - this is the minute from 00-59 ss - this is the second from 00-59 tt - this shows either AM or PM d - this is day of month from 1-31 (if this is used on its own it will display the entire date) us - this shows the date using the US culture which is MM/DD/YYYY

For all the different custom date and time format strings to use with the SQL Server FORMAT command, check out this list.

Next Steps You can now work with a more flexible and intuitive function to handle date formats. Here is more information on the FORMAT function

Last Updated: 2018-12-06


Format SQL Server Dates with FORMAT Function
Format SQL Server Dates with FORMAT Function
About the author
Format SQL Server Dates with FORMAT Function
Daniel Calbimonte is a Microsoft SQL Server MVP, Microsoft Certified Trainer and Microsoft Certified IT Professional. View all my tips

Related Resources

More Database Developer Tips...

本文数据库(mssql)相关术语:熊片数据库 mssql数据库 oracle数据库 pubmed数据库 access数据库 万方数据库

代码区博客精选文章
分页:12
转载请注明
本文标题:Format SQL Server Dates with FORMAT Function
本站链接:https://www.codesec.net/view/621150.html


1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责;
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性,不作出任何保证或承若;
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
技术大类 技术大类 | 数据库(mssql) | 评论(0) | 阅读(77)