date in varchar

[camy123]

hi guys.... I have a script to write which is converting date which is stored as varchar(yeh i know)
for example
the data is currently in the format
of 04/2004 and needs to be 2004/04 the columns all consists of data such
null
Y???
dhdhdh
etc
I have an idea of what to do but just wana see how you lot would go about it.
thanks guys

 

[vongrunt]

the data is currently in the format of 04/2004 and needs to be 2004/04

This is relevant only to SET DATEFORMAT during conversions.

There are four "types" of data you may encounter:

- empty values. Either NULL, "", "EMPTY" or whatever. In final results these should be replaced with default value of choice.
- crap. You can filter it with WHERE ISDATE(myColumn) = 0. Correct obvious typos if you want, fill incomplete dates (04/2004 -> 2004-04-01) if appropriate, forget the rest.
- valid dates out of range (Feb 30 2004, Aug 01 1005 etc). This is the toughest part - ISDATE() will return 1 but conversion will still crash
- valid dates. Just use CONVERT() and voila.

Basically this is tedious try-and-handle-exceptions procedure.

 

[donutman]

With just month and year, it's fairly easy to catch out or range dates with a sort on right(date,2) and another sort on left(date,4). I don't understand how you can be "allowed" to change to yyyymm from mmyyyy and not be "allowed" to change to datetime format.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]