CODEWITHSUNDEEP
sql-serverdate
Umesh D
Umesh D

Reputation: 263

SQL Server : convert date to YYYMMDD?

I have a date in one of the column in SQL Server, the sample dates are:

10/02/2012
23/11/2012
13/01/2012
10/02/2012
10/02/2012

I have tried the approach to convert the dates to YYYYMMDD

DECLARE @v DATE= '1/11/2012'

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

I have another column in a same table in which i want to update the date in YYYYMMDD format ,the problem here is that the date are not proper and throws an error

DECLARE @v DATE= '23/11/2012'

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

Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.

Any help is appreciated in this, The date will come in any order either dd/mm/yyyy or mm/dd/yyyy, it should be able to convert it properly

Upvotes: 1

Views: 2181

Answers (2)

Marin Mohanadas
Marin Mohanadas

Reputation: 163

if you want to insert date in SQL server you want to follow certain formats. You can follow either yyyy-MM-dd or MM-dd-yyyy

for your question if you follow MM-dd-yyyy this format and if you using SQL server 2012 or newer you can use this and you can get the result 

DECLARE @v DATE = '11/23/2012';

SELECT FORMAT ( @v, 'yyyy/MM/dd', 'en-US' )

Refer these links FORMAT (Transact-SQL) , SQL Server date format function

Upvotes: 0

M.Topa
M.Topa

Reputation: 64

You may try using the following format to one data type to another.(dd/mm/yyyy to yyyy/mm/dd)

CONVERT(data_type(length),expression,style)

As well as update like following

UPDATE table_name SET
    destination_column_name=orig_column_name
WHERE condition_if_necessary

Upvotes: 1

Related Questions