Click here to Skip to main content
65,938 articles
CodeProject is changing. Read more.
Articles / database / SQL-Server

Find all SQL Server DateTime Formats

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...
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.

SQL
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:

MSIL
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)