Convert from VARCHAR to DATETIME

Question

I need to convert all values in a table and I need that change to be permanent so the table has dates and they are formatted like dd.mm.yyy.

I need to change them to DATETIME Format which is yyyy-mm-dd.

CAST(FieldName as datetime) You really need to give more information, some sample data and what you've already tried and what has and hasn't worked — Rich Benner, Jun 22 '16 at 09:40

score 0 · Answer 1 · answered Jun 22 '16 at 09:42

0

Use the CONVERT function with style 20.

SELECT CONVERT(VARCHAR,yourdatefield,20)

Outputs:

yyyy-mm-dd hh:mi:ss

answered Jun 22 '16 at 09:42

Matt

14,906
27
99
149

ɐlǝx · Answer 2 · 2016-06-22T10:12:38.953

0

SELECT CONVERT(NVARCHAR(10),CONVERT(DATETIME,yourData,104),20)

edited Jun 22 '16 at 10:12

answered Jun 22 '16 at 09:44

ɐlǝx

1,384
2
17
22

score 0 · Answer 3 · answered Jun 22 '16 at 09:58

If the format is "dd.mm.yyyy". Follow the steps.

Add New Column

    ALTER TABLE TABLE_NAME ADD COLUMN_NAME DATETIME/DATE;

Update the column with date value from old column

    UPDATE TABLE_NAME SET COLUMN_NAME=CONVERT(DATETIME,V_DATE_COL,104);

Remove Old Column

    ALTER TABLE TABLE_NAME DROP COLUMN V_DATE_COL

Rename New Column

    EXEC sp_RENAME 'TABLE_NAME.COLUMN_NAME' , 'NewColumnName', 'COLUMN'

score 0 · Accepted Answer · edited May 23 '17 at 12:08

If your dates are stored with the appropriate data type (date, datetime or datetime2), then they are stored without any display format.

If that is the case you need to convert them to char(10) when selecting the data:

SELECT CONVERT(char(10), DateTimeColumn, 120)

If they are stored as a string type, you should refactor your database and store them properly. If that's impossible for some reason, you can convert twice. one from string to date, and the other one back to string:

SELECT CONVERT(char(10), CONVERT(date, DateTimeStringColumn, 104), 120)

Convert from VARCHAR to DATETIME

4 Answers4