CODEWITHSUNDEEP
sql-serverdatabasedatedatetimevarchar
Kevin M
Kevin M

Reputation: 387

SQL Server date format yyyymmdd

I have a varchar column where some values are in mm/dd/yyyy format and some are in yyyymmdd.

I want to convert all mm/dd/yyyy dates into the yyyymmdd format. What is the best way to do this? Thanks

Table is Employees and column is DOB

Upvotes: 29

Views: 271589

Answers (9)

Borja
Borja

Reputation: 1

mm/dd/yyyy corresponds to U.S. standard so if you convert to date using 101 value and then to varchar using 112 for ISO date get the expected result.

declare @table table (date_value varchar(10))
insert into @table values ('03/30/2022'),('20220330')

select date_value
    --converted to varchar
    ,case 
        --mm/dd/yyyy pattern
        when patindex('[0,1][0-9]/[0-3][0-9]/[0-9][0-9][0-9][0-9]',date_value)>0 then convert(varchar(10),convert(date,date_value,101),112) 
        else date_value end date_value_new
    --converted to date
    ,case 
        when patindex('[0,1][0-9]/[0-3][0-9]/[0-9][0-9][0-9][0-9]',date_value)>0 then convert(date,date_value,101)
        else convert(date,date_value,112) end date_value_date
from @table

Upvotes: 0

Jonathan Felt
Jonathan Felt

Reputation: 81

You can do as follows:

Select Format(test.Time, 'yyyyMMdd')
From TableTest test

Upvotes: 8

Rishwanth Immaneni
Rishwanth Immaneni

Reputation: 1

SELECT TO_CHAR(created_at, 'YYYY-MM-DD') FROM table; //converts any date format to YYYY-MM-DD

Upvotes: -3

Varun
Varun

Reputation: 181

SELECT YEAR(getdate()) * 10000 + MONTH(getdate()) * 100 + DAY(getdate())

Upvotes: 0

rchacko
rchacko

Reputation: 2129

Select CONVERT(VARCHAR(8), GETDATE(), 112)

Tested in SQL Server 2012

https://www.w3schools.com/sql/func_sqlserver_convert.asp

Upvotes: 3

Daniel
Daniel

Reputation: 121

DECLARE @v DATE= '3/15/2013'

SELECT CONVERT(VARCHAR(10), @v, 112)

you can convert any date format or date time format to YYYYMMDD with no delimiters

Upvotes: 12

Ankitkumar Tandel
Ankitkumar Tandel

Reputation: 319

try this....

SELECT FORMAT(CAST(DOB AS DATE),'yyyyMMdd') FROM Employees;

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1270583

In SQL Server, you can do:

select coalesce(format(try_convert(date, col, 112), 'yyyyMMdd'), col)

This attempts the conversion, keeping the previous value if available.

Note: I hope you learned a lesson about storing dates as dates and not strings.

Upvotes: 1

John Cappelletti
John Cappelletti

Reputation: 81990

Assuming your "date" column is not actually a date.

Select convert(varchar(8),cast('12/24/2016' as date),112)

or 

Select format(cast('12/24/2016' as date),'yyyyMMdd')

Returns

20161224

Upvotes: 55

Related Questions