|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Select data that can't be converted.I have a table with a string column, most of the data in there is in a format that can be converted into a datetime type. How can i just retrieve the rows that cannot be converted? ie.. TestColumn Row1 12/04/2004 Row2 03/09/2003 Row3 dd/mm/yyyy Row4 string Row5 09/07/1998 I want a select statement that only returns those rows where the column cannot be converted to a date. hope this makes sense. Thanks, R. R1gg4 wrote:
Show quote > Hi all, This isn't perfect, but might get you what you need:> > I have a table with a string column, most of the data in there is in a > format that can be converted into a datetime type. > > How can i just retrieve the rows that cannot be converted? > > ie.. > TestColumn > Row1 12/04/2004 > Row2 03/09/2003 > Row3 dd/mm/yyyy > Row4 string > Row5 09/07/1998 > > I want a select statement that only returns those rows where the column > cannot be converted to a date. hope this makes sense. > > Thanks, > R. > > SELECT * FROM table WHERE ISDATE(TestColumn) = 0 Thanks for that,
Are there IS functions for all the data types? Regards, R. Show quote "Tracy McKibben" <tr***@realsqlguy.com> wrote in message news:%23HtIA8npGHA.2292@TK2MSFTNGP05.phx.gbl... > R1gg4 wrote: >> Hi all, >> >> I have a table with a string column, most of the data in there is in a >> format that can be converted into a datetime type. >> >> How can i just retrieve the rows that cannot be converted? >> >> ie.. >> TestColumn >> Row1 12/04/2004 >> Row2 03/09/2003 >> Row3 dd/mm/yyyy >> Row4 string >> Row5 09/07/1998 >> >> I want a select statement that only returns those rows where the column >> cannot be converted to a date. hope this makes sense. >> >> Thanks, >> R. > > This isn't perfect, but might get you what you need: > > SELECT * > FROM table > WHERE ISDATE(TestColumn) = 0 > > > -- > Tracy McKibben > MCDBA > http://www.realsqlguy.com R1gg4 wrote:
> Thanks for that, Not all of them, no. Books Online will tell you which ones there are.> > Are there IS functions for all the data types? > Thanks,
R. Show quote "Tracy McKibben" <tr***@realsqlguy.com> wrote in message news:uGTDxJopGHA.516@TK2MSFTNGP05.phx.gbl... > R1gg4 wrote: >> Thanks for that, >> >> Are there IS functions for all the data types? >> > > Not all of them, no. Books Online will tell you which ones there are. > > > -- > Tracy McKibben > MCDBA > http://www.realsqlguy.com Try using function IsDate and "like" operator.
select * from dbo.t1 where c1 IsDate(c1) = 0 go -- if you want just those rows where the column has this format select * from dbo.t1 where c1 not like '[0-9][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9]' go AMB Show quote "R1gg4" wrote: > Hi all, > > I have a table with a string column, most of the data in there is in a > format that can be converted into a datetime type. > > How can i just retrieve the rows that cannot be converted? > > ie.. > TestColumn > Row1 12/04/2004 > Row2 03/09/2003 > Row3 dd/mm/yyyy > Row4 string > Row5 09/07/1998 > > I want a select statement that only returns those rows where the column > cannot be converted to a date. hope this makes sense. > > Thanks, > R. > > > Correction,
select * from dbo.t1 where IsDate(c1) = 0 go AMB Show quote "Alejandro Mesa" wrote: > Try using function IsDate and "like" operator. > > select * > from dbo.t1 > where c1 IsDate(c1) = 0 > go > > -- if you want just those rows where the column has this format > select * > from dbo.t1 > where c1 not like '[0-9][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9]' > go > > > AMB > > > "R1gg4" wrote: > > > Hi all, > > > > I have a table with a string column, most of the data in there is in a > > format that can be converted into a datetime type. > > > > How can i just retrieve the rows that cannot be converted? > > > > ie.. > > TestColumn > > Row1 12/04/2004 > > Row2 03/09/2003 > > Row3 dd/mm/yyyy > > Row4 string > > Row5 09/07/1998 > > > > I want a select statement that only returns those rows where the column > > cannot be converted to a date. hope this makes sense. > > > > Thanks, > > R. > > > > > >  |
|||||||||||||||||||||||
Other interesting topics