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.
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;
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;
SELECT CAST(REPLACE('^2018/05/29','^','') AS DATETIME2)
DateorDateTimeorDateTime2