Search code examples
t-sqlsqlcmdinvoke-sqlcmd

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

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.

Solution

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

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