SQL SERVER Date Conversion Format Codes

Standard

Ever wonder what the format for all of the date conversion codes is? Where you convert, for instance GETDATE() into a VARCHAR(10). Like if you wanted to display GETDATE() as 20150610 how do you go about it?

Well, here’s some code to find out which code you need to use:

DECLARE @DateFormat TABLE
(
[DateFormat] VARCHAR(50),
FormatNumberUsed INT
)

DECLARE @Type INT = 1;

WHILE @Type < 200
BEGIN

  BEGIN TRY

  INSERT INTO @DateFormat SELECT CONVERT(VARCHAR(50),GETDATE(),@Type), @Type

  END TRY
  BEGIN CATCH

  INSERT INTO @DateFormat SELECT 'NOT VALID', @Type

  END CATCH

  SET @Type = @Type + 1;

END

SELECT TOP 100 *
FROM @DateFormat
WHERE [DateFormat] <> 'NOT VALID'

Obviously you can change the WHERE clause if you want to see all of the invalid codes.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s