datetime

create proc dbo.GetList
(
@.OrgList varchar(1000),
@.startDateTime datetime

)
as
begin

declare @.SQL varchar(1000)

set @.SQL = 'Select A.TransactionID,
A.PermitId,
A.IssuingOrganizationId,
A.VehicleId

From Vehicle A WITH (NOLOCK)
join PurchasingCompany B WITH (NOLOCK)
on A.PurchasingCompanyId = B.PurchasingCompanyId
Where
A.IssueDate >= '+ '@.startDateTime' +' And
A.IssuingOrganizationId IN ('+ @.OrgList+')'
exec(@.sql)
end
go

This doesn't work, But If I substitute@.startDateTime with '2/1/2004', it works. I think iam missing some formatting, I tried several ways to make it work. Could anyone tell me how I should do this.

You are including the literal "@.StartDateTime", which is clearly not what you want.

create proc dbo.GetList
(
@.OrgList varchar(1000),
@.startDateTime datetime

)
as
begin

Look at the BOL article on CONVERT to determine what format is right for you - I am using the ODBC canonical format.

declare @.SQL varchar(1000)

set @.SQL = 'Select A.TransactionID,
A.PermitId,
A.IssuingOrganizationId,
A.VehicleId

From Vehicle A WITH (NOLOCK)
join PurchasingCompany B WITH (NOLOCK)
on A.PurchasingCompanyId = B.PurchasingCompanyId
Where
A.IssueDate >= '''+ CONVERT(nvarchar(30),@.startDateTime,120) +''' And
A.IssuingOrganizationId IN ('+ @.OrgList+')'
exec(@.sql)
end
go

|||That really worked for me, thank you so much for the reply.

DATETIME

Below SQL works
declare @.startdate datetime, @.enddate datetime, @.sql varchar(1000)
select @.startdate = '06/01/2003'
select @.enddate = '06/03/2003'
select top 10 * from mytable where mydate between @.startdate and @.enddate
But , below one error out with message
Server: Msg 241, Level 16, State 1, Line 4
Syntax error converting datetime from character string.
declare @.startdate datetime, @.enddate datetime, @.sql varchar(1000)
select @.startdate = '06/01/2003'
select @.enddate = '06/03/2003'
select @.sql = 'select * from mytable where mydate between ' + @.startdate +
' AND ' + @.enddate
execute @.sql
Here @.startdate and @.enddate are parameters and used inside a SP.
I have to use dynamic SQL for my logic and don't want to use CONVERT
function.
How to make this dynamic SQL work '
Thx
ShShamin,
the following code should work:
declare @.startdate datetime, @.enddate datetime, @.sql varchar(1000)
select @.startdate = '06/01/2003'
select @.enddate = '06/03/2003'
select @.sql = 'select * from MyTable where MyTime between '''
+ cast(@.startdate as varchar) + ''' AND ''' + cast (@.enddate as varchar) +
''''
execute (@.sql)
hope this helps
Quentin
"Shamim" <shamim.abdul@.railamerica.com> wrote in message
news:#TgE1pwSDHA.2196@.TK2MSFTNGP12.phx.gbl...
> Below SQL works
> declare @.startdate datetime, @.enddate datetime, @.sql varchar(1000)
> select @.startdate = '06/01/2003'
> select @.enddate = '06/03/2003'
> select top 10 * from mytable where mydate between @.startdate and
@.enddate
> But , below one error out with message
> Server: Msg 241, Level 16, State 1, Line 4
> Syntax error converting datetime from character string.
> declare @.startdate datetime, @.enddate datetime, @.sql varchar(1000)
> select @.startdate = '06/01/2003'
> select @.enddate = '06/03/2003'
> select @.sql = 'select * from mytable where mydate between ' + @.startdate
+
> ' AND ' + @.enddate
> execute @.sql
> Here @.startdate and @.enddate are parameters and used inside a SP.
> I have to use dynamic SQL for my logic and don't want to use CONVERT
> function.
> How to make this dynamic SQL work '
> Thx
> Sh
>
>

DatePart

I am stuck with a particular Datepart Query
Declare @.day char(2)
Declare @.month char(2)
Declare @.year char(4)
Declare @.date char(8)
set @.day = (select DATEPART(day, GETDATE()))
set @.month = (select DATEPART(month, GETDATE()))
set @.year = (select DATEPART(year, GETDATE()))
set @.date = (select @.day + @.month + @.year)
The results for this query are displayed as follows
151 2004
I actually need to pad 1 with 01 and also repeat the same for the Day. So
if the date was 1st Jan 2004 then my current query would return
1 1 2004 (require 01012004)
How do I do this? Any help appreciated.
Thanks Sarahnot sure if this is the 'correct' way but I notice you are declaring the
variables as strings, so this should work:
declare @.day char(8)
set @.day = (select DATEPART(month, GETDATE()))
if len(@.day) < 2
set @.day = '0' + @.day
Select @.day
Cheers,
James Goodman MCSE, MCDBA
http://www.angelfire.com/sports/f1pictures

DatePart

I am stuck with a particular Datepart Query
Declare @.day char(2)
Declare @.month char(2)
Declare @.year char(4)
Declare @.date char(8)
set @.day = (select DATEPART(day, GETDATE()))
set @.month = (select DATEPART(month, GETDATE()))
set @.year = (select DATEPART(year, GETDATE()))
set @.date = (select @.day + @.month + @.year)
The results for this query are displayed as follows
151 2004
I actually need to pad 1 with 01 and also repeat the same for the Day. So
if the date was 1st Jan 2004 then my current query would return
1 1 2004 (require 01012004)
How do I do this? Any help appreciated.
Thanks Sarahnot sure if this is the 'correct' way but I notice you are declaring the
variables as strings, so this should work:
declare @.day char(8)
set @.day = (select DATEPART(month, GETDATE()))
if len(@.day) < 2
set @.day = '0' + @.day
Select @.day
Cheers,
James Goodman MCSE, MCDBA
http://www.angelfire.com/sports/f1pictures|||SELECT CONVERT(char, getdate(), 103)
results in 15/01/2004
if you don't want the /
try
SELECT REPLACE( CONVERT(char, getdate(), 103), '/', '')
>--Original Message--
>I am stuck with a particular Datepart Query
>Declare @.day char(2)
>Declare @.month char(2)
>Declare @.year char(4)
>Declare @.date char(8)
> set @.day = (select DATEPART(day, GETDATE()))
> set @.month = (select DATEPART(month, GETDATE()))
> set @.year = (select DATEPART(year, GETDATE()))
> set @.date = (select @.day + @.month + @.year)
>The results for this query are displayed as follows
>151 2004
>I actually need to pad 1 with 01 and also repeat the same
for the Day. So
>if the date was 1st Jan 2004 then my current query would
return
>1 1 2004 (require 01012004)
>How do I do this? Any help appreciated.
>Thanks Sarah
>
>.
>