All geek questions in one place

Convert varchar column to smalldatetime

I have a table with 800+ records. In this table, I have a column called "Data" of the varchar (10) data type that contains dates in the format dd.MM.yyyy. I want to convert it to smalldatetime.

I tried to convert it using Enterprise Management Studio Express, but I get this error:

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

How to convert it?

+3
source share
3 answers

I think you need to manipulate the lines a bit to make this work, as I think SQL expects "MM.dd.yyyy". So, first update the table to first turn the month and day, and then go.

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

:
SELECT ID, CAST(VarcharCol As SmallDateTime) as DateTimeCol From Test1
varcharcol smalldatetime varcharcol .

0

If you do not want to implicitly convert to smalldatetime, you should use CONVERT and the argument style .
The format dd.MM.yyyy corresponds to style 104.
For example:

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

Result
------
2018-12-31 00:00:00
0
source

Source: https://habr.com/ru/post/1767547/

More articles:


All Articles