CODEWITHSUNDEEP
c#sqlsql-serverdatetime
RedRocket
RedRocket

Reputation: 1733

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

Upvotes: 0

Views: 1519

Answers (4)

Matt
Matt

Reputation: 14341

You can do it all in one line easily. also I don't like having to look up 101, 102,113, etc for date string formats with convert. seeing you mention c# if you are on SQL 2012 or newer just use FORMAT like you would in c#.

PRINT FORMAT(DATEADD(MONTH,-1,GETDATE()), 'yyyy/MM/dd')

Upvotes: 0

clifton_h
clifton_h

Reputation: 1298

Lets first focus on transforming it to VARCHAR/NVARCHAR. Just pass the variable like this:

DECLARE @Time DATE
SET @Time = CAST(DATEADD(MM, -1, GETDATE() ) AS DATE ) 
SELECT CAST(@TIME AS VARCHAR(10) )

See how easy that was?

Result: 2016-05-14

Please note that your DB may be set up differently, and you should test or format the dates correctly. Lastly, if you use TRY_CONVERT(), the answer will return a NULL instead of failing the entire code.

Upvotes: 1

diiN__________
diiN__________

Reputation: 7656

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)

Upvotes: 3

Mairaj Ahmad
Mairaj Ahmad

Reputation: 14604

@dateMinusOneMonth is of type datetime and you are trying to assign it varchar value which is not valid.

You can convert it in print like below

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

Upvotes: 2

Related Questions