Getting conversion error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Getting conversion error

Author

Topic

dond75
Starting Member

4 Posts

Posted - 2014-08-05 : 10:45:42

Hi
I'm trying to create two dates as variables. The first part works fine but when I use the variables in a later query I get error:

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

If I don't have the query in that sql variable then it works fine but I have to for later use in reporting services. Any suggestions?

DECLARE @reportdate DATETIME
set @reportdate = GetDate()

DECLARE @startoffiscalyear DATETIME
set @startoffiscalyear = iif(month(@reportdate) < 4, DATEFROMPARTS(year(@reportdate) - 1, '04', '01'), DATEFROMPARTS(year(@reportdate), '04', '01'))

DECLARE @endoffiscalyear DATETIME
set @endoffiscalyear = iif(month(@reportdate) >= 4, DATEFROMPARTS(year(@reportdate) + 1, '03', '31'), DATEFROMPARTS(year(@reportdate), '03', '31'))

select @startoffiscalyear as [startoffiscalyear], @endoffiscalyear as [endoffiscalyear]

DECLARE @SQL nvarchar(max)
SET @SQL =
'
SELECT
nor_volume AS tonnage,
"count" = 1
from FilteredOpportunity as FA
WHERE
(
createdon < '+ @startoffiscalyear +'
AND statecode = 0
)
OR
(
createdon < '+ @startoffiscalyear +'
AND statecode <> 0
AND actualclosedate BETWEEN '+ @startoffiscalyear +' AND '+ @endoffiscalyear +'
)'
EXEC(@SQL)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-08-05 : 12:23:13

You need to add CONVERT/CAST for the two datetime variables inside the @SQL as you are concatenating into a string/nvarchar.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

dond75
Starting Member

4 Posts

Posted - 2014-08-05 : 15:33:06

The input date has the format 2014-04-01 00:00:00.000

and so also has the createdon.

If I do like this:
createdon < convert(datetime,'+ @startoffiscalyear +',126)

is still get the same error.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-08-05 : 15:35:55

What I'm saying is that the variables need to have the convert in order for them to be concatenated. Do this for all datetime variables in @SQL:

cast(@startoffiscalyear as varchar(25))

This is so that it can be concatenated with the rest of the string.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

dond75
Starting Member

4 Posts

Posted - 2014-08-05 : 16:13:37

DECLARE @SQL nvarchar(max)
SET @SQL =
'
SELECT
nor_volume AS tonnage,
"count" = 1
from FilteredOpportunity
WHERE
(
createdon < cast(' + @startoffiscalyear + ' as varchar(25))
AND statecode = 0
)'

but still getting the same error

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-08-05 : 16:18:18

You changed what I said to do. Do this:

SET @SQL =
'
SELECT
nor_volume AS tonnage,
"count" = 1
from FilteredOpportunity
WHERE
(
createdon < ''' + cast(@startoffiscalyear as varchar(25)) + '''
AND statecode = 0
)'

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-08-05 : 16:18:52

And add this until you get the syntax right:

print @sql

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

dond75
Starting Member

4 Posts

Posted - 2014-08-05 : 16:27:41

ahhh now I see what I was doing wrong here. Thank you