| Author |
Topic |
|
zhshqzyc
Posting Yak Master
240 Posts |
 Posted - 2009-03-16 : 13:21:58
|
Hello,Suppose I set a variable @t1 as current datedeclare @t1 varchar(50)select @t1 = '03/16/2009 12:00:00 PM' My question is how can I get the time one week before @t1? declare @t2 varchar(50)I want @t2 ='03/9/2009 12:00:00 PM'Thanks |
|
|
mfemenel
Professor Frink
1421 Posts |
Posted - 2009-03-16 : 13:24:38
|
| declare @mydate datetimeset @mydate=GETDATE()select DATEADD(WEEK,-1,@mydate)Mike"oh, that monkey is going to pay" |
 |
|
|
zhshqzyc
Posting Yak Master
240 Posts |
Posted - 2009-03-16 : 13:32:40
|
| [code]declare @mydate varchar(50)[/code]If I define @mydate as a string, then how? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-03-16 : 13:36:04
|
| dont use varchar variables to store dates. that will make date manipulations difficult as you cant use date functions on them. so use datetime variabnle and then use dateadd function to get dates before week. |
|
|
|
zhshqzyc
Posting Yak Master
240 Posts |
Posted - 2009-03-16 : 13:39:19
|
| I know it is difficult to manage it but I must use it.Because I am using dynamic sql. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-03-16 : 13:41:38
|
| you can use datetime variables in dynamic sql. |
|
|
|
zhshqzyc
Posting Yak Master
240 Posts |
Posted - 2009-03-16 : 13:47:08
|
| Let me explain a little bit.I want assing a variable @t1 in a dynamic query.select @qry = 'select bdt from status'I want to assign it to @t1, then get @t2 based on @t1.Need help. |
|
|
|
zhshqzyc
Posting Yak Master
240 Posts |
Posted - 2009-03-16 : 13:48:43
|
| the result of exec(qry) is the current time. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-03-16 : 13:51:15
|
quote:
Originally posted by zhshqzyc
the result of exec(qry) is the current time.
if you want to assign values of variables inside dynamic sql then you need to use sp_executesql not exec(). see books online for examples of sp_executesql |
|
|
|
zhshqzyc
Posting Yak Master
240 Posts |
Posted - 2009-03-16 : 14:13:09
|
Can you tell me what's wrong?declare @qry varchar(8000)declare @t1 varchar(50)declare @t2 varchar(50)set @qry = N'select bdt from status'execute sp_executesql @qry,@t1 OUTPUTPRINT @t1 Procedure expects parameter '@statement' of 'ntext/nchar/nvarchar'. Thanks |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-03-16 : 14:15:17
|
| where have you used t1,t2,.. in query? |
|
|
|
zhshqzyc
Posting Yak Master
240 Posts |
Posted - 2009-03-16 : 14:35:35
|
| Could you give the code directly?I don't understand the example on online book. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-03-16 : 14:38:03
|
| http://msdn.microsoft.com/en-us/library/ms175170.aspx |
|
|
|
zhshqzyc
Posting Yak Master
240 Posts |
Posted - 2009-03-16 : 14:52:12
|
| Ignore t2,I just want to assing the query result to @t1. |
|
|
|
mfemenel
Professor Frink
1421 Posts |
Posted - 2009-03-16 : 15:01:01
|
quote:
Originally posted by zhshqzyc
Can you tell me what's wrong?declare @qry varchar(8000)declare @t1 varchar(50)declare @t2 varchar(50)set @qry = N'select bdt from status'execute sp_executesql @qry,@t1 OUTPUTPRINT @t1 Procedure expects parameter '@statement' of 'ntext/nchar/nvarchar'. Thanks
you must declarel your @qry variable as nvarchar(4000) instead of varchar.To answer your other question further up. If your date is stored in a varchar field then convert it to a date and then do the dateadd to it. You can find the convert syntax in BOL.Mike"oh, that monkey is going to pay" |
|
|
|
zhshqzyc
Posting Yak Master
240 Posts |
Posted - 2009-03-16 : 16:24:03
|
I am not sure what's wrong?Can you look at my code?select @qry = N'select BUSINESS_DATE from STATUS'execute sp_executesql @qry, N'@d datetime output', @d output |
|
|
|
|
Get a specific date