So is the SQL preventing you from using DateParse? Can you combine it in an equation?
Make sure you are displaying your date using the following attributes:
- Exact Date
- change the DateMaker format to 03/14/2001
Let me know if this works! --Michael
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.
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?
when "Jan" then 1
when "Feb" then 2
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.
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.