DragonBorn DragonBorn - 5 months ago 15
SQL Question

Cannot convert datetime to string SQL

I want to convert date time to string. In my SQL query, I have a date "2016/06/15". With this date, I am subtracting it one month "2016/05/15".
I got this part working. However, when I am trying to convert it to a string (varchar or nvarchar), I encounter the following exception. Conversion failed when converting date time from character string. I am not sure how to fix this, help will be appreciated.

Here is my query

Declare @date as datetime
Set @date = GetDate()
print @date
Declare @dateMinusOneMonth as datetime
Set @dateMinusOneMonth = GetDate()
Set @dateMinusOneMonth = Convert(nvarchar, Convert(nvarchar(3), DateAdd(month, -1, @date)), 101)
print @dateMinusOneMonth

Answer

You are declaring @dateMinusOneMonth as datetime. Do it like this:

Declare @dateMinusOneMonth as nvarchar(20)
Set @dateMinusOneMonth = Convert(nvarchar, Convert(nvarchar(3), DateAdd(month, -1, @date)), 101)