Problem with datetime to text string conversion. 1

[Chuck712]

I have a stored proceedure that runs a report that automatically fills in the date variable. I planned to have a change when the end of the year came around, and now the report won't work. I can't see the problem. Perhaps someone can give this code a quick look over and see what the problem is. I am getting an error Syntax error converting datetime from character string. Here is the code, and thanks.

DECLARE @start_date datetime
DECLARE @finish_date datetime
DECLARE @last_month datetime
SET @last_month = DATEADD(mm,-1,GetDate())
SET @start_date = CONVERT(datetime,STR(MONTH(@last_month))+'/01/'+
STR(YEAR(@last_month)),101)
IF MONTH(@last_month)<12
SET @finish_date = DATEADD(day,-1,CONVERT(datetime,STR(MONTH(@last_month)+1,2)
+'/01/'+STR(YEAR(@last_month)),101))
ELSE
SET @finish_date=DATEADD(day,-1,convert(datetime,'01/01/'+STR(YEAR(@last_month)+1)+'23:59:00',101))

 

[Gixonita]

Hi

On your last line:

ELSE
SET @finish_date=DATEADD(day,-1,convert(datetime,'01/01/'+STR(YEAR(@last_month)+1)+'23:59:00',101))

Add a space before the time:

From: '23:59:00'
To: ' 23:59:00'

At the moment its concatenating the year and time together without spaces.That will solve the problem

Hope this helps

 

[johnv20]

you need to specifiy the length of the year string

DECLARE @finish_date datetime
DECLARE @last_month datetime
SET @last_month = DATEADD(mm,-1,GetDate())
SET @start_date = CONVERT(datetime,STR(MONTH(@last_month),2)+'/01/'+STR(YEAR(@last_month)),101)

IF MONTH(@last_month)<12
     SET @finish_date = DATEADD(day,-1,CONVERT(datetime,STR(MONTH(@last_month)+1,4)+'/01/'+STR(YEAR(@last_month)),101))

ELSE SET @finish_date=DATEADD(day,-1,convert(datetime,'01/01/'+STR(YEAR(@last_month)+1,4)+' 23:59:00',101))

 

[Chuck712]

Thank you all so much. Adding the space solved the problem and I don't think I would have found such a small thing without the help. I guess you really can't party and then do this stuff.