How to convert DateTime using SQL Server

Tutorialsrack 20/02/2019 SQL

SQL Server gives various alternatives you can use to format a date/time string. One of the primary need is to get an actual date/time. The most common way to get the current date/time using GETDATE(). GETDATE() provides the current date and time according to the server providing the date and time. If you needed a universal date/time, then GETUTCDATE() should be used. To change the format of the date, you need to convert the requested date to a string and specify the format number corresponding to the format needed.

How to get different SQL Server date formats

1. Use the date format option along with CONVERT function.
2. Syntax :-  Convert(varchar, getdate(), format-number), For Example,  To get YYYY-MM-DD use SELECT CONVERT(varchar, getdate(), 23)
3.  Check out the chart to get a list of all format options

Date Only Formats:-

TIME ONLY FORMATS

DATE & TIME FORMATS

FORMATS WITH ISSUES

You can also format the date or time without dividing characters, as well as concatenate the date and time string:

If you want to get a list of all valid date and time formats, you could use the code below and change the @Datetime to GETDATE() or any other date you want to use.  This will output just the valid formats.

DECLARE @counter INT = 0
DECLARE @Datetime DATETIME = '2018-09-23 00:38:54.840'

CREATE TABLE #DateTimeFormats (dateFormatOption int, dateOutput varchar(40))

WHILE (@counter <= 150 )
BEGIN
   BEGIN TRY
      INSERT INTO #DateTimeFormats
      SELECT CONVERT(varchar,@counter),CONVERT(varchar,@Datetime , @counter) 
      SET @counter = @counter + 1
   END TRY
   BEGIN CATCH;
      SET @counter = @counter + 1
      IF @counter >= 150
      BEGIN
         BREAK
      END
   END CATCH
END

SELECT * FROM #DateTimeFormats

Related Posts