Cannot convert datetime to string SQL

Question

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

I think converting isn't Sql Server task. Convert values in your C# code — Fabio
– Fabio, Commented Jun 15, 2016 at 6:07
Shows how much Fabio knows. CONVERT( {Interval}, {expression}, {style} ) is a function in SQL SERVER.link — clifton_h
– clifton_h, Commented Jun 15, 2016 at 6:22

diiN__________ · Accepted Answer · 2016-06-15 06:07:09Z

3

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)

answered Jun 15, 2016 at 6:07

diiN__________

7,7197 gold badges49 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

clifton_h Over a year ago

Yeah, @DragonBorn, hopefully you noted the length of the string (20). That can mess up your code if you do not take care.

Mairaj Ahmad · Accepted Answer · 2016-06-15 06:07:05Z

2

@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)

answered Jun 15, 2016 at 6:07

Mairaj Ahmad

14.6k2 gold badges29 silver badges43 bronze badges

Comments

clifton_h · Accepted Answer · 2016-06-15 06:20:54Z

1

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.

answered Jun 15, 2016 at 6:20

clifton_h

1,2988 silver badges12 bronze badges

Comments

Matt · Accepted Answer · 2016-06-15 21:23:56Z

0

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')

answered Jun 15, 2016 at 21:23

Matt

14.4k2 gold badges19 silver badges29 bronze badges

Collectives™ on Stack Overflow

Cannot convert datetime to string SQL

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related