Date problem

1. @cp_dt declared twice i.e at 1st line & in @sqlcp01 . I have to put the
declaration in @sqlcp01 (within quote). If not, it failed with error 'Must
declare variable @cp_dt'
2. @cp_dt type is datetime while @sqlcp01 is string. I want to put @cp_dt
outside the quote. If I put it outside, I will get '@cp_dt conversion error'.
In normal query, it will be like this :-

select a, b, c, d, position_date, e, f, g, h, i into moon.dbo.CP_+@cp_file
from glob.dbo.cp where position_date = @cp_dt


"TheSQLGuru" wrote:

> Not sure how you expect us to help you without a single definition of the
> table(s) involved nor sample data for those tables nor expected outcomes...
>
> --
> Kevin G. Boles
> Indicium Resources, Inc.
> SQL Server MVP
> kgboles a earthlink dt net
>
>
> "GW" wrote in message
>
> > Need help.
> >
> > Having problem to bring the date into string. Currently, I have to declare
> > @cp_dt in sql string where @cp_dt is within the quote. I dont want to
> > re-declare @cp_dt (double work). Is there any other way to do the
> > following
> > :-
> >
> >
> > declare @cp_dt datetime,
> > @cp_dt_cv varchar(10),
> > @cp_dt_txt varchar(,
> > @cp_file varchar(6)
> > set @cp_dt = (select top 1 position_date from glob.dbo.cp order by
> > position_date desc)
> > set @cp_dt_cv = convert(varchar(10),@cp_dt,103)
> > set @cp_dt_txt = replace(@cp_dt_cv,'/','')
> > set @cp_file = (select right(ltrim(rtrim(@cp_dt_txt)),6) )
> > declare @sqlcp01 nvarchar(4000)
> > declare @sqlcp02 nvarchar(4000)
> > set @sqlcp02 = 'drop table moon.dbo.CP_'+@cp_file
> > set @sqlcp01 = 'declare @cp_dt datetime '+
> > ' set @cp_dt = (select top 1 position_date from glob.dbo.cp order by
> > position_date desc) ' +
> > 'select a, b, c, d, '+
> > 'position_date, e, f, '+
> > 'g, h, i '+
> > 'into moon.dbo.CP_'+@cp_file+
> > ' from glob.dbo.cp '+
> > 'where position_date = @cp_dt'
> >
> > if exists ( select * from dbo.sysobjects where id =
> > object_id(N'moon.dbo.CP_'+@cp_file)
> > and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> >
> > exec sp_executesql @sqlcp02
> >
> > exec sp_executesql @sqlcp01
> >
> > declare @sqlcp03 nvarchar(4000)
> > set @sqlcp03 = 'ALTER TABLE CP_'+@cp_file +
> > ' ADD j VARCHAR(10), '+
> > 'k VARCHAR(10), m VARCHAR(3), n VARCHAR(1), '+
> > 'p varchar(2), q varchar(3) '
> >
> > exec sp_executesql @sqlcp03
> >
>
>
> .
>
 >> Stay informed about: Date problem 

Erland Sommarskog wrote two must-read articles on dynamic SQL, which I
strongly suggest you read before attempting any kind of work involving
dynamic SQL:

http://www.sommarskog.se/dynamic_sql.html
http://www.sommarskog.se/dyn-search.html

If, for some reason, you're in a hurry, you can start by PRINTing your query
string and reviewing it instead of executing it and then trying to guess
what's wrong with it.

And as Kevin already pointed out, next time, please, provide DDL, sample
data and expected results.


ML

---
Matija Lah, SQL Server MVP
http://milambda.blogspot.com/
 >> Stay informed about: Date problem 

Hello GW,

Although this is not 'your' solution it may help you to your solution.

1.
Reduced the number of parameters used for the date to two.
One parameter to hold the date in date time format.
One parameter to hold the string which is used as part of the filename.
2.
All declarations at the beginning of the script.
3.
The strings to be executes are formatted for better reading.
The dateparts in the string are filled with placeholders which are replaced
in the string.

Advise:
Read the site from Tibor on datetime datetypes:
http://www.karaszi.com/sqlserver/info_datetime.asp
Prefere dataformats with yyyy mm dd, (also in filenames and in code).

In your example some elements are missing, examples, but also the table
definitions,
most important, the 'datetime' field from which the cp_dt is picked up from.

The code below does run, but is only an illustration, the prints should be
replaced
by execution statements.

Ben Brugman

---------------------------------------------------------------------------------------------
declare @sqlcp01 nvarchar(4000)
declare @sqlcp02 nvarchar(4000)
declare @sqlcp03 nvarchar(4000)

declare @cp_dt datetime
declare @cp_file varchar(6)

--
-- Pick up the date
-- In a datetime format and a string format for the file.
-- The getdate() should be replaced the the technique to pick up the
'correct' date.
--
---------------------------------------------------------------------------------------------
set @cp_dt =
-- Pick up the date
set @cp_file = SUBSTRING(convert(varchar(10), @cp_dt,126),6,2)+ -- Pick up
mm
SUBSTRING(convert(varchar(10), @cp_dt,126),1,4) -- Pick up
yyyy


--
-- Build the strings which are needed.
-- Strings are represented in readable format.
-- Constants are replaces afterwards to keep the strings readable.
--
---------------------------------------------------------------------------------------------
-- describe here the purpose of this statement.
--
set @sqlcp01 = 'drop table moon.dbo.CP '+@cp_file

---------------------------------------------------------------------------------------------
-- describe here the purpose of this statement.
--
set @sqlcp02 =
'
select a, b, c, d, position_date, e, f, g, h, i
into moon.dbo.CP_DDDD01
from glob.dbo.cp
where position_date = ''DDDD02''
'
set @sqlcp02 = REPLACE (@sqlcp02, 'DDDD01', @cp_file)
set @sqlcp02 = REPLACE (@sqlcp02, 'DDDD02', convert(varchar(10),@cp_dt,121))

---------------------------------------------------------------------------------------------
-- describe here the purpose of this statement.
--
set @sqlcp03 =
'
ALTER TABLE CP_DDDD01
ADD j VARCHAR(10), k VARCHAR(10), m VARCHAR(3), n VARCHAR(1), p
varchar(2), q varchar(3)
'
set @sqlcp03 = REPLACE (@sqlcp03, 'DDDD01', @cp_file)

--
-- Print the strings.
-- This should be replaced by executing the strings.
--
print @sqlcp01
print '-------------------------'
print @sqlcp02
print '-------------------------'
print @sqlcp03
print '-------------------------'






"GW" wrote in message

> 1. @cp_dt declared twice i.e at 1st line & in @sqlcp01 . I have to put the
> declaration in @sqlcp01 (within quote). If not, it failed with error 'Must
> declare variable @cp_dt'
> 2. @cp_dt type is datetime while @sqlcp01 is string. I want to put @cp_dt
> outside the quote. If I put it outside, I will get '@cp_dt conversion
> error'.
> In normal query, it will be like this :-
>
> select a, b, c, d, position_date, e, f, g, h, i into moon.dbo.CP_+@cp_file
> from glob.dbo.cp where position_date = @cp_dt
>
>
> "TheSQLGuru" wrote:
>
>> Not sure how you expect us to help you without a single definition of the
>> table(s) involved nor sample data for those tables nor expected
>> outcomes...
>>
>> --
>> Kevin G. Boles
>> Indicium Resources, Inc.
>> SQL Server MVP
>> kgboles a earthlink dt net
>>
>>
>> "GW" wrote in message
>>
>> > Need help.
>> >
>> > Having problem to bring the date into string. Currently, I have to
>> > declare
>> > @cp_dt in sql string where @cp_dt is within the quote. I dont want to
>> > re-declare @cp_dt (double work). Is there any other way to do the
>> > following
>> > :-
>> >
>> >
>> > declare @cp_dt datetime,
>> > @cp_dt_cv varchar(10),
>> > @cp_dt_txt varchar(,
>> > @cp_file varchar(6)
>> > set @cp_dt = (select top 1 position_date from glob.dbo.cp order by
>> > position_date desc)
>> > set @cp_dt_cv = convert(varchar(10),@cp_dt,103)
>> > set @cp_dt_txt = replace(@cp_dt_cv,'/','')
>> > set @cp_file = (select right(ltrim(rtrim(@cp_dt_txt)),6) )
>> > declare @sqlcp01 nvarchar(4000)
>> > declare @sqlcp02 nvarchar(4000)
>> > set @sqlcp02 = 'drop table moon.dbo.CP_'+@cp_file
>> > set @sqlcp01 = 'declare @cp_dt datetime '+
>> > ' set @cp_dt = (select top 1 position_date from glob.dbo.cp order by
>> > position_date desc) ' +
>> > 'select a, b, c, d, '+
>> > 'position_date, e, f, '+
>> > 'g, h, i '+
>> > 'into moon.dbo.CP_'+@cp_file+
>> > ' from glob.dbo.cp '+
>> > 'where position_date = @cp_dt'
>> >
>> > if exists ( select * from dbo.sysobjects where id =
>> > object_id(N'moon.dbo.CP_'+@cp_file)
>> > and OBJECTPROPERTY(id, N'IsUserTable') = 1)
>> >
>> > exec sp_executesql @sqlcp02
>> >
>> > exec sp_executesql @sqlcp01
>> >
>> > declare @sqlcp03 nvarchar(4000)
>> > set @sqlcp03 = 'ALTER TABLE CP_'+@cp_file +
>> > ' ADD j VARCHAR(10), '+
>> > 'k VARCHAR(10), m VARCHAR(3), n VARCHAR(1), '+
>> > 'p varchar(2), q varchar(3) '
>> >
>> > exec sp_executesql @sqlcp03
>> >
>>
>>
>> .
>>
 >> Stay informed about: Date problem