Query help

I am trying to find invalid dates in a column after loading from a text file
but the queries I have doesn't give me any result.
Because of the error, I have loaded column as a varchar rather than datetime
so that I can find the invalid dates. My query is:
Select col from dbo.mytable
where substr(col, 0, 2) NOT in (01,02,03,04,05,06,07,08,09,10,11,12) -- For
month
-----
Select col from dbo.mytable
where substr(col, 4, 2) NOT in
(01,02,03,04,05,06,07,08,09,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31) -- For Day
-----
Select col from dbo.mytable
where substr(col, 7, 2) NOT in (19,20) -- For year
-----
Typical dates in the text file are
01/12/1958
09/05/2007
04/23/1978
12/28/2003
01/01/1900
If I try to change the column data type from varchar to datetime I get an
error like 'can not convert to datetime, date is out of range'.
Thanks for any help.Well, I can think of a procedural approach.
Load the data as you have done and create a table variable with a datetime
column.
For each row, attempt an insert, or update to the table variable (put a
dummy row in 1st) and if the insert fails, or @.@.ROWCOUNT = 0, put that key
somewhere else. Or on success, move the row to the final table, or choose
your own logic.
All that said, I was pretty sure DTS did this kind of thing for you.
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:BDE15221-20D3-46AE-B901-1965A8519493@.microsoft.com...
>I am trying to find invalid dates in a column after loading from a text
>file
> but the queries I have doesn't give me any result.
> Because of the error, I have loaded column as a varchar rather than
> datetime
> so that I can find the invalid dates. My query is:
> Select col from dbo.mytable
> where substr(col, 0, 2) NOT in (01,02,03,04,05,06,07,08,09,10,11,12) --
> For
> month
> -----
> Select col from dbo.mytable
> where substr(col, 4, 2) NOT in
> (01,02,03,04,05,06,07,08,09,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31)
> -- For Day
> -----
> Select col from dbo.mytable
> where substr(col, 7, 2) NOT in (19,20) -- For year
> -----
> Typical dates in the text file are
> 01/12/1958
> 09/05/2007
> 04/23/1978
> 12/28/2003
> 01/01/1900
> If I try to change the column data type from varchar to datetime I get an
> error like 'can not convert to datetime, date is out of range'.
> Thanks for any help.|||The message from DTS is not what the data is. It is something like this (I
don't have the exact error):
Can not insert data Source column (Column number 70) data type string -
destination column (column_name) data type
DATE..................something like that which doesn't tell you what
the exact invalid date is. If the data is small, you can scroll it up and
down and find it but when it is 2.4 million rows, it is hard to find it
manually or when the text file is ~800 MB.........
"Jay" wrote:
> Well, I can think of a procedural approach.
> Load the data as you have done and create a table variable with a datetime
> column.
> For each row, attempt an insert, or update to the table variable (put a
> dummy row in 1st) and if the insert fails, or @.@.ROWCOUNT = 0, put that key
> somewhere else. Or on success, move the row to the final table, or choose
> your own logic.
> All that said, I was pretty sure DTS did this kind of thing for you.
>
> "DXC" <DXC@.discussions.microsoft.com> wrote in message
> news:BDE15221-20D3-46AE-B901-1965A8519493@.microsoft.com...
> >I am trying to find invalid dates in a column after loading from a text
> >file
> > but the queries I have doesn't give me any result.
> >
> > Because of the error, I have loaded column as a varchar rather than
> > datetime
> > so that I can find the invalid dates. My query is:
> >
> > Select col from dbo.mytable
> > where substr(col, 0, 2) NOT in (01,02,03,04,05,06,07,08,09,10,11,12) --
> > For
> > month
> > -----
> > Select col from dbo.mytable
> > where substr(col, 4, 2) NOT in
> > (01,02,03,04,05,06,07,08,09,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31)
> > -- For Day
> > -----
> > Select col from dbo.mytable
> > where substr(col, 7, 2) NOT in (19,20) -- For year
> > -----
> >
> > Typical dates in the text file are
> >
> > 01/12/1958
> > 09/05/2007
> > 04/23/1978
> > 12/28/2003
> > 01/01/1900
> >
> > If I try to change the column data type from varchar to datetime I get an
> > error like 'can not convert to datetime, date is out of range'.
> >
> > Thanks for any help.
>
>|||I've only used DTS when I had full control of the complete data stream, but
I though I remembered something about exceptions.
Still, if that isn't working for you and no one can help you with exception
filters in DTS, write the import filter yourself.
You could also try:
select *
from table
where datestr not LIKE '[0-1][0-9]/[[0-3][0-9]/[1-2][09][0-9][0-9]'
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:91B0F853-FD70-415D-A31E-17CE8EDC4E0E@.microsoft.com...
> The message from DTS is not what the data is. It is something like this (I
> don't have the exact error):
> Can not insert data Source column (Column number 70) data type string -
> destination column (column_name) data type
> DATE..................something like that which doesn't tell you what
> the exact invalid date is. If the data is small, you can scroll it up and
> down and find it but when it is 2.4 million rows, it is hard to find it
> manually or when the text file is ~800 MB.........
>
> "Jay" wrote:
>> Well, I can think of a procedural approach.
>> Load the data as you have done and create a table variable with a
>> datetime
>> column.
>> For each row, attempt an insert, or update to the table variable (put a
>> dummy row in 1st) and if the insert fails, or @.@.ROWCOUNT = 0, put that
>> key
>> somewhere else. Or on success, move the row to the final table, or choose
>> your own logic.
>> All that said, I was pretty sure DTS did this kind of thing for you.
>>
>> "DXC" <DXC@.discussions.microsoft.com> wrote in message
>> news:BDE15221-20D3-46AE-B901-1965A8519493@.microsoft.com...
>> >I am trying to find invalid dates in a column after loading from a text
>> >file
>> > but the queries I have doesn't give me any result.
>> >
>> > Because of the error, I have loaded column as a varchar rather than
>> > datetime
>> > so that I can find the invalid dates. My query is:
>> >
>> > Select col from dbo.mytable
>> > where substr(col, 0, 2) NOT in (01,02,03,04,05,06,07,08,09,10,11,12) --
>> > For
>> > month
>> > -----
>> > Select col from dbo.mytable
>> > where substr(col, 4, 2) NOT in
>> > (01,02,03,04,05,06,07,08,09,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31)
>> > -- For Day
>> > -----
>> > Select col from dbo.mytable
>> > where substr(col, 7, 2) NOT in (19,20) -- For year
>> > -----
>> >
>> > Typical dates in the text file are
>> >
>> > 01/12/1958
>> > 09/05/2007
>> > 04/23/1978
>> > 12/28/2003
>> > 01/01/1900
>> >
>> > If I try to change the column data type from varchar to datetime I get
>> > an
>> > error like 'can not convert to datetime, date is out of range'.
>> >
>> > Thanks for any help.
>>|||On Aug 28, 4:40 am, DXC <D...@.discussions.microsoft.com> wrote:
> I am trying to find invalid dates in a column after loading from a text f=ile
> but the queries I have doesn't give me any result.
> Because of the error, I have loaded column as a varchar rather than datet=ime
> so that I can find the invalid dates. My query is:
> Select col from dbo.mytable
> where substr(col, 0, 2) NOT in (01,02,03,04,05,06,07,08,09,10,11,12) -- F=or
> month
> ----=--=AD--
> Select col from dbo.mytable
> where substr(col, 4, 2) NOT in
> (01,02,03,04,05,06,07,08,09,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,=25=AD,26,27,28,29,30,31) -- For Day
> ----=--=AD--
> Select col from dbo.mytable
> where substr(col, 7, 2) NOT in (19,20) -- For year
> ----=--=AD--
> Typical dates in the text file are
> 01/12/1958
> 09/05/2007
> 04/23/1978
> 12/28/2003
> 01/01/1900
> If I try to change the column data type from varchar to datetime I get an
> error like 'can not convert to datetime, date is out of range'.
> Thanks for any help.
Hi, there is an isdate function. Find the invalid dates and handle
them separately. HTH.|||That did it............Thanks..........
"SB" wrote:
> On Aug 28, 4:40 am, DXC <D...@.discussions.microsoft.com> wrote:
> > I am trying to find invalid dates in a column after loading from a text file
> > but the queries I have doesn't give me any result.
> >
> > Because of the error, I have loaded column as a varchar rather than datetime
> > so that I can find the invalid dates. My query is:
> >
> > Select col from dbo.mytable
> > where substr(col, 0, 2) NOT in (01,02,03,04,05,06,07,08,09,10,11,12) -- For
> > month
> > ----
> > Select col from dbo.mytable
> > where substr(col, 4, 2) NOT in
> > (01,02,03,04,05,06,07,08,09,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25-,26,27,28,29,30,31) -- For Day
> > ----
> > Select col from dbo.mytable
> > where substr(col, 7, 2) NOT in (19,20) -- For year
> > ----
> >
> > Typical dates in the text file are
> >
> > 01/12/1958
> > 09/05/2007
> > 04/23/1978
> > 12/28/2003
> > 01/01/1900
> >
> > If I try to change the column data type from varchar to datetime I get an
> > error like 'can not convert to datetime, date is out of range'.
> >
> > Thanks for any help.
> Hi, there is an isdate function. Find the invalid dates and handle
> them separately. HTH.
>