SQLCMD gives conversion failed when converting date and/or time from character string

Question

The important part of this question being the use of Sqlcmd. Simply, I'm trying to figure out how to use date/time in my scripts and documentation is light on this.

I've tried several date formats, conversion and casting, etc.

Anyone have ideas?

-- I don't know how else to format this but as a string
:SETVAR CURRENT_DATE "2016-09-15T17:30:00"

PRINT CONVERT(DATETIME2,'${CURRENT_DATE}',106)

Gives the error: Conversion failed when converting date and/or time from character string.

EDIT:

I think it has something to do with sqlcmd and not the formatting.

This works:

PRINT CONVERT(DATETIME2,'2016-09-15T17:30:00', 106)

And this works:

PRINT CONVERT(DATETIME2,'2016-09-15T17:30:00', 126)

But using Setvar it does not work

:SETVAR CURRENT_DATE "2016-09-15T17:30:00"
PRINT CONVERT(DATETIME2,'${CURRENT_DATE}',126)

I also don't care what format the datetme is in, so I can change it if needed.

Answer 1

You're using the wrong style parameter in your convert function.
For ISO8601 you should use 126, not 106 as you're doing now.

This should work:

:SETVAR CURRENT_DATE "2016-09-15T17:30:00"

PRINT CONVERT(DATETIME2,'${CURRENT_DATE}',126)

Answer 2

Well I'm a fool. Wrong kind of brackets:

:SETVAR CURRENT_DATE "2016-09-15T17:30:00"
PRINT CONVERT(DATETIME2,'$(CURRENT_DATE)',126)