Conversion failed when converting date and/or time from character string

Transact-SQL
1

Hi experts. Maybe because it's Monday, but I can't resolve the error below.
Thanks for any tips.

Declare @LastDateReceived datetime;
Declare @Body1 varchar(255);
Set @LastDateReceived = (Select MAX(RowCreatedDate) from ........)
Set @Body1 = 'We last received files on ' + @LastDateReceived <<<<<--- error here

Error: Conversion failed when converting date and/or time from character string.

2

Is the RowCreatedDate column declared as datetime?

3

@robert_volk Yes it is datetime.
And in this case the value is Dec 1 2022 6:00AM

Thanks

4

Probably just change it to:

Set @Body1 = CONCAT('We last received files on ', @LastDateReceived)

That should handle the conversion.

5

Good idea, but unfortunately, this is an old instance - 2005 is the compatibility level

'CONCAT' is not a recognized built-in function name.

I've done this before on old instances but I can't remember how I did it.

6

If the instance is a later version, like 2012, you can do the following:

USE tempdb;
Declare @LastDateReceived datetime;
Declare @Body1 varchar(255);
Set @LastDateReceived = (Select MAX(RowCreatedDate) from myDb.dbo.myTable)
Set @Body1 = CONCAT('We last received files on ',@LastDateReceived)

Basically you use 3-part names for all of the databases in the old compatibility mode.

1 Like
7

Declare @LastDateReceived datetime;
Declare @Body1 varchar(255);
Set @LastDateReceived = (Select MAX(RowCreatedDate) from ........)
Set @Body1 = 'We last received files on ' + CONVERT(varchar(30), @LastDateReceived, 120) --change the CONVERT code to whatever date format you want

1 Like
8

Thanks @robert_volk and @ScottPletcher.
as this old instance will be retired in a year or so... I'll run the job from a newer instance to read from the table via linked server. I've got that in place now and it's working well.