Date help

Hi -
I have a column in a table that is supposed to be a date, it's data type is
text since it's source is a text file. Which looks like this:
Chg_Date
19990105
20000323
20010526
00000000
00000000
as you can see, there are some dates with 0's which of course, are invalid,
I need to insert this date field in another table even if the date is all
0's, because there is not always a chg_date associated with a record.
Here is what I have tried since I know the 0's are invalid:
select chg_date
from table
where ISDATE(chg_date ) <> 0
and of course, I only get the records that are not 0's - but how do I get
all of the dates whether they are valid or not?Patrice,
Try using a reference date for the 0's. In SQL Server, integer values can be
converted to datetime data type, so 0 (zero) will be converted to 01/01/1900
(19000101).
insert into t2(c1)
select case when isdate(c1) then c1 else '19000101' end
from t1
go
AMB
"Patrice" wrote:

> Hi -
> I have a column in a table that is supposed to be a date, it's data type i
s
> text since it's source is a text file. Which looks like this:
> Chg_Date
> 19990105
> 20000323
> 20010526
> 00000000
> 00000000
> as you can see, there are some dates with 0's which of course, are invalid
,
> I need to insert this date field in another table even if the date is all
> 0's, because there is not always a chg_date associated with a record.
> Here is what I have tried since I know the 0's are invalid:
> select chg_date
> from table
> where ISDATE(chg_date ) <> 0
> and of course, I only get the records that are not 0's - but how do I get
> all of the dates whether they are valid or not?|||You want to make the distinction in your select clause, not your where
clause. That was you are still getting the rows. Putting it in the where
clause is for filtering rows. Manipulate your select clause to work with
columns.
SELECT CASE WHEN ISDATE(chg_date) <> 0 THEN chg_date ELSE NULL END
FROM [table]
HTH,
John Scragg
"Patrice" wrote:

> Hi -
> I have a column in a table that is supposed to be a date, it's data type i
s
> text since it's source is a text file. Which looks like this:
> Chg_Date
> 19990105
> 20000323
> 20010526
> 00000000
> 00000000
> as you can see, there are some dates with 0's which of course, are invalid
,
> I need to insert this date field in another table even if the date is all
> 0's, because there is not always a chg_date associated with a record.
> Here is what I have tried since I know the 0's are invalid:
> select chg_date
> from table
> where ISDATE(chg_date ) <> 0
> and of course, I only get the records that are not 0's - but how do I get
> all of the dates whether they are valid or not?|||Correction:
insert into t2(c1)
select case when isdate(c1) = 1 then c1 else '19000101' end
from t1
go
AMB
"Alejandro Mesa" wrote:
> Patrice,
> Try using a reference date for the 0's. In SQL Server, integer values can
be
> converted to datetime data type, so 0 (zero) will be converted to 01/01/19
00
> (19000101).
> insert into t2(c1)
> select case when isdate(c1) then c1 else '19000101' end
> from t1
> go
>
> AMB
> "Patrice" wrote:
>