How to modify the date column values?

Question

Using SQL Server 2000, Date Column Datatype is varchar...

In my table date column values are like:

2009/01/31
2009/02/00
2009/02/01....
2009/03/31
2009/04/00
2009/04/01.... so on...,

I want to display 2009/01/31 instead of 2009/02/00, If date is 2009/02/00 it should display previous date.

How to make a query for this condition?

Answer 1

How about try this

DECLARE @Table TABLE(
        Val VARCHAR(10)
)

INSERT INTO @Table (Val) SELECT '2009/01/31'
INSERT INTO @Table (Val) SELECT '2009/02/00'

SELECT * FROM @Table

SELECT  CAST(SUBSTRING(Val, 1, 8) + CASE WHEN SUBSTRING(Val, 9, 2) = '00' THEN '01' ELSE SUBSTRING(Val, 9, 2) END AS DATETIME) + CASE WHEN SUBSTRING(Val, 9, 2) = '00' THEN -1 ELSE 0 END
FROM    @Table

Check if the last chars is 00, then set to 01 and add -1

Answer 2

OK, I didn't see the fact that there are dates with 00 as the day part.

I whipped up this brute force way.

I'm sure there are optimizations that can be applied, but this should give you a starting point.

select dateadd(dd, -1, convert(datetime, case when substring(Reverse(@WeirdDate), 1, 2) = '00' then reverse('1' + substring(Reverse(@WeirdDate), 2, len(@WeirdDate) - 1)) else @WeirdDate end, 101))

replace the @WeirdDate with the column name, and it seems to work

declare @WeirdDate varchar(10)
set @WeirdDate = '2009/04/00'
select dateadd(dd, -1, convert(datetime, case when substring(Reverse(@WeirdDate), 1, 2) = '00' then reverse('1' + substring(Reverse(@WeirdDate), 2, len(@WeirdDate) - 1)) else @WeirdDate end, 101))

set @WeirdDate = '2009/04/03'
select dateadd(dd, -1, convert(datetime, case when substring(Reverse(@WeirdDate), 1, 2) = '00' then reverse('1' + substring(Reverse(@WeirdDate), 2, len(@WeirdDate) - 1)) else @WeirdDate end, 101))

set @WeirdDate = '2009/01/00'
select dateadd(dd, -1, convert(datetime, case when substring(Reverse(@WeirdDate), 1, 2) = '00' then reverse('1' + substring(Reverse(@WeirdDate), 2, len(@WeirdDate) - 1)) else @WeirdDate end, 101))

Answer 3

To show the valid dates is pretty easy:

SELECT  
   CASE ISDATE(datecolumn) 
     WHEN 1 THEN CAST(datecolumn AS DATETIME) 
     ELSE NULL 
   END  AS 'DateValue'
FROM
  dbo.YourTable

But handling the invalid dates like '2009/02/00' is a bit trickier.....

But since you already seem to have the previous date (2009/01/31) - couldn't you just ignore the invalid dates???

SELECT  
   CAST(datecolumn AS DATETIME) 
FROM
   dbo.YourTable
WHERE
   ISDATE(datecolumn) = 1

Marc