Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

MS Access load datetime field as YYYYMM

Hi Experts,

This is probably a silly question, but what is the syntax to load an MS Access DateTime as YYYYMM?  I tried this, but it's not working

Date(Date#(VisitDate, 'YYYYMMDD'), 'YYYYMM') as 'VisitYear Month',

Thanks in advance!

1 Solution

Accepted Solutions
jpapador
Partner - Specialist
Partner - Specialist

If it is already coming in as a data type "Date" you won't need the Date#.

Try:

Date(MonthStart(VisitDate), 'YYYYMM') as [Visit Year Month],

View solution in original post

5 Replies
sunny_talwar

What format does VisitDate come in? If it does come in as YYYYMMDD, then what you are using looks right except for the single quote around the field name. I would either use double quotes or square brackets

Date(Date#(VisitDate, 'YYYYMMDD'), 'YYYYMM') as [VisitYear Month],


Update: I guess it doesn't matter, single quotes seems to work as well.

Not applicable
Author

Thanks for the reply!  OK, I changed the line to :

Date(Date#(VisitDate, 'MM/DD/YYYY'), 'YYYYMM') as ['Visit Year Month]',

The format in Access is MM/DD/YYYY.  I'm still not getting any results.  Is there something else that could be the problem?

sunny_talwar

I think single quotes seems to work, but have square bracket or single quotes (not both). Try this:

Date(Date#(VisitDate, 'MM/DD/YYYY'), 'YYYYMM') as [Visit Year Month],


jpapador
Partner - Specialist
Partner - Specialist

If it is already coming in as a data type "Date" you won't need the Date#.

Try:

Date(MonthStart(VisitDate), 'YYYYMM') as [Visit Year Month],

Not applicable
Author

This worked!  I think the fact that the field is already DateTime must somehow play into the syntax choice.  (I'm a total newbie, so take that for what it's worth!)

Thank you both very very much!