Dates are mixed for two months

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Dates are mixed for two months

Author

Topic

lols
Posting Yak Master

174 Posts

Posted - 2009-04-30 : 09:10:43

I am running the query:

SELECT DISTINCT CONVERT(VARCHAR(12), MyDate, 106) as MyDate FROM MyTable WHERE MyDate >= DATEADD(month,-1,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)) and MyDate < dateadd(month,datediff(month,-1,getdate()),0) ORDER BY MyDate Desc

I get the result as:

31 Mar 2009
30 Mar 2009
30 Apr 2009
29 Mar 2009
29 Apr 2009
28 Mar 2009
28 Apr 2009
27 Mar 2009
27 Apr 2009
26 Mar 2009
26 Apr 2009
25 Mar 2009
25 Apr 2009
24 Mar 2009
24 Apr 2009
23 Mar 2009
23 Apr 2009
22 Mar 2009
22 Apr 2009
21 Mar 2009
21 Apr 2009
20 Mar 2009
20 Apr 2009
19 Mar 2009
19 Apr 2009
18 Mar 2009
18 Apr 2009
17 Mar 2009
17 Apr 2009
16 Mar 2009
16 Apr 2009
15 Mar 2009
15 Apr 2009
14 Mar 2009
14 Apr 2009
13 Mar 2009
13 Apr 2009
12 Mar 2009
12 Apr 2009
11 Mar 2009
11 Apr 2009
10 Mar 2009
10 Apr 2009
09 Mar 2009
09 Apr 2009
08 Mar 2009
08 Apr 2009
07 Mar 2009
07 Apr 2009
06 Mar 2009
06 Apr 2009
05 Mar 2009
05 Apr 2009
04 Mar 2009
04 Apr 2009
03 Mar 2009
03 Apr 2009
02 Mar 2009
02 Apr 2009
01 Mar 2009
01 Apr 2009

However I want the result where first dates of April and then dates of March should be listed rather than being mixed. How to do it?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-30 : 09:14:21

SELECT DISTINCT CONVERT(VARCHAR(12), MyDate, 106) as MyDate FROM MyTable WHERE MyDate >= DATEADD(month,-1,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)) and MyDate < dateadd(month,datediff(month,-1,getdate()),0) ORDER BY cast(MyDate as datetime) Desc

Madhivanan

Failing to plan is Planning to fail

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-04-30 : 09:17:06

datatype for MyDate is varchar ?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-30 : 09:24:58

quote:
Originally posted by sakets_2000

datatype for MyDate is varchar ?

No. Op convert it in the SELECT statement as varchar

Madhivanan

Failing to plan is Planning to fail

lols
Posting Yak Master

174 Posts

Posted - 2009-04-30 : 09:30:01

Thanks. When I run your query, I get error
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-30 : 09:38:31

SELECT * FROM
(
SELECT DISTINCT CONVERT(VARCHAR(12), MyDate, 106) as MyDate FROM MyTable WHERE MyDate >= DATEADD(month,-1,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)) and MyDate < dateadd(month,datediff(month,-1,getdate()),0)
) AS t
ORDER BY CAST(MyDate as datetime) Desc

Madhivanan

Failing to plan is Planning to fail

lols
Posting Yak Master

174 Posts

Posted - 2009-04-30 : 11:16:07

Thanks so much. I get the desired output but now it is taking very long time to get records. Can I generate the desired output without using MyTable. I want to list down last 2 months dates in a similar format

30 Apr 2009
29 Apr 2009 ...and so on

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-05-04 : 01:44:54

select dateadd(month,datediff(month,0,getdate())-2,0)+number from master..spt_values
where type='p' and dateadd(month,datediff(month,0,getdate())-2,0)+number<dateadd(month,datediff(month,0,getdate()),0)
order by 1

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources