Converting Varchar Date back to smalldatetime

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Converting Varchar Date back to smalldatetime

Author

Topic

Rika1976
Starting Member

5 Posts

Posted - 2004-10-11 : 09:53:15

Okay, so let me set this up. I have a job that runs that includes the current date as a step. For awhile I had used a DTS to pull all the information into an archive table, which inserted the date into a varchar field as smalldatetime format. Recently, thinking that if I used a T-SQL statement to pull the data if might work faster, I changed the step. Well, it not only did not work faster (much slower), but it pulled the data into the varchar field as a varchar formated date (oct 7 etc...). I have now changed back to the DTS which is once again pulling in the dates into the field in a smalldatetime format, but no matter what I do I cannot seem to change those items with the varchar formated date back into a smalldatetime format.

Here is what I've tried.

I've tried to do a set statement, setting the date field where the data is like Oct% to = the cast(field as smalldatetime). It said it updated, but when I go back they are still in the varchar format.

Then I tried to withdraw the bad date info into another table and update from that table. That also said it updated, but yet again is still in the varchar format.

I'm afraid to change to field type to smalldatetime, because I'm not sure what it would do to those ones with the varchar format. Then I would have a whole days data incorrect.

Any ideas would be helpful.
Thanks
Erika

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-11 : 10:49:04

as i get it you column datatype is varchar. so you can only store it as varchar.
add a datetime column and do an update

update myTable
set datetimeColumn = cast(varcharColumn, datetime)

or am i missing the point??

Go with the flow & have fun! Else fight the flow

Rika1976
Starting Member

5 Posts

Posted - 2004-10-11 : 11:26:57

Yes the field is in varchar format. But here is a big part of the issue. This table stores about 2 million records. All of the records except for 180,000 show the date in this format (2004-07-01 15:58:43) whereas the 180,000 are in this format(Oct 7 2004 1:22PM)

I'm wondering if I changed that varchar field to smalldatetime, it shouldn't affect most of them because they are already in that fomat although it is a varchar field, but I'm not sure if it would convert those 180000 correctly.

I should have never listened to another person on this job! Thats the reason I'm in this mess.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-11 : 11:45:28

yes it will convert it ok:
see this:


declare @table table (col1 datetime)--varchar(50))
insert into @table
select '2004-07-22 15:58:43' union all
select '2004-06-22 15:58:43' union all
select 'Oct 7 2004 1:22PM' union all
select 'Oct 8 2004 1:22PM' 

select *
from @table

change col1 to varchar or datetime. put your data int and see if it works.

Go with the flow & have fun! Else fight the flow

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources