Convert varchar column to smalldatetime

Question

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?

score 2 · Accepted Answer · answered Oct 01 '10 at 14:49

2

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)

answered Oct 01 '10 at 14:49

Joe Stefanelli

132,803
19
237
235

WELL NOTED OKAY – Bilgin Kılıç Jan 11 '19 at 08:35

score 0 · Answer 2 · answered Oct 01 '10 at 14:46

0

You can use:
SELECT ID, CAST(VarcharCol As SmallDateTime) as DateTimeCol From Test1
This will return a table with varcharcol values as smalldatetime Then update the content of varcharcol with the new values.

answered Oct 01 '10 at 14:46

Kamyar

18,639
9
97
171

score 0 · Answer 3 · answered Dec 14 '18 at 10:19

If you don't want implicit conversion to smalldatetime, you should use CONVERT and the argument style.
dd.MM.yyyy format correspond to style 104.
For example :

SELECT CONVERT(smalldatetime, '31.12.2018', 104) AS "Result"

Result
------
2018-12-31 00:00:00

Convert varchar column to smalldatetime

3 Answers3