September 16, 2008 at 2:05 am
I have the following dynamic sql that adds a second to a timestamp and then adds quotes with the quotename function:
'INSERT INTO ' + N'' + QUOTENAME(TABLE_SCHEMA) +
N'.' + QUOTENAME(TABLE_NAME) +
N' SELECT T.*,' + QUOTENAME(@STAMP,'''') ........................
@stamp is defined as:
CONVERT(VARCHAR(23),DATEADD(SS,@global_time,CAST(Floor(CAST(Getdate() AS FLOAT)) AS DATETIME)),120)
The returned value is:
'Sep 16 2008 12:00AM'
instead of:
'2008-09-16 00:00:01'
So ,I basically lose the second again.
Any thoughts?
September 16, 2008 at 2:24 am
If you print out the dynamic SQL before you exec it, what do you see?
I tested a section of your code out
DECLARE @Stamp varchar(25), @global_time int
SET @global_time = 25
set @stamp = CONVERT(VARCHAR(23),DATEADD(SS,@global_time,CAST(Floor(CAST(Getdate() AS FLOAT)) AS DATETIME)),120)
print N' SELECT T.*,' + QUOTENAME(@STAMP,'''')
and it seems to return fine
SELECT T.*,'2008-09-16 00:00:25'
Where are you seeing the 'Sep 16 2008 12:00AM' value? When selecting from the table later?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 16, 2008 at 2:47 am
No,
When I take the entire SQL string and run it, then the result is:
INSERT INTO [dbo].[H_DATUM_KEY] SELECT T.*,'Sep 16 2008 12:00AM' FROM [dbo].[V_H_I_DATUM_KEY] T
When I take your example the result is:
SELECT T.*,'2008-09-16 00:00:25'
which is what I want.
I am baffled..
September 16, 2008 at 2:49 am
Sorry,
Must have been the lack of coffee.
I had @stamp defined as TIMESTAMP, if I define it as VARCHAR(23) then all is as should be..
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply