cast datetime to yyyymm format

How can I cast a datetime to a format of yyyymm?
firstdate and seconddate are both datetime fields and I want to use them in
the yyyymm format in my where clause below.
Declare @.curdate datetime
Set @.curdate = 2005/11
select firstdate, seconddate
from dates
where @.curdate between firstdate and seconddate
and firstdate < seconddate
Thanks!
MattI do not know any style argument to format it like that, but you can buid it
yourself in a function
DECLARE @.TestDate datetime
SET @.TestDate = GetDate()
SELECT CONVERT(varchar(4), @.TestDate, 120) + SUBSTRING(CONVERT(varchar(7),
@.TestDate, 120), 6, 2)
You can buid a function that takes a datetime argument and returns a varchar
.
HTH,
John Scragg
"Matt" wrote:

> How can I cast a datetime to a format of yyyymm?
> firstdate and seconddate are both datetime fields and I want to use them i
n
> the yyyymm format in my where clause below.
> Declare @.curdate datetime
> Set @.curdate = 2005/11
> select firstdate, seconddate
> from dates
> where @.curdate between firstdate and seconddate
> and firstdate < seconddate
> Thanks!
> Matt|||Try,
select convert(char(6), getdate(), 112)
go
AMB
"Matt" wrote:

> How can I cast a datetime to a format of yyyymm?
> firstdate and seconddate are both datetime fields and I want to use them i
n
> the yyyymm format in my where clause below.
> Declare @.curdate datetime
> Set @.curdate = 2005/11
> select firstdate, seconddate
> from dates
> where @.curdate between firstdate and seconddate
> and firstdate < seconddate
> Thanks!
> Matt|||2005/11 is not a date. A date requires year, month and day.
e.g., to find dates in Nov 2005,
set @.curdate='20051101'
select firstdate, seconddate
from dates
where firstdate<=@.curdate and seconddate>=dateadd(month,1,@.curdate)-1
and firstdate<seconddate
Matt wrote:

>How can I cast a datetime to a format of yyyymm?
>firstdate and seconddate are both datetime fields and I want to use them in
>the yyyymm format in my where clause below.
>Declare @.curdate datetime
>Set @.curdate = 2005/11
> select firstdate, seconddate
>from dates
>where @.curdate between firstdate and seconddate
> and firstdate < seconddate
>Thanks!
>Matt
>|||Thanks to you all for your help. It does appear that I get correct results
when I run the @.curdate as a char. When running as datetime the where claus
e
fails.
"Matt" wrote:

> How can I cast a datetime to a format of yyyymm?
> firstdate and seconddate are both datetime fields and I want to use them i
n
> the yyyymm format in my where clause below.
> Declare @.curdate datetime
> Set @.curdate = 2005/11
> select firstdate, seconddate
> from dates
> where @.curdate between firstdate and seconddate
> and firstdate < seconddate
> Thanks!
> Matt|||On Wed, 26 Oct 2005 12:41:18 -0700, Matt wrote:

>Thanks to you all for your help. It does appear that I get correct results
>when I run the @.curdate as a char. When running as datetime the where clau
se
>fails.
Hi Matt,
As Trey already wrote: 2005/11 is not a datetime. It is the division of
an integer by another integer. The result of 2005/11 is 182 (the decimal
portion is lost, because integer division is used). This result is then
converted to datetime, which will yield the 182nd day of the year 1900.
To check this, run
Declare @.curdate datetime
Set @.curdate = 2005/11
SELECT @.curdate
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)