Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

How to find/delete invalid date?

3 posts in General Discussion Last posting was on 2009-09-25 15:20:53.0Z
AJ Posted on 2009-09-24 19:37:12.0Z
Sender: 6a37.4abbbdf8.1804289383@sybase.com
From: AJ
Newsgroups: sybase.public.ase.general
Subject: How to find/delete invalid date?
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4abbca68.6bf7.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 24 Sep 2009 12:37:12 -0700
X-Trace: forums-1-dub 1253821032 10.22.241.41 (24 Sep 2009 12:37:12 -0700)
X-Original-Trace: 24 Sep 2009 12:37:12 -0700, 10.22.241.41
Lines: 8
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28368
Article PK: 77613

Hello,
I need to delete some of the rows with invalid date got
loaded into the tables in the db during the import; not sure
what is the date function I should use. Here are some of the
examples of dates-
'4/1/5523', '5/9/5598', '3/26/5568' .

Thanks


Sherlock, Kevin [TeamSybase] Posted on 2009-09-24 21:05:14.0Z
From: "Sherlock, Kevin [TeamSybase]" <kevin.sherlock@teamsybase.com>
Newsgroups: sybase.public.ase.general
References: <4abbca68.6bf7.1681692777@sybase.com>
Subject: Re: How to find/delete invalid date?
Lines: 30
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3198
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4abbdf0a$1@forums-1-dub>
Date: 24 Sep 2009 14:05:14 -0700
X-Trace: forums-1-dub 1253826314 10.22.241.152 (24 Sep 2009 14:05:14 -0700)
X-Original-Trace: 24 Sep 2009 14:05:14 -0700, vip152.sybase.com
X-Authenticated-User: teamsybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28369
Article PK: 77612

Well, those aren't "invalid" dates per se. Logically to you, they are, so
you have to determine some criteria to use to identify them as "invalid".
But, from a pure "datetime" domain perspective, those are valid values from
ASE's point of view.

So, you might have to come up with something like:

delete from mytable
where mycol >= '1/1/3000'

or whatever you determine is logically "invalid". I'm assuming that the
column containing these dates is either a "datetime" or "smalldatetime" or
"date" datatype here of course. If it's a (var)char field, then you need to
convert before comparing:

delete from mytable
where convert(datetime,mycol) >= '1/1/3000'

<AJ> wrote in message news:4abbca68.6bf7.1681692777@sybase.com...
> Hello,
> I need to delete some of the rows with invalid date got
> loaded into the tables in the db during the import; not sure
> what is the date function I should use. Here are some of the
> examples of dates-
> '4/1/5523', '5/9/5598', '3/26/5568' .
>
> Thanks


AJ Posted on 2009-09-25 15:20:53.0Z
Sender: 6a37.4abbbdf8.1804289383@sybase.com
From: aj
Newsgroups: sybase.public.ase.general
Subject: Re: How to find/delete invalid date?
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4abcdfd5.1610.1681692777@sybase.com>
References: <4abbdf0a$1@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 25 Sep 2009 08:20:53 -0700
X-Trace: forums-1-dub 1253892053 10.22.241.41 (25 Sep 2009 08:20:53 -0700)
X-Original-Trace: 25 Sep 2009 08:20:53 -0700, 10.22.241.41
Lines: 33
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28371
Article PK: 77615

Thank you so much! It was perfect.

> Well, those aren't "invalid" dates per se. Logically to
> you, they are, so you have to determine some criteria to
> use to identify them as "invalid". But, from a pure
> "datetime" domain perspective, those are valid values from
> ASE's point of view.
>
> So, you might have to come up with something like:
>
> delete from mytable
> where mycol >= '1/1/3000'
>
> or whatever you determine is logically "invalid". I'm
> assuming that the column containing these dates is either
> a "datetime" or "smalldatetime" or "date" datatype here
> of course. If it's a (var)char field, then you need to
> convert before comparing:
>
> delete from mytable
> where convert(datetime,mycol) >= '1/1/3000'
>
>
> <AJ> wrote in message
> > news:4abbca68.6bf7.1681692777@sybase.com... Hello,
> > I need to delete some of the rows with invalid date got
> > loaded into the tables in the db during the import; not
> > sure what is the date function I should use. Here are
> > some of the examples of dates-
> > '4/1/5523', '5/9/5598', '3/26/5568' .
> >
> > Thanks
>
>