Date Conversion Syntax from MS Query to Excel

[baltjazz08]

I am pulling dates from MS Query into Excel, with the ultimate aim of being able to group the dates being pulled by month in order to do counts by category within that month. Doing this in Excel through a pivot table has proven extremely difficult (although we may revisit that in a subsequent question). Trying to apply the same syntax for an Excel date conversion to do an in-line transformation (as the data is being pulled) has not worked. Examples of the syntax I have treied unsuccessfully so far are provided below:

To_date((HPD_Help_Desk.Reported_Date, 'MM-YYYY’) Month_year

INT(HPD_Help_Desk.Reported_Date-DAY(HPD_Help_Desk.Reported_Date) +1)+0

So the underlying question is whether it is better to pull the data in the form that I want it originally, or to set up the data in a manner that makes the development of a pivot table possible. Any help in solving this puzzle would be greatly appreciated.

Regards,
TJ

 

[xenou]

How about just pulling the date:

SELECT HPD_Help_Desk.Reported_Date FROM HPD_Help_Desk;

You can do the grouping in the pivot table. Or you can use SQL aggregate queries to summarize your data in the SQL.

 

[baltjazz08]

xenou -

Thanks for your reply. The problem with doing the date transformation in Excel is that it's time consuming and a bit opaque. I guess what I am looking for is the syntax for pulling the raw data through MS Query in the proper (desired) format right from the start. Is that something you have some insight into?

Thanks,
TJ

 

[xenou]

xenou -

Thanks for your reply. The problem with doing the date transformation in Excel is that it's time consuming and a bit opaque. I guess what I am looking for is the syntax for pulling the raw data through MS Query in the proper (desired) format right from the start. Is that something you have some insight into?

Thanks,
TJ

As far as I know, pulling the raw data does mean pulling the date. I'm not sure what you mean by the opacity of data transformation in Excel. Usually its very straightforward (exception being US dates vs. dates in other formats when dealing with international data). Having the real date is probably the easiest way.

What kind of database are you connecting to?

 

[baltjazz08]

Alright, let me be more specific.

When you "format" a date in Excel, the presentation changes in the column, but the underlying format does not. For example, if you pull a date that is timestamped from MS Query into Excel, you can change the format to be month and year (Ex. 'Jan-10' for January of 2010), but the underlying column is still a timestamp. When you try to group by the formatted date in Excel, it doesn't do so by the 'Jan-10', it does it by the underlying timestamp, giving you multiple groups instead of just one.

Also, in order to do a true conversion from the timestamp to the desired format, you can use a formula (which I've done), but this means adding another column to the original worksheet, and then you get into the whole referential integrity issue when using the data in the original sheet in other sheets, etc. That's what I'm referring to by opacity.

What I'd like to do is pull the data from MS Query already in the 'Jan-2010' format in order to avoid this dilemna. There are other categories within each month that I would like to group by also, but I can do that already. It's the date conversion that is hanging me up. Is there an easy way to simply do an in-line conversion of the data from MS Query as it's being pulled into Excel?

Thanks,
TJ

 

[Norie]

If you group a date with a timestamp by day/date/month etc the time part should be ignored.

 

[xenou]

I have never experienced this problem you are speaking of. Generally it is best to have true dates as these can be manipulated in many different ways provide more flexibility for your data analysis.

and then you get into the whole referential integrity issue when using the data in the original sheet in other sheets, etc.

Different databases and different drivers use different date/time functions. We need to know what kind of database you are using. Normally ODBC drivers support formulas such as Day(), Month(), and Year(). So you could try:

Year([TheDate]) & "-" & Month([TheDate])

ODBC might have it's own quirks for formatting. For instance, it may prefer a plus sign (+) to an ampersand (&) for concatenation. So you could also try:

Year([TheDate]) + "-" + Month([TheDate])

Your original To_Date() function won't work with SQL Server or Access (that I am aware of). It might work with Oracle.

Pivot tables provide grouping by months and years and there's no need to worry about formatting. If you are using the data with pivot tables you can pull the true dates.