CODEWITHSUNDEEP
sqlsql-servert-sql
Raul Escalona
Raul Escalona

Reputation: 117

How to convert varchar to date format

I want to convert my field (dateVar = '08/2018') to a real date field. Actually i have only months and years, so i need to add just 01 like a day. My final result should be 2018-08-01.

CREATE TABLE dateTable (
    dateVar varchar(10)
);

insert into dateTable (dateVar)
values('08/2018'),('01/2015');

i used this to convert to date but doesnt work

SELECT CONVERT(date, dateVar, 103) FROM dateTable

Upvotes: 0

Views: 107

Answers (3)

KeithL
KeithL

Reputation: 5594

The trick is just to manipulate the string into YYYYMMDD format and cast it.

cast(right(dateVar,4) + left(dateVar,2) + '01' as date)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269463

You should use standard formats for dates:

insert into dateTable (dateVar)
    values('2018-08-01'), ('2015-01-01');

The hyphens is ISO standard and standard in most databases. It almost always works correctly in SQL Server, but the real SQL Server standard drops the hyphens -- '20180801' and '20150101'.

The table should then be declared to have a date for the column:

CREATE TABLE dateTable (
    dateVar date
);

If you need to see this in a particular format, use a computed variable:

CREATE TABLE dateTable (
    dateVar date,
    date_mmyyyy as (right(convert(varchar(10), dateVar, 103), 7))
);

Then date_mmyyyy shows the value as you would like to see it.

Upvotes: 2

Lukasz Szozda
Lukasz Szozda

Reputation: 175566

You could concatenate missing 01:

SELECT CONVERT(date, '01/'+dateVar, 103)
FROM tab

DBFiddle Demo

To avoid problems with incorrect user input you could use TRY_CONVERT. In case of conversion error it will return NULL instead.

Upvotes: 3

Related Questions