Convert VarChar>smallDateTime BUT some data is DD/MM/YY and MM/DD/YY !

[hpadwal]

Hello all,

I have a large database of user which used to use an amercian based server which stored all birth days as 03/21/1998(MM/DD/YYYY) in a VarChar column...

...Later we switched our server and the dates where then been stored as 26/12/1998 (DD/MM/YYYY)...

As you can guess this is a bit annoying. I am unsure how to tackle this and have broken down the prolem below

1. need to convert MM/DD/YYYY to DD/MM/YYYY, but the database has a mix of Both....so i dont want the script to convert correct data.

2. i need to then change VarChar to smallDateTime format


Can you help?

Thanks you

_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/

- Doh

http://www.my-portfolio.me.uk

 

[vongrunt]

> Hello all,
> I have a large database of user which used to use an amercian based server which stored all birth days as 03/21/1998(MM/DD/YYYY) in a VarChar column...

Varchardates... don't ya ppl ever learn? .

You can safely determine some dates - where either "month" or "day" is greater than 12. For such dates ISDATE() with one SET DATEFORMAT in action will return 1 and 0 with another.

For dates like '04/03/2006' - this becomes mission impossible. Or guessing of some kind. For example:

- if table has timestamp column... find min. value where change in dateformat became active. From that point on you can assume all dates are DMY.

- if table has identity column... ditto. That would safely work only for INSERTed rows though - UPDATEd rows with "earlier" identity values won't be detected that way.

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[hpadwal]

Would sql script shall i execute to convert some of the dates that can be determined?

Im still getting to grips sql server capabilities.. maybe something like

isdate(user_DOB(MM))>12 then

Swap the DD and MM ??? but the column is a varchar at the mo?

_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/

- Doh

http://www.my-portfolio.me.uk

 

[hpadwal]

I have 10500 records and about 2000 will hhave US style dates!

_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/

- Doh

http://www.my-portfolio.me.uk

 

[fredericofonseca]

option 1 - go back to the backup you had before you converted, and restore that table into a temporary table.


Then join both new and old table and change dates accordingly.

Option 2 - if your table, or a directly related table, has other fields that can identify whether a particular record was created on the old system use this instead to identify the records that need changing.

Otherwise your 2000 rows may be, and probably are, incorrect.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com