Date manipulations

3 Aug 2006 11:41 AM

ricky

Hi

I am trying to write :

SELECT

CAST(CAST(YEAR(GETDATE()) AS CHAR(4)) + RIGHT('0' +
CAST((MONTH(GETDATE())-1) AS VARCHAR(2)), 2) + '01' AS DATETIME)

but instead of hardcoding the DAY to '1', I wish to use the GETDATE(), as
follows:

SELECT
(CAST(CAST(YEAR(GETDATE()) AS CHAR(4)) + RIGHT('0' +
CAST((MONTH(GETDATE())-1) AS VARCHAR(2)), 2) + (CAST(DAY(GETDATE()) AS
CHAR(2))) AS DATETIME))

But this doesn't seem to work?

Any ideas on why this is?

Kind Regards

Ricky

3 Aug 2006 12:03 PM

Uri Dimant

Ricky
SELECT DATEADD(month,DATEDIFF(month,0,GETDATE())-1,0)

Show quote

"ricky" <ri***@ricky.com> wrote in message
news:%23TZk5GvtGHA.5056@TK2MSFTNGP06.phx.gbl...
> Hi
>
> I am trying to write :
>
> SELECT
>
> CAST(CAST(YEAR(GETDATE()) AS CHAR(4)) + RIGHT('0' +
> CAST((MONTH(GETDATE())-1) AS VARCHAR(2)), 2) + '01' AS DATETIME)
>
> but instead of hardcoding the DAY to '1', I wish to use the GETDATE(), as
> follows:
>
> SELECT
> (CAST(CAST(YEAR(GETDATE()) AS CHAR(4)) + RIGHT('0' +
> CAST((MONTH(GETDATE())-1) AS VARCHAR(2)), 2) + (CAST(DAY(GETDATE()) AS
> CHAR(2))) AS DATETIME))
>
> But this doesn't seem to work?
>
> Any ideas on why this is?
>
> Kind Regards
>
> Ricky
>
>
>
>

3 Aug 2006 12:58 PM

ricky

Thanks guys for your contributions.

Found all solutions very useful, in more than one query.

Kind regards

Ricky

Show quote

"Uri Dimant" <u***@iscar.co.il> wrote in message
news:eQRmhSvtGHA.1888@TK2MSFTNGP03.phx.gbl...
> Ricky
> SELECT DATEADD(month,DATEDIFF(month,0,GETDATE())-1,0)
>
>
>
> "ricky" <ri***@ricky.com> wrote in message
> news:%23TZk5GvtGHA.5056@TK2MSFTNGP06.phx.gbl...
> > Hi
> >
> > I am trying to write :
> >
> > SELECT
> >
> > CAST(CAST(YEAR(GETDATE()) AS CHAR(4)) + RIGHT('0' +
> > CAST((MONTH(GETDATE())-1) AS VARCHAR(2)), 2) + '01' AS DATETIME)
> >
> > but instead of hardcoding the DAY to '1', I wish to use the GETDATE(),
as
> > follows:
> >
> > SELECT
> > (CAST(CAST(YEAR(GETDATE()) AS CHAR(4)) + RIGHT('0' +
> > CAST((MONTH(GETDATE())-1) AS VARCHAR(2)), 2) + (CAST(DAY(GETDATE()) AS
> > CHAR(2))) AS DATETIME))
> >
> > But this doesn't seem to work?
> >
> > Any ideas on why this is?
> >
> > Kind Regards
> >
> > Ricky
> >
> >
> >
> >
>
>