Convert varchar column to smalldatetime

sql-server sql-server-2005 varchar smalldatetime
milo2010 picture milo2010 · Oct 1, 2010 · Viewed 10.1k times · Source

I have a table with 800+ records. In this table I have a column named 'Data' of varchar(10) datatype which contains dates in dd.MM.yyyy format.I want to convert it to smalldatetime.

I've tried converting it using Enterprise Management Studio Express, but I receive this error:

The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value.

How can I convert it?

Answer

Joe Stefanelli picture Joe Stefanelli · Oct 1, 2010

I think you'll need to do a little string manipulation to get this to work as I think SQL is expecting 'MM.dd.yyyy'. So, update your table to flip-flop the month and day first, then the conversion should go through.

update YourTable
    set Data = SUBSTRING(Data,4,3) + LEFT(Data,3) + RIGHT(Data,4)

Related questions

What are the main performance differences between varchar and nvarchar SQL Server data types?

I'm working on a database for a small web app at my school using SQL Server 2005. I see a couple of schools of thought on the issue of varchar vs nvarchar: Use varchar unless you deal with a lot of …

Read More
Add a column with a default value to an existing table in SQL Server

How can I add a column with a default value to an existing table in SQL Server 2000 / SQL Server 2005?

Read More
Insert results of a stored procedure into a temporary table

How do I do a SELECT * INTO [temp table] FROM [stored procedure]? Not FROM [Table] and without defining [temp table]? Select all data from BusinessLine into tmpBusLine works fine. select * into tmpBusLine from BusinessLine I am trying the same, but …

Read More