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:02 PM

Dan Guzman

> 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?

The corrected version of your script (adding leading '0' to the day):

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

However, if your intention is to remove the time and subtract one month, use
DATEADD like the examples below. This is easier and will properly handle
year boundaries.

SELECT CAST(CONVERT(char(8), DATEADD(month, -1, GETDATE()), 112) AS
datetime)

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

--
Hope this helps.

Dan Guzman
SQL Server MVP

Show quote

"ricky" <ri***@ricky.com> wrote in message
news:OpsfOHvtGHA.2224@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
>
>
>
>
>