CODEWITHSUNDEEP
sql-serverdatetimetype-conversion
Yilun Zhang
Yilun Zhang

Reputation: 9008

SQL Server date conversion from string value

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.

Upvotes: 0

Views: 564

Answers (3)

Robert Sievers
Robert Sievers

Reputation: 1355

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

Upvotes: 0

Cetin Basoz
Cetin Basoz

Reputation: 23797

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;

Upvotes: 2

Thom A
Thom A

Reputation: 95544

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;

Upvotes: 0

Related Questions