Convert current_timestamp problem

[snufse1]

I have following code:

declare @DateTime varchar(50)
set @DateTime = convert(varchar(50), current_timestamp, 121)

exec('insert into ZyTax.dbo.ttable(
report_state, report_datetime
select ztadds, ' + @DateTime +
' from AS400SRV_MSDASQL.VGSYS400.' + @AliasLibrary + '.' + @SourceFile)


Now it gives me error on @DateTime in the "select". If I use code 108 it gives error as well. It works fine for code 112. I need to capture both date and time. I guess code 108 and 121 have some invalid characters like ":" whereas code 112 does not.

 

[SQLBill]

One way to test is try this:

declare @DateTime varchar(50)
set @DateTime = convert(varchar(50), current_timestamp, 121)declare @mysql VARCHAR(400)

SET @mysql = 'insert into ZyTax.dbo.ttable(
report_state, report_datetime
select ztadds, ' + @DateTime +
' from AS400SRV_MSDASQL.VGSYS400.' + @AliasLibrary + '.' + @SourceFile

PRINT @mysql

That will let you double-check that the syntax is valid.

-SQLBill



The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[gmmastros]

Try [!]126[/!]

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[snufse1]

Using code 121 the value of select string is:

insert into ZyTax.dbo.ttable( report_state, report_datetime
select ztadds, 2009-03-04 07:50:06.947 from AS400SRV_MSDASQL.VGSYS400.VGITFRLIB.F55125


Using code 126 the value of the select string is:

insert into ZyTax.dbo.ttable(
report_state, report_datetime
select ztadds, 2009-03-04T07:52:09.070 from AS400SRV_MSDASQL.VGSYS400.VGITFRLIB.F55125

 

[gmmastros]

126 is slightly better because of internationalization issues. I see now that the real problem is with single-quotes.

declare @DateTime varchar(50)
set @DateTime = convert(varchar(50), current_timestamp, 126)

exec('insert into ZyTax.dbo.ttable(
                             report_state, report_datetime
select                       ztadds, [!]''[/!]' + @DateTime +
                            '[!]''[/!] from AS400SRV_MSDASQL.VGSYS400.' +  @AliasLibrary + '.' + @SourceFile)

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[snufse1]

Good job, that worked. Thank you.

 

[SQLBill]

snufse1,

that's why I made the suggestion that I did. Whenever you are dynamically creating a script use the PRINT command to see what the script REALLY looks like. You can even highlight that script and parse it to see where errors might be. Then you make the necessary fixes and execute the final result.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875