| 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  |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
Getting conversion error