CODEWITHSUNDEEP
sqlsql-servert-sqldate-format
learningtech
learningtech

Reputation: 33683

SQL Server CONVERT and CAST not returning the correct date format

I have a table in SQL Server with a column RGSTR_DATE that is of varchar(10) data type, and it has values like 2016-01-23, 1998-08-12, etc...

I want to select and convert those values to 20160123, 19980812, etc...

I tried running these queries:

SELECT CONVERT(DATE, CAST(RGSTR_DATE AS DATE), 112) 
FROM [project].[dbo].[my_table];

SELECT CONVERT(VARCHAR(10), RGSTR_DATE, 112) 
FROM [project].[dbo].[my_table];

But the results that came back were still 2016-01-23, 1998-08-12 etc...

What am I doing wrong?

Upvotes: 0

Views: 88

Answers (2)

John Cappelletti
John Cappelletti

Reputation: 81930

Did you try

SELECT CONVERT(VARCHAR(10),cast(RGSTR_DATE as date),112)

Upvotes: 4

John Pasquet
John Pasquet

Reputation: 1842

You're converting a varchar to a date, but all you need to do is remove the hyphens.

SELECT REPLACE(RGSTR_DATE, '-', '')
FROM [project].[dbo].[my_table]

Upvotes: 2

Related Questions