Changing data type

[arbytech]

Hi everyone! I have a question about changing data that already exists in a SQL db.

I have a table with a field called pdate. The type for pdate is varchar(15). However it searches incorrectly since it's using dates as varchar (yes I had a previous question for that).

My question is can I take the data for pdate which is setup like this: mm-dd-yyyy (ex. 03-29-2005) and change it to yyyy-mm-dd (ex. 2005-03-29)? After that I could change the type to date instead of varchar(15) and edit the code of the VB program that queries the db.

Thanks for any help or comments!

RB

 

[TJRTech]

I assume you are talking about changing the format of the string from '03-29-2005' to '2005-03-29' for sorting purposes?

If so, then I recommend you continue further down your path of thought and change the column to a datetime and be done with it.

Look at the SQL books online, specifically the "ALTER TABLE" commands or do the column add through SQL Enterprise Manager.

I would do the following:

- rename the old pDate varchar column to tmpDate

- Add the new pDate column as a datetime data type

- Run an update to get the old values from tmpDate and convert them to datetime and set the new pDate column:

UPDATE <tablename>
SET pDate=convert(datetime, tmpDate)

- Remove the old tmpDate column

- Update your client VB code accordingly


Once done, you can sort on the pDate column in Asc, Desc or whatever and it will sort as you expect.

Never put dates or times in anything but a datetime column.

TJR