Convert finance period into Date/Time

Question

I am trying to convert finance period into datetime e.g. 201801 = 01/04/2018

e.g.;
201801 = 01/04/2018
201802 = 01/05/2018
201803 = 01/06/2018
201804 = 01/07/2018
201805 = 01/08/2018
201806 = 01/09/2018
201807 = 01/10/2018
201808 = 01/11/2018
201809 = 01/12/2018
201810 = 01/01/2019
201811 = 01/02/2019
201812 = 01/03/2019

Formula in tableau that works 
DATE(left(str([Period]),4) + "-" + right(str([Period]),2) + "-1")

I need it in SQL. tried using convert and Cast but keep getting syntax errors.

CONVERT(date,(left(GL_master.period,4)+ '-' +(right(GL_master.period,2)+ 
'-1')),

As per summary.

You are missing a closing parenthesis `)` somewhere in that statement — tshimkus, Feb 11 '19 at 20:03
What errors are you getting specifically? Which DBMS are you using? — SovietFrontier, Feb 11 '19 at 20:06
using SSMS but looks like I have to use a combination of convert plus dateadd? — Reg Chand, Feb 11 '19 at 20:09
something like this is working but I need to use left period,4 and choose year only then use right period,2 to choose month. — Reg Chand, Feb 11 '19 at 20:17
This is giving me the right year now, just need to get month and concatenate them together; convert(datetime, DATEADD(year,0,(Left(GL_master.period,4))),112), — Reg Chand, Feb 11 '19 at 20:19
Side note: you **should** have what's known as a `Calendar Table` (a list of all dates, and a bunch of information about them), which would make this a pretty trivial `JOIN`; they're one of the most useful analysis tables possible. This is more important when you're using a 52/53 week calendar, given how leap-weeks work (instead of what you appear to be using here, with start-of-year shifted). — Clockwork-Muse, Feb 11 '19 at 20:56

Caius Jard · Accepted Answer · 2019-02-11T20:55:08.407

Where I put '201801' you should put your column name - [Period] ?

select DATEADD(month, 3, CONVERT(date, CONCAT('201801', '01'), 112)) 
                                              ^^^^^^^^
                                              [Period] ?

We pad your "date" of 201801 out with another '01' to make it like a date format 112, convert it to a date and add 3 months after it's turned into a date.

 201801 -> 20180101 -> 01/04/2018

This method works for rolling over years too

 201812 -> 20181201 -> 01/03/2019

Convert finance period into Date/Time

1 Answers1