
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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!
- Tags:
- qlikview_scripting
Accepted Solutions


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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],

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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],


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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],

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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!
