February 25, 2010 at 12:42 pm
Ok, I have a dynamic SQL statement inside one of my SP's, and I'm trying to use a date function in that statement but I get the following error:
Msg 241, Level 16, State 1, Line 4
Conversion failed when converting datetime from character string.
Here are the functions generating the error:
DECLARE @sql NVARCHAR(MAX)
SELECT @sql = ISNULL(@sql, '') + 'USE ' + name + '
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET @startdate = ''select dateadd(mm,-3,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0))''
SET @enddate = ''select dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0))''
NOW if I run this with the actualy time stamp, it works fine!@ I dont get it.
DECLARE @sql NVARCHAR(MAX)
SELECT @sql = ISNULL(@sql, '') + 'USE ' + name + '
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET @startdate = ''2009-11-01 00:00:00.000''
SET @enddate = ''2010-01-31 23:59:59.997''
BTW @startdate and @enddate are DATETIME.
TIA!
Code
February 25, 2010 at 1:13 pm
If they are Datetime, you don't do it as a string.
Try this:
declare @startdate datetime
select @startdate = dateadd(mm,-3,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0))
select @startdate
February 25, 2010 at 1:17 pm
Hi Pam,
I had already tried that and received the same error.
Thanks,
Code
February 25, 2010 at 1:20 pm
Well, if you tried it as I wrote it you would receive no error, as that is tested code.
Do you see the differences between my statement and yours or NO?
You have set @startdate = "Select...
I have
Select @startdate = ...
Big difference
February 25, 2010 at 1:29 pm
Like I said, I tested these statements:
select @startdate = ''dateadd(mm,-3,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0))''
select @enddate = ''dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0))''
and they failed. Did you test your code within a dynamic statement? Below is the the complete script.
SET NOCOUNT ON
IF OBJECT_ID ('tempdb..#tempAPCFLCount')IS NOT NULL DROP TABLE #tempAPCFLCount
IF OBJECT_ID ('tempdb..#tempAPCPACount')IS NOT NULL DROP TABLE #tempAPCPACount
IF OBJECT_ID ('tempdb..#tempDRGNYCount')IS NOT NULL DROP TABLE #tempDRGNYCount
IF OBJECT_ID ('tempdb..#tempDRGFLCount')IS NOT NULL DROP TABLE #tempDRGFLCount
IF OBJECT_ID ('tempdb..#tempDRGPACount')IS NOT NULL DROP TABLE #tempDRGPACount
IF OBJECT_ID ('tempdb..#APCDRG')IS NOT NULL DROP TABLE #APCDRG
CREATE TABLE #APCDRG ([client] varchar(max),[Month] INT,[type] varchar, billIDNo INT, line_no int, override int, endnote int, overidden int)
CREATE TABLE #tempAPCFLCount ([client] varchar(max),[Month] INT, [type] varchar(max), billIDNo INT, line_no int, override int, endnote int)
CREATE TABLE #tempAPCPACount ([client] varchar(max),[Month] INT, [type] varchar(max), billIDNo INT, line_no int, override int, endnote int)
CREATE TABLE #tempDRGNYCount ([client] varchar(max),[Month] INT, [type] varchar(max), billIDNo INT, line_no int, override int, endnote int)
CREATE TABLE #tempDRGFLCount ([client] varchar(max),[Month] INT, [type] varchar(max), billIDNo INT, line_no int, override int, endnote int)
CREATE TABLE #tempDRGPACount ([client] varchar(max),[Month] INT, [type] varchar(max), billIDNo INT, line_no int, override int, endnote int)
DECLARE @sql NVARCHAR(MAX)
SELECT @sql = ISNULL(@sql, '') + 'USE ' + name + '
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
select @startdate = ''dateadd(mm,-3,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0))''
select @enddate = ''dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0))''
/*****************************/
/*APC-FL*/
INSERT INTO #APCDRG
SELECT SUBSTRING(db_Name(),CHARINDEX(''_'', db_Name())+ 1, LEN(db_name())) [Client],
MONTH(bh.createDate), '' '', bh.billIDNo, b.line_no, b.overridereason, '' '','' ''
FROM bill_hdr bh
JOIN bills b ON b.billIDNo = bh.billIDNo
WHERE bh.createDate BETWEEN @StartDate AND @EndDate
AND b.overrideReason IN (''309'', ''39'', ''90'', ''310'', ''88'') --or (b.over_ride = 0 and bse.endnote IN (''309'', ''39'', ''90'', ''310'', ''88'')))
GROUP BY MONTH(bh.createDate), bh.billIDNo, b.line_no, b.overridereason
UNION
SELECT SUBSTRING(db_Name(),CHARINDEX(''_'', db_Name())+ 1, LEN(db_name())) [Client],
MONTH(bh.createDate), '' '', bh.billIDNo, b.line_no, '' '', bse.endnote,b.over_ride
FROM bill_hdr bh
JOIN bills b ON b.billIDNo = bh.billIDNo
join bills_endnotes bse on bse.billidno = b.billidno and bse.line_no = b.line_no
WHERE bh.createDate BETWEEN @StartDate AND @EndDate
AND (b.over_ride = 0 and bse.endnote IN (''309'', ''39'', ''90'', ''310'', ''88''))
GROUP BY MONTH(bh.createDate), bh.billIDNo, b.line_no, bse.endnote, b.over_ride
/******* INSERT THE DB NAMES BELOW THAT YOU WANT THE SCRIPT TO CYCLE THRU ********/
' + CHAR(10)
FROM sys.databases
WHERE name IN (
'PR0D1_AAAMI',
'PR0D1_Aequicap',
'PR0D1_Affirmative',
'PR0D1_AFG',
'PR0D1_CalCasualty',
'PR0D1_DirectG',
'PR0D1_Elco',
'PR0D1_FBFS',
'PR0D1_NFU',
'PR0D1_Occidental',
'PR0D1_OneBeacon',
'PR0D1_Palisades',',
'PR0D1_Safeco',
'PR0D1_Safeway',
'PR0D1_Sentry',
'PR0D1_StateFarm',
'PR0D1_THI',
'PR0D1_UUG'
)
DECLARE @startdate DATETIME, @enddate DATETIME
EXECUTE sp_executesql
@sql,
N'@startdate DATETIME, @enddate DATETIME',
@startdate = @startdate,
@enddate = @enddate
February 25, 2010 at 1:37 pm
Note NO QUOTES on my version as well. You don't need them, can't have them. Quotes are ONLY for char/varchar data types.
Also, not sure what you're wanting to do with this part at the end:
DECLARE @startdate DATETIME, @enddate DATETIME
EXECUTE sp_executesql
@sql,
N'@startdate DATETIME, @enddate DATETIME',
@startdate = @startdate,
@enddate = @enddate
The declare needs to happen at the beginning before you start using the variables.
Is it simply misplaced?
February 25, 2010 at 2:23 pm
Since that statement loops though multiple databases the declaration goes at the end so start and end dates are set when it runs against each new DB. Also, iit's a dynamic statement if I dont use double quotes it breaks. I've been using this script for a year and it's worked perfectly until I used a date function to set the dates. If you look at my first post where I included an example with the literal dates, they have double quotes and it works fine.
February 25, 2010 at 2:43 pm
DECLARE @startdate DATETIME, @enddate DATETIME
DECLARE @sql NVARCHAR(MAX)
SELECT @sql = ISNULL(@sql, '') + 'USE ' + 'name' + '
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
select @startdate = dateadd(mm,-3,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0))
select @enddate = dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0))
'
PRINT @sql
Result:
USE name
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
select @startdate = dateadd(mm,-3,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0))
select @enddate = dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0))
Note that column [name] was converted to a literal demonstration purposes only.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
February 25, 2010 at 2:47 pm
SQL Server does an implicit conversion for dates when it can, so a recognizable date string such as '12/21/2010' will get converted. BUT, that is NOT what you have with your new version. What you have is a SQL statement.
There are too many other errors with your code for me to go through fixing them for you (missing, improperly placed quotes being the primary one).
Guessing that you want the @startdate, @enddate assignments within the @sql string, JUST that part of the code would look something like this:
DECLARE @sql NVARCHAR(MAX)
SELECT @sql = ISNULL(@sql, '') + 'USE ' + name + '
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
select @startdate = dateadd(mm,-3,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0))
select @enddate = dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0))
/*****************************/
though I'm not quite sure what you're getting at with name here. Did you mean DB_NAME() perhaps?
February 25, 2010 at 2:54 pm
@pam:
sys.databases.[name]
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
February 25, 2010 at 2:59 pm
The reason the version you were using before worked is because SQL was doing a string-to-datetime conversion implicitly behind the scenes. It can't do that if the string can't be implicitly converted directly to a datetime value. "select ..." can't be implicitly converted.
If you take out the double-quotes, your dynamic SQL should work, but you'll need to declare them inside the string.
What doesn't make sense is that you have set up the start and end dates as input parameters for the dynamic SQL statement, but then you define the values within the string instead of supplying values from the calling procedure. They have no external purpose, which means they should not be parameters. The better way to handle this would be to declare them as internal variables within the dynamic string, assign them values inside it, and don't use parameters for this at all.
I've modified your script to work that way. See if it does what you need:
SET NOCOUNT ON
IF OBJECT_ID ('tempdb..#tempAPCFLCount')IS NOT NULL DROP TABLE #tempAPCFLCount
IF OBJECT_ID ('tempdb..#tempAPCPACount')IS NOT NULL DROP TABLE #tempAPCPACount
IF OBJECT_ID ('tempdb..#tempDRGNYCount')IS NOT NULL DROP TABLE #tempDRGNYCount
IF OBJECT_ID ('tempdb..#tempDRGFLCount')IS NOT NULL DROP TABLE #tempDRGFLCount
IF OBJECT_ID ('tempdb..#tempDRGPACount')IS NOT NULL DROP TABLE #tempDRGPACount
IF OBJECT_ID ('tempdb..#APCDRG')IS NOT NULL DROP TABLE #APCDRG
CREATE TABLE #APCDRG ([client] varchar(max),[Month] INT,[type] varchar, billIDNo INT, line_no int, override int, endnote int, overidden int)
CREATE TABLE #tempAPCFLCount ([client] varchar(max),[Month] INT, [type] varchar(max), billIDNo INT, line_no int, override int, endnote int)
CREATE TABLE #tempAPCPACount ([client] varchar(max),[Month] INT, [type] varchar(max), billIDNo INT, line_no int, override int, endnote int)
CREATE TABLE #tempDRGNYCount ([client] varchar(max),[Month] INT, [type] varchar(max), billIDNo INT, line_no int, override int, endnote int)
CREATE TABLE #tempDRGFLCount ([client] varchar(max),[Month] INT, [type] varchar(max), billIDNo INT, line_no int, override int, endnote int)
CREATE TABLE #tempDRGPACount ([client] varchar(max),[Month] INT, [type] varchar(max), billIDNo INT, line_no int, override int, endnote int)
DECLARE @sql NVARCHAR(MAX)
SELECT @sql = ISNULL(@sql, '') + 'USE ' + name + '
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
declare @startdate datetime, @enddate datetime
select @startdate = dateadd(mm,-3,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)),
@enddate = dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0))
/*****************************/
/*APC-FL*/
INSERT INTO #APCDRG
SELECT SUBSTRING(db_Name(),CHARINDEX(''_'', db_Name())+ 1, LEN(db_name())) [Client],
MONTH(bh.createDate), '' '', bh.billIDNo, b.line_no, b.overridereason, '' '','' ''
FROM bill_hdr bh
JOIN bills b ON b.billIDNo = bh.billIDNo
WHERE bh.createDate BETWEEN @StartDate AND @EndDate
AND b.overrideReason IN (''309'', ''39'', ''90'', ''310'', ''88'') --or (b.over_ride = 0 and bse.endnote IN (''309'', ''39'', ''90'', ''310'', ''88'')))
GROUP BY MONTH(bh.createDate), bh.billIDNo, b.line_no, b.overridereason
UNION
SELECT SUBSTRING(db_Name(),CHARINDEX(''_'', db_Name())+ 1, LEN(db_name())) [Client],
MONTH(bh.createDate), '' '', bh.billIDNo, b.line_no, '' '', bse.endnote,b.over_ride
FROM bill_hdr bh
JOIN bills b ON b.billIDNo = bh.billIDNo
join bills_endnotes bse on bse.billidno = b.billidno and bse.line_no = b.line_no
WHERE bh.createDate BETWEEN @StartDate AND @EndDate
AND (b.over_ride = 0 and bse.endnote IN (''309'', ''39'', ''90'', ''310'', ''88''))
GROUP BY MONTH(bh.createDate), bh.billIDNo, b.line_no, bse.endnote, b.over_ride
/******* INSERT THE DB NAMES BELOW THAT YOU WANT THE SCRIPT TO CYCLE THRU ********/
' + CHAR(10)
FROM sys.databases
WHERE name IN (
'PR0D1_AAAMI',
'PR0D1_Aequicap',
'PR0D1_Affirmative',
'PR0D1_AFG',
'PR0D1_CalCasualty',
'PR0D1_DirectG',
'PR0D1_Elco',
'PR0D1_FBFS',
'PR0D1_NFU',
'PR0D1_Occidental',
'PR0D1_OneBeacon',
'PR0D1_Palisades',
'PR0D1_Safeco',
'PR0D1_Safeway',
'PR0D1_Sentry',
'PR0D1_StateFarm',
'PR0D1_THI',
'PR0D1_UUG'
)
EXECUTE sp_executesql
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 25, 2010 at 3:05 pm
On another note, you shouldn't be subtracting 3 milliseconds from a date to define the end of a datetime range. The right way to do it is to use:
date>=@StartDate
and date < @EndDate
There are several reasons for this, but the main one is that it will work even if Microsoft changes the definition of the datetime data type (which is always possible), or if someone changes to a different data type when refactoring the script.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 1, 2010 at 4:06 pm
Thanks for the help Gsquared. The problem with the script you posted is that once you execute and it cycles to the next database on the list you receive the following error if the declaration is inside the string:
Msg 134, Level 15, State 1, Line 44
The variable name '@startdate' has already been declared. Variable names must be unique within a query batch or stored procedure.
A few months back I worked on this script pretty extensively with people on this site to get it working as needed, and unfortunately the method you posted is where I originally started.
March 2, 2010 at 5:51 am
I'd have to see the script it's generating to tell you what to do to fix that. Run it with Print instead of Execute, and post the script here, and I can tell you what to fix.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 2, 2010 at 6:13 am
Code-1029433 (3/1/2010)
Thanks for the help Gsquared. The problem with the script you posted is that once you execute and it cycles to the next database on the list you receive the following error if the declaration is inside the string:Msg 134, Level 15, State 1, Line 44
The variable name '@startdate' has already been declared. Variable names must be unique within a query batch or stored procedure.
You need to move the declaration of the DATETIME variables to before the first 'USE <db>' statement, and outside of the main SELECT something like this:
DECLARE @sql NVARCHAR(MAX)
SELECT @sql = 'DECLARE @startdate DATETIME,
@enddate DATETIME' + CHAR(10)
SELECT @sql = @sql + 'USE ' + name + '
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
...
... rest of script omiited for clarity
...
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply