Query Shortcut for Displaying All DateTime Conversion Styles

[DaveInIowa]

There have been times when I’ve needed a specific format for a DateTime value but don’t have the different style values memorized. Instead of doing a search on the CONVERT function each time, I created this query shortcut and thought I’d share.

Go to (SSMS 2014) ...
[tt]Tools > Options… > Environment > Keyboard > Query Shortcuts[/tt]

Paste the following into one of the shortcuts (for example, Ctrl+7)
[tt]DECLARE @Now DATETIME2(7) = SYSDATETIME(); SELECT Style, CONVERT(VARCHAR, @Now, Style) [CONVERT(VARCHAR, @Now, Style)] FROM (VALUES (0), (100), (101), (102), (103), (104), (105), (106), (107), (108), (9), (109), (110), (111), (112), (13), (113), (114), (20), (120), (21), (121), (126), (127), (130), (131)) s(Style)[/tt]

Now, when you have a query window open, you can press the query shortcut and see the current date/time in all of the available styles in the Results window.

 

[SkipVought]

Ought to be in an FAQ.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]