Query Assistance

[cstimart]

I have the following Query

SELECT Format([Adate], 'mmm, yyyy') as [A]
FROM [DETAIL]
WHERE [SomeThing]='Blah'
GROUP BY [Adate]

However, the results come back as...

Jan, 2008
Jan, 2008
Jan, 2008
Feb, 2008
etc...

I am looking to get
Jan, 2008
Feb, 2008
Mar, 2008
etc...

 

[xenou]

Hi,
Group by the same expression as in your SELECT:
GROUP BY Format([Adate], 'mmm, yyyy')

That should get you the right result I think,
ξ

 

[cstimart]

It does group it, but then I lose the Chronological order.

 

[xenou]

That is correct, these are text values and your date format is not suitable for sorting as text. If possible, prefer to use real dates - you can still display dates as mmm yyyy. For instance:

SELECT DateSerial(Year([Adate]),Month([Adate])+1,0) as [A]
FROM [DETAIL]
WHERE [SomeThing]='Blah'
GROUP BY DateSerial(Year([Adate]),Month([Adate])+1,0)
ORDER BY DateSerial(Year([Adate]),Month([Adate])+1,0)

I guess it depends on what you are really doing here.

 

[cstimart]

That is correct, these are text values and your date format is not suitable for sorting as text. If possible, prefer to use real dates - you can still display dates as mmm yyyy. For instance:

SELECT DateSerial(Year([Adate]),Month([Adate])+1,0) as [A]
FROM [DETAIL]
WHERE [SomeThing]='Blah'
GROUP BY DateSerial(Year([Adate]),Month([Adate])+1,0)
ORDER BY DateSerial(Year([Adate]),Month([Adate])+1,0)

I guess it depends on what you are really doing here.

Thanks, I will give this a try on Monday.