Galhano //127.0.0.1

Below is a list of formats and an example of the output.  The date used for all of these examples is “2006-12-30 00:38:54.840”.

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 @date to GETDATE() or any other date you want to use.  This will output just the valid formats.

DECLARE @counter INT = 0
DECLARE @date DATETIME = '2006-12-30 00:38:54.840'

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

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

SELECT * FROM #dateFormats

Next Steps

• The formats listed above are not inclusive of all formats provided. Experiment with the different format numbers to see what others are available.
• These formats can be used for all date/time functions, as well as data being served to clients, so experiment with these data format conversions to see if they can provide data more efficiently.
• Also, check out the SQL Server FORMAT Function to Format Dates.

Source link :

https://www.mssqltips.com/sqlservertip/1145/date-and-time-conversions-using-sql-server/