CODEWITHSUNDEEP
sql-serversql-server-2005varcharsmalldatetime
milo2010
milo2010

Reputation: 91

Convert varchar column to smalldatetime

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?

Upvotes: 3

Views: 14142

Answers (3)

Tomao44
Tomao44

Reputation: 41

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

Upvotes: 0

Joe Stefanelli
Joe Stefanelli

Reputation: 135878

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)

Upvotes: 2

Kamyar
Kamyar

Reputation: 18797

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.

Upvotes: 0

Related Questions