-
1. Re: Convert MMM and YYYY columns to one date column
Michael Hesser Oct 26, 2016 8:16 AM (in response to Jaakko Salminen)So is the SQL preventing you from using DateParse? Can you combine it in an equation?
DateMaker
Dateparse("dd/MMMM/yyyy","01/"+[Period]+"/"+str([Year]))
Make sure you are displaying your date using the following attributes:
- Exact Date
- Discrete
- change the DateMaker format to 03/14/2001
Let me know if this works! --Michael
-
2. Re: Convert MMM and YYYY columns to one date column
Joe OppeltOct 26, 2016 8:20 AM (in response to Michael Hesser)
Check out the MAKEDATE function.
MAKEDATE(year, month, day)
So you would have to convert your string month field to a number, but it would look like this:
MAKEDATE([Year], [Numeric month], 1)
And if your YEAR field is coming in as a string, then just do INT([Year]) in the MAKEDATE function.
-
3. Re: Convert MMM and YYYY columns to one date column
Michael Hesser Oct 26, 2016 8:36 AM (in response to Joe Oppelt)Hi Joe,
I think the data may be coming in with MONTH as a string ("Jan"), which means to tease the numeric month out of it, you'd still need to use DATEPARSE to transform it into a date... and then MAKEDATE it.
Maybe there's a step I'm missing above?
-
4. Re: Convert MMM and YYYY columns to one date column
Joe OppeltOct 26, 2016 8:39 AM (in response to Michael Hesser)
Month number:
CASE [Month]
when "Jan" then 1
when "Feb" then 2
.
.
.
END
-
5. Re: Convert MMM and YYYY columns to one date column
Jaakko Salminen Oct 26, 2016 11:26 PM (in response to Joe Oppelt)Thank you! It worked just the way you said.
I was working with a live connection which disabled DATEPARSE and MAKEDATE functions. I had to go with the extract to enable them. Probably would have worked with DATEPARSE now too.
-
6. Re: Convert MMM and YYYY columns to one date column
Joe OppeltOct 27, 2016 7:26 AM (in response to Jaakko Salminen)
If you can't do MAKEDATE on the live data you might be able to do:
DATE( "01/" + STR([Month numeric variable] + "/" + STR([Numeric year variable]) )
And, of course, if your month and year variables are already character variables, you don't have to STR() them.