2

I have a question concerning the code snippet functionality in MS SQL Server Management Studio. I want to create a snippet that will contain the current date and time in some predefined place of code at the moment when the snippet is added. Not when the whole script will be executed, hence GETDATE() won't satisfy my needs. Is it even possible to perform such task?

Konrad
  • 1,014
  • 1
  • 13
  • 24
  • Can you elaborate more? What do you mean by "when the snippet is added"? Who will be adding a snippet where? – murtazat Feb 03 '17 at 05:37
  • 1
    No, that's not possible, unless you write an extension for SSMS. SSMS supports templates, but you always need to fill in the values explicitly. There's no built-in replace values. – Jeroen Mostert Feb 03 '17 at 10:15
  • Thank you very much for information @JeroenMostert – Konrad Feb 03 '17 at 10:22

1 Answers1

3

This is a limitation in SQL server snippets. If you are rich and buy the redgate sql prompt or toolbelt you can use their more advanced snippets which can insert the date and lots of other things dynamically. ( The tools are great but expensive ) Redgate Sql Prompt Free Trial

For the poor among us I have found a workaround by creating a batch file in my snippets folder and running it everyday on a scheduled task just after midnight. It looks at each snippet file in the same folder and replaces lines starting with "-- Date: XXX... " with todays date ie. -- Date: 15 Jul 2019.

This means my custom snippet files always has todays date written in them.

Batch File: updatetoday.bat

:: my date environment is YYYY/MM/DD it might not be the same for you depending on your region settings
:: the script finds lines in all snippet files in the same folder as the batch file
:: starting with "-- Date:" and replaces the whole line with "-- Date: DD MMM YYYY" of the current date
:: use a scheduled task to run it each day just after midnight and it will set the date each day!

@echo off 
setlocal EnableExtensions DisableDelayedExpansion
set month=JanFebMarAprMayJunJulAugSepOctNovDec
set /a y="%date:~0,4%"
set /a m=("%date:~5,2%"-1) * 3
set /a d="%date:~8,2%"
set mn=!month:~%m%,3!
set "r=^-^- Date: %d% %mn% %y%"
for %%j in (".\*.snippet") do (
    for /f "delims=" %%i in ('type "%%~j" ^& break ^> "%%~j"') do (
        set "line=%%i"      
        setlocal EnableDelayedExpansion     
        if "!line:~0,8!" == "-- Date:" set "line=%r%"       
        >>"%%~j" echo !line!
        endlocal
    )
)
endlocal

Note: In the scheduled task you must set the 'Start in' property to your snippet folder. Don't use quotes even if there is spaces in the path.

hamish inglis
  • 41
  • 6