Format SQL Server Dates with FORMAT Function (2024)

Thanks
Greg

/*
Formatting dates and times

ctr dateinfo descr
100 Nov 17 2021 10:23AM convert(varchar(50),getdate(),100)
101 11/17/2021 convert(varchar(50),getdate(),101)
102 2021.11.17 convert(varchar(50),getdate(),102)
103 17/11/2021 convert(varchar(50),getdate(),103)
104 17.11.2021 convert(varchar(50),getdate(),104)
105 17-11-2021 convert(varchar(50),getdate(),105)
106 17 Nov 2021 convert(varchar(50),getdate(),106)
107 Nov 17, 2021 convert(varchar(50),getdate(),107)
108 10:23:43 convert(varchar(50),getdate(),108)
109 Nov 17 2021 10:23:43:110AM convert(varchar(50),getdate(),109)
110 11-17-2021 convert(varchar(50),getdate(),110)
111 2021/11/17 convert(varchar(50),getdate(),111)
112 20211117 convert(varchar(50),getdate(),112)
113 17 Nov 2021 10:23:43:110 convert(varchar(50),getdate(),113)
114 10:23:43:110 convert(varchar(50),getdate(),114)
120 2021-11-17 10:23:43 convert(varchar(50),getdate(),120)
121 2021-11-17 10:23:43.110 convert(varchar(50),getdate(),121)
126 2021-11-17T10:23:43.110 convert(varchar(50),getdate(),126)
130 12 ???? ?????? 1443 10:23:43:110AM convert(varchar(50),getdate(),130)
131 12/04/1443 10:23:43:110AM convert(varchar(50),getdate(),131)
0 20211117102343 replace(replace(replace(convert(varchar(50),getdate(),120),' ',''),'-',''),':','')
1 1023 replace(convert(varchar(5),getdate(),108),':','')

*/
declare @ctr int
select @ctr = 100
create table #temp (ctr int, dateinfo varchar(50), descr varchar(100))

while @ctr < 115
begin
insert into #temp
select @ctr as 'Counter', convert(varchar(50),getdate(),@ctr), 'convert(varchar(50),getdate(),'+convert(varchar(3),@ctr)+')'
select @ctr = @ctr + 1
end
select @ctr = 120
while @ctr < 122
begin
insert into #temp
select @ctr as 'Counter', convert(varchar(50),getdate(),@ctr), 'convert(varchar(50),getdate(),'+convert(varchar(3),@ctr)+')'
select @ctr = @ctr + 1
end
select @ctr = 126
insert into #temp
select @ctr as 'Counter', convert(varchar(50),getdate(),@ctr), 'convert(varchar(50),getdate(),'+convert(varchar(3),@ctr)+')'
select @ctr = 130
insert into #temp
select 130 as 'Counter', convert(varchar(50),getdate(),130), 'convert(varchar(50),getdate(),'+convert(varchar(3),@ctr)+')'
select @ctr = 131
insert into #temp
select 131 as 'Counter', convert(varchar(50),getdate(),131), 'convert(varchar(50),getdate(),'+convert(varchar(3),@ctr)+')'
select @ctr = 120
insert into #temp
select 0, replace(replace(replace(convert(varchar(50),getdate(),120),' ',''),'-',''),':',''),'replace(replace(replace(convert(varchar(50),getdate(),120),'' '',''''),''-'',''''),'':'','''')'
select @ctr = 108
insert into #temp
select 1 as 'Counter', replace(convert(varchar(5),getdate(),108),':',''), 'replace(convert(varchar(5),getdate(),108),'':'','''')'

select * from #temp

drop table #temp

I might be missing it but, if I am, others will miss it, as well.

You said back in December of 2018 that you would add a warning (and it should be a significant warning) about how comparatively terrible FORMAT is for just about everything even compared to relative complex CONVERT and SUBSTRING (etc) functionality.

Some of the Developers that work with me have referenced this very article and I'm constantly having to prove to people that FORMAT should generally be avoided.

Can you add the performance warning in nice bold letters at the beginning of this article, please? Thank you.

Assuming getdate() returns "2018-03-21 11:36:14.840" as in your example.

For sorting dates later, placing the date in Year, Month, and Day order works much better. Such as:

SELECT FORMAT (getdate(), 'yyyy/MM/dd') as date

Output

Hi guys,

I am just trying to update the output to the spreadsheet of the date from the current standard format to format # 6.

See code snippit

Can anyone assist please.

declare @date datetime

declare @monday datetime

select @date = GETDATE()

SET DATEFIRST 2

select @monday = convert(date, DATEADD(day, 7 - DATEPART(dw, @Date), @Date), 105)

select patients.firstname as 'Name', replace(patients.mobile, ' ', '') as 'Mobile', 'Hi ' + patients.firstname + ' your upcoming appointment at Dental1 Craigieburn is on ' + cast(app_date as varchar(10)) + ' at ' + min(cast([start] as varchar(5))) + '. Reply "Y" if this is still suitable or call 9021 8928 to reschedule. Please note you may receive another message if we do not receive a reply as to your attendance.' as 'Confirmation'

from a_appointments

join "patients"

on patients.patient_id = a_appointments.pat_id

join "app_books"

on a_appointments.app_book_id = app_book_number

where app_date >= @monday and app_date < @monday + 7 and LEN(patients.mobile) > 9 and app_book_number != 5 and a_appointments.status like '%G%' and a_appointments.status not like '%K%'

group by appoint_id, app_date, app_book_id, patients.firstname, app_books.app_book_description, patients.mobile

order by app_date, min(cast([start] as varchar(5)));

output to Craigieburn_NextweekconfirmList.csv quote '' with column names

format ascii

Hi.

For input to an automated appointment reminder voice message, how would I convert the date from 12/5/2019 to Dec 05th, as in "you have an appointment on Dec 05th" (or 3rd or 13th, etc)?

Hi

Ihave queryin SQL:SELECT FORMAT (getdate(), 'dd-MMM-yy') as date

I expected the result as date when export it to excel via function export excel on asp.net but the result returns it as text ('31-Dec-18) instead of date (31-12-18). I mean that , it should remove comma (') in beginning of result

Could you help me how to export it as date with format dd-mmm-yy (31-Dec-18) in excel file

Many thanks

Xuan

Hi Doug, thanks for pointing out the error in the code. The code above has been updated.

-Greg

In the 4 bulleted items at the top of the article, #2 states: To get DD-MM-YYYY use SELECT FORMAT (getdate(), 'dd/MM/yyyy ') as date. This is incorrect and would yield DD/MM/YYYY. Also, it should possibly be mentioned that FORMAT is a CLR function and, as such, is considerably slower than using CONVERT.

Ultimately, dates SHOULD be formatted by the front end (e.g. SSRS, Excel, .Net application, etc.) but if you are using it in a query and you've got thousands of rows with multiple dates/row to format, you are going to see a performance degradation.

Thanks clarkvera.

We will update the tip based on your suggestion.

It would be MUCH more useful if you used a date with a day that would obviously not be confused with a month. Like the March 21 instead of March 7.

I am new to SQL Server and want to create good habits for future business solutions. I have an Access Database front end with a new SQL Server backend. My customer wants to have same functionality to change an assignment time in their form by just typing "9a" and pressing the [Tab] key to get the converted result of 9:00 am (i.e., a Medium Date field in MS Access). After moving the table data into SQL Server, it resulted in a datetime() format i.e., 1899-12-30 07:00:00.000 I have modified the column to be time(0) time(7) but MS Access still doesn't allow the conversion of their entry to medium date. I have many forms and VBA Code toadjust if I can't find a simple solution. In the meantime, I have created unbound text boxes which allow them to to type "9a" and convert the entry to a time value. However, if the end-user types an inaccurate value, it will error. So Icreated another process control to simplyprovide the correct values to choose from (i.e., 9:00 am, 9:15 am, etc.) after their unbound combo box selection it will update the table with a correct time format. This is over the top on my part, but I don't have any other solution. Any suggestions?

You missed a universal date format. Sometimes the date must evaluated in a SELECT statement regardless of geographic location.