By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,326 Members | 2,726 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,326 IT Pros & Developers. It's quick & easy.

MS Query - STANDARD DATE to YEAR-MONTH

P: 4
I'm extracting some data on the hand of MS query, out of an SQL server and put it in a pivot table ...

I found a threat about this in this forum,
but I've got a problem with it ...
If I put this command :

convert(varchar(4);year(HARP_TOT.D_FACT_YM))+'-'+convert(varchar(2);month(HARP_TOT.D_FACT_YM))

The results are like this : 2005-11, 2005-12, 2005-1, 2005-2, ...
Next, I have to put the data in a pivot table
and the months are not in the right order ...
It would have to be displayed like this : 2005-01, 2005-02, ... , 2005-10, ...

Has somebody any suggestions ? :confused:
Aug 16 '06 #1
Share this Question
Share on Google+
5 Replies


P: 4
nobody can help me ?
Aug 17 '06 #2

MMcCarthy
Expert Mod 10K+
P: 14,534
Try this code.

Format([HARP_TOT.D_FACT_YM],"yyyy-mm")

This will return a String Value in the specified format. It is the only way I know of to assert the trailing 0 in the Month.



I'm extracting some data on the hand of MS query, out of an SQL server and put it in a pivot table ...

I found a threat about this in this forum,
but I've got a problem with it ...
If I put this command :

convert(varchar(4);year(HARP_TOT.D_FACT_YM))+'-'+convert(varchar(2);month(HARP_TOT.D_FACT_YM))

The results are like this : 2005-11, 2005-12, 2005-1, 2005-2, ...
Next, I have to put the data in a pivot table
and the months are not in the right order ...
It would have to be displayed like this : 2005-01, 2005-02, ... , 2005-10, ...

Has somebody any suggestions ? :confused:
Aug 17 '06 #3

P: 4
I thank you very much and will try further on monday ...
At first sight, MS Query doesn't allow me to use this expression,
but I think that I don't put the expression at the right place ...
Thank you very much for the help indeed !!
Aug 17 '06 #4

P: 4
Today, I tried the suggestion I received.
I had another error message like :
"parameters are not allowed for this query"

Thank you anyway ... but if someone has any other suggestion,
it would be a pleasure for me to try it out ;)
Aug 21 '06 #5

100+
P: 179
Does the year have to be ahead of the month? Try changing it around.

convert(varchar(2);month(HARP_TOT.D_FACT_YM))+'-'+convert(varchar(4);year(HARP_TOT.D_FACT_YM))

comteck
Aug 24 '06 #6

Post your reply

Sign in to post your reply or Sign up for a free account.