CODEWITHSUNDEEP
sqlsql-server
user11299833
user11299833

Reputation: 25

Convert YYYYMM string to date YYYY-MM-DD with day 1

I have this column Startmonth from the tables, which is dates liking this: YYYYMM, so 201801, 201707 etc.

I want this column in my view that I'm creating, but converted to a date, like YYYY-MM-DD with the day as the first of the month, so **201801 > 2018-01-01 and 201707 > 2017-07-01. **

I keep getting the error:

Conversion failed when converting date and/or time from character string.

Can someone help me out?

I tried this:

select convert (DATE, concat (s.StartMonth,'01'), 102) as SubscriptionStart

from incident i

left join imcustomerid cid on i.accountid = cid.accountid
left join billing_subscription s on cid.imcustomerid = s.idcustomer
left join billing_subscriptiontype st on s.idsubscriptiontype = st.id

Upvotes: 0

Views: 8186

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269693

I think the clearest method is datefromparts():

select datefromparts(year(s.StartMonth),
                     month(s.StartMonth),
                     1) as SubscriptionStart

The more traditional way is just to subtract a number of days:

select dateadd(day,
               (1 - day(s.StartMonth)),
               s.StartMonth
              ) as SubscriptionStart

I do not recommend converting the date to a string for this purpose. It is definitely not necessary and dates are different from strings.

Upvotes: 0

Joakim Danielson
Joakim Danielson

Reputation: 51892

The problem is that you are doing a left join on the table with the string which means it can sometimes be null if there is no matching row for an incident row and then the conversion fails

Here is one way to solve this

SELECT CASE WHEN s.StartMonth IS NOT NULL THEN convert(DATE, concat (s.StartMonth,'01'), 112)
       ELSE null -- Or return some default value, otherwise this row can be removed
       END  as SubscriptionStart
FROM incident i
LEFT JOIN imcustomerid cid ON i.accountid = cid.accountid
LEFT JOIN billing_subscription s ON cid.imcustomerid = s.idcustomer
LEFT JOIN billing_subscriptiontype st ON s.idsubscriptiontype = st.id

another is to use TRY_CONVERT instead of the CASE as noted in another answer, it depends what you want to return when null.

Upvotes: 1

Sergey Menshov
Sergey Menshov

Reputation: 3906

Try to use

select convert (DATE, concat (s.StartMonth,'01'), 112) as SubscriptionStart

or

select cast(concat (s.StartMonth,'01') as date) as SubscriptionStart

CAST and CONVERT (Transact-SQL)

I think better to use TRY_CONVERT here as said @Larnu:

select try_convert(date, s.StartMonth+'01', 112) as SubscriptionStart

and + instead of CONCAT to convert NULL values correctly.

Upvotes: 2

Related Questions