Date conversion help - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

Date conversion help

Author

Topic

direrayne
Starting Member

30 Posts

Posted - 2011-06-07 : 20:58:56

I have the query below which will be used in ssrs.
the problem i have is that i need to query data using the month in the where clause only. not using "transaction_date" between in the query. I need some help on how to do this.

SELECT  --dbo.SalesRep.Number,
        dbo.SalesReportRMS.SalesRep ,
        dbo.SalesReportRMS.LogicalStoreName AS [Home Store] ,
        dbo.SalesRep.[Target] ,
        SUM(dbo.SalesReportRMS.FullPrice) AS [Home Store Sales] ,
        SUM(dbo.SalesReportRMS.Quantity) AS [Quantity]
FROM    dbo.SalesReportRMS
        LEFT OUTER JOIN dbo.SalesRep ON dbo.SalesReportRMS.SalesRepID = dbo.SalesRep.Number
WHERE   ( NOT ( dbo.SalesRep.[Target] = '0' )
        )
        AND ( NOT ClassID LIKE 'MISS-%'
            )
        AND ( dbo.SalesRep.ShTarget = 'y' )
        AND dbo.SalesReportRMS.StoreID = dbo.SalesRep.HomeStore
        AND dbo.SalesReportRMS.Transaction_Date BETWEEN '5/01/11 00:00:01'
                                                AND     '5/31/11 23:59:00'
GROUP BY dbo.SalesRep.Number ,
        dbo.SalesReportRMS.SalesRep ,
        dbo.SalesReportRMS.LogicalStoreName ,
        dbo.SalesRep.[Target]

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-08 : 07:06:41

You can change it to use the month by changing the where clause to


AND month(dbo.SalesReportRMS.Transaction_Date) = 5

However, two things about this change.

First, if there is the possibility that your report/data can span more than a single year, you would also need to include the year, for example like this:


AND month(dbo.SalesReportRMS.Transaction_Date) = 5
AND year(dbo.SalesReportRMS.Transaction_Date) = 2011

Second, the way you have it currently is probably more efficient that what I just described above, especially if Transaction_Date is indexed.

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2011-06-08 : 14:08:00

quote:

Originally posted by direrayne

I have the query below which will be used in ssrs.
the problem i have is that i need to query data using the month in the where clause only. not using "transaction_date" between in the query. I need some help on how to do this.

SELECT  --dbo.SalesRep.Number,
        dbo.SalesReportRMS.SalesRep ,
        dbo.SalesReportRMS.LogicalStoreName AS [Home Store] ,
        dbo.SalesRep.[Target] ,
        SUM(dbo.SalesReportRMS.FullPrice) AS [Home Store Sales] ,
        SUM(dbo.SalesReportRMS.Quantity) AS [Quantity]
FROM    dbo.SalesReportRMS
        LEFT OUTER JOIN dbo.SalesRep ON dbo.SalesReportRMS.SalesRepID = dbo.SalesRep.Number
WHERE   ( NOT ( dbo.SalesRep.[Target] = '0' )
        )
        AND ( NOT ClassID LIKE 'MISS-%'
            )
        AND ( dbo.SalesRep.ShTarget = 'y' )
        AND dbo.SalesReportRMS.StoreID = dbo.SalesRep.HomeStore
        AND dbo.SalesReportRMS.Transaction_Date BETWEEN '5/01/11 00:00:01'
                                                AND     '5/31/11 23:59:00'
GROUP BY dbo.SalesRep.Number ,
        dbo.SalesReportRMS.SalesRep ,
        dbo.SalesReportRMS.LogicalStoreName ,
        dbo.SalesRep.[Target]

Actually it is better to compare the full transaction date, because index on transaction date can be used efficently in that case. However instead of

rpt.transaction_date between '5/01/11 00:00:01' and '5/31/11 23:59:00'

better use

rpt.transaction_date >= '20110501 00:00:00' and
rpt.transaction_date < '20110601 00:00:00'

Mirko

My blog: http://mirko-marovic-eng.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources