Click here to Skip to main content
Click here to Skip to main content

Find all SQL Server DateTime Formats

By , 25 Mar 2010
Rate this:
Please Sign up or sign in to vote.
When using DateTime with SQL Server, I often forget what the parameters are that allow formatting DateTime in specific ways. When that happens, I fire up this code fragment in SQL Server Management Studio and it neatly lists out every possible parameter with example output.
 
declare @Loop int
set @Loop = -1
 
declare @table table
(
    [Date] nvarchar(50) not null,
    [Param] int not null
)
 
while @Loop <= 150
begin
    set @Loop = @Loop + 1
 
    begin try
        insert into @table
        select convert(nvarchar, getdate(), @Loop), @Loop
    end try
 
    begin catch
        continue
    end catch
end
 
select * from @table
 
Sample output:
 
Date                                               Param
-------------------------------------------------- -----------
Mar 24 2010  8:22AM                                0
03/24/10                                           1
10.03.24                                           2
24/03/10                                           3
24.03.10                                           4
24-03-10                                           5
24 Mar 10                                          6
Mar 24, 10                                         7

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

mark merrens

United States United States

I am me: what else is there?

Comments and Discussions

 
GeneralSimply press F1 and write date formats.. Pinmemberran.sharabi23-Mar-10 22:50 
GeneralRe: Simply press F1 and write date formats.. Pinmemberdigital man24-Mar-10 0:49 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web04 | 2.8.140415.2 | Last Updated 26 Mar 2010
Article Copyright 2010 by mark merrens
Everything else Copyright © CodeProject, 1999-2014
Terms of Use
Layout: fixed | fluid