June 23, 2004 at 2:23 pm
I have a varchar field of date info in two different formats, 1-1-2000 and 1/1/2000. This data needs to go into a new column of type small date time. What is the best way to do this. I cannot just change the data type of the column directly as I get a error:
- Unable to modify table.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value.
[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been terminated.
June 23, 2004 at 3:13 pm
create table #test (#char varchar(10), #date smalldatetime)
insert #test (#char)
values ('1/1/2000')
insert #test (#char)
values ('2/2/2000')
insert #test (#char)
values ('3/3/2000')
update #test
set #date = cast(#char as datetime)
select * from #test
drop table #test
Something else to consider would be to create a new table with the smalldatetime field, and export the data into the new table, then delete the old table (be sure to hang on to any permissions, constraints, etc), and rename the new table.
OR, create the new table, export the data, truncate the old table, change the format, then import the data back into the old table.
Steve
June 23, 2004 at 4:13 pm
Converting of varchar to datetime really depends on your local settings. If SQL server can convert 6/23/2004 it cannot recognize 23/6/2004. There are many other tricky faults with dates conversion. And you not always can get which part of OS-EM-Server brings you this error.
The best way is to rearrange your varchar data to format YYYY-MM-DD or YYYYMMDD (the columns data type is still varchar) and than convert it to smalldatetime.
And if you will create right universal UDF for rearranging varchar dates to YYYYMMDD format it will save a lot of your time in future.
Sergiy.
_____________
Code for TallyGenerator
June 23, 2004 at 4:40 pm
You should just be able to use CAST e.g.
INSERT INTO TABLE (NEW_FIELD)
SELECT NEW_DATE = CAST(OLD_DATE AS SMALLDATETIME(3))
FROM TABLE
If you need more complicated logic then just use the CASE statement and the SUBSTRING function and concatenate the string you need then cast that.
For more info on CAST see BOL the 3 in brackets will change depending on the date format you want. THIS IS DD/MM/YY 103 IS DD/MM/YYYY
Dave
June 24, 2004 at 6:30 am
At the beginning of your script just tell SQL Server how it is to interpret the date data.
How do you do that? Easy. Lets say your format is day, month, then year. You would use:
SET DATEFORMAT dmy
It doesn't matter if the date portions are separated by dashes (-) or slants (/).
Likewise if your date information is month, day, year you would use:
SET DATEFORMAT mdy
Then follow that by your script.
-SQLBill
June 24, 2004 at 6:33 am
By the way, SET DATEFORMAT does not change the way the date is saved in SQL Server, nor the way it is displayed.
All it does is say: "SQL Server, when I pass you date information, this will be the format."
-SQLBill
June 24, 2004 at 7:57 am
Thanks all. With the leads that all the replys gave me I got it to work exacly as I need.
Jeff
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply