SQL Server date conversion from string value

Question

In my table, I have a string-represented date column in the following format: {^2013/05/29}.

How do I convert this to standard datetime format in SQL Server? This contains string characters that are not part of what a datetime string usually has.

With [substring](https://learn.microsoft.com/en-us/sql/t-sql/functions/substring-transact-sql?view=sql-server-2017) and then a cast to `Date` or `DateTime` or `DateTime2` — Igor, Aug 21 '18 at 21:06
Possible duplicate of [Sql Server string to date conversion](https://stackoverflow.com/questions/207190/sql-server-string-to-date-conversion) — Igor, Aug 21 '18 at 21:07
I found the problem. It is right here. "I have a string-represented date column". https://sqlblog.org/2009/10/12/bad-habits-to-kick-choosing-the-wrong-data-type — Sean Lange, Aug 21 '18 at 21:27

score 2 · Accepted Answer · edited Aug 22 '18 at 05:12

2

That format is recognizable as a strict format by VFP only. Is that stored in SQL Server as text? If so:

Select cast(substring(myColumn, 3, 10) as date) as myDate from myTable;

would do the conversion.

If you mean it is stored like that in a VFP table and you want to convert a date then:

select ctod(myColumn) as myDate from myTable;

edited Aug 22 '18 at 05:12

marc_s

732,580
175
1,330
1,459

answered Aug 21 '18 at 21:10

Cetin Basoz

22,495
3
31
39

Ah... VFP, my first true love. – John Cappelletti Aug 21 '18 at 21:11
1

@JohnCappelletti, everyone who have seen and used VFP loved it:) Unfortunately one day it was acquired by Microsoft. – Cetin Basoz Aug 21 '18 at 21:13

Robert Sievers · Answer 2 · 2018-08-22T13:54:50.427

0

SELECT CAST(REPLACE('^2018/05/29','^','') AS DATETIME2)

edited Aug 22 '18 at 13:54

answered Aug 21 '18 at 21:09

Robert Sievers

1,277
10
15

You also need to remove { and }. – Cetin Basoz Aug 21 '18 at 21:11

score 0 · Answer 3 · answered Aug 21 '18 at 21:10

If the data is always in the format {^yyyy/MM/dd} then you could use:

CONVERT(date,REPLACE(SUBSTRING(YourDateColumn,3,10),'/',''))

Ideally, however, you should be fixing your column to be the correct datatype:

CREATE TABLE Test (DateColumn varchar(13));
INSERT INTO Test VALUES ('{^2013/05/29}');
GO

SELECT *
FROM Test;

UPDATE Test
SET DateColumn = CONVERT(date,REPLACE(SUBSTRING(DateColumn,3,10),'/',''));

SELECT *
FROM Test;

ALTER TABLE test ALTER COLUMN DateColumn date;

SELECT *
FROM Test;
GO

DROP TABLE Test;

SQL Server date conversion from string value

3 Answers3