DATEPART sales month ? - 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 2005 Forums

Transact-SQL (2005)

DATEPART sales month ?

Author

Topic

mberggren
Starting Member

15 Posts

Posted - 2011-12-15 : 05:31:50

Hi,

I´m trying to figure out how to get sales from last month. By manual typing this below I´ll get the result for november. I want it to be more dynamic.

WHERE DATEPART(mm,table2.business_date) = 11

I did try to write this below but didn´t work. Everytime I run the script I want to get results of previous month sale.

Is it the DATEPART function I need here or something else ?

WHERE DATEPART(mm,table2.business_date) - 1


SELECT
table1.name AS Catagory,
COUNT(table2.sales_count) AS Qty,
SUM(table2.sales_total) AS Total
FROM table2 
JOIN table1 
ON table2.seq = table1.seq
WHERE DATEPART(mm,table2.business_date) = 12
GROUP BY table1.name
ORDER BY table1.name ASC

Regards

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2011-12-15 : 05:45:56

WHERE DATEPART(mm,table2.business_date) = datepart(mm,GETDATE())-1

Senthil Kumar C
------------------------------------------------------
MCITP - Database Administration SQL SERVER 2008
MCTS - Database Development SQL SERVER 2008

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-15 : 05:54:49

[code]
WHERE table2.business_date> = dateadd(mm,datediff(mm,0,GETDATE())-1,0)
AND table2.business_date< dateadd(mm,datediff(mm,0,GETDATE()),0)
[/code]

if you want to utilise an index which is available on business_date field

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mberggren
Starting Member

15 Posts

Posted - 2011-12-15 : 05:54:55

@Senthil Kumar C

Thanks a lot, it worked perfectly. You saved my day! :)

Regards

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2011-12-15 : 06:32:41

quote:
Originally posted by mberggren

@Senthil Kumar C

Thanks a lot, it worked perfectly. You saved my day! :)

Regards

Welcome

Senthil Kumar C
------------------------------------------------------
MCITP - Database Administration SQL SERVER 2008
MCTS - Database Development SQL SERVER 2008

sql-programmers
Posting Yak Master

190 Posts

Posted - 2011-12-15 : 06:33:36

Hi mberggren,

'Senthil Kumar C' is query is wrong one. For example If you have records for last 3 year in your table then this query will display sum/ count of all the year in the 11 month(previous month) at the same time we can not use ' AND YEAR(table2.business_date) = YEAR(GETDATE())' condition with senthil's query because when you are in January month that query does not return any value because datepart(mm,GETDATE())-1 will return '0' as previous month so,

Use 'visakh16' replied query it is the exact one to get the previous month result.

SQL Server Programmers and Consultants
http://www.sql-programmers.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-15 : 06:45:26

quote:
Originally posted by sql-programmers

Hi mberggren,

'Senthil Kumar C' is query is wrong one. For example If you have records for last 3 year in your table then this query will display sum/ count of all the year in the 11 month(previous month) at the same time we can not use ' AND YEAR(table2.business_date) = YEAR(GETDATE())' condition with senthil's query because when you are in January month that query does not return any value because datepart(mm,GETDATE())-1 will return '0' as previous month so,

Use 'visakh16' replied query it is the exact one to get the previous month result.

SQL Server Programmers and Consultants
http://www.sql-programmers.com/

yep...thats a valid point too in addition to performance aspect

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2011-12-15 : 06:55:12

Yes, Accepted

Can also include 'AND YEAR(table2.business_date) = YEAR(GETDATE())'

Senthil Kumar C
------------------------------------------------------
MCITP - Database Administration SQL SERVER 2008
MCTS - Database Development SQL SERVER 2008

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-15 : 07:01:30

quote:
Originally posted by senthil_nagore

Yes, Accepted

Can also include 'AND YEAR(table2.business_date) = YEAR(GETDATE())'

Senthil Kumar C
------------------------------------------------------
MCITP - Database Administration SQL SERVER 2008
MCTS - Database Development SQL SERVER 2008

It will again make it non sargable and would ignore an index on the date column if already present

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mberggren
Starting Member

15 Posts

Posted - 2011-12-15 : 07:34:41

Scenario,

When using DATEPART in this case I guess it takes sales (november) from
'2011-01-10 00:00:00' to '2011-30-10 00:00:00' starting at 00:00:00.

Can you change the way you pull out the data. For example if you want to get sales from '2011-01-10 00:05:00' to '2011-30-10 00:05:00'.

If you want to start new "business date" + 5 hours from 00:00:00 to 00:05:00, to get sales on the correct day.

Regards

mberggren
Starting Member

15 Posts

Posted - 2011-12-15 : 07:36:42

Thx, I´ll be testing this right away, missed a couple of post here :)

quote:
Originally posted by sql-programmers

Hi mberggren,

'Senthil Kumar C' is query is wrong one. For example If you have records for last 3 year in your table then this query will display sum/ count of all the year in the 11 month(previous month) at the same time we can not use ' AND YEAR(table2.business_date) = YEAR(GETDATE())' condition with senthil's query because when you are in January month that query does not return any value because datepart(mm,GETDATE())-1 will return '0' as previous month so,

Use 'visakh16' replied query it is the exact one to get the previous month result.

SQL Server Programmers and Consultants
http://www.sql-programmers.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-15 : 07:39:59

quote:
Originally posted by mberggren

Scenario,

When using DATEPART in this case I guess it takes sales (november) from
'2011-01-10 00:00:00' to '2011-30-10 00:00:00' starting at 00:00:00.

Can you change the way you pull out the data. For example if you want to get sales from '2011-01-10 00:05:00' to '2011-30-10 00:05:00'.

If you want to start new "business date" + 5 hours from 00:00:00 to 00:05:00, to get sales on the correct day.

Regards

for that you just need to add required part to start and end values

WHERE table2.business_date> = dateadd(mi,5,dateadd(mm,datediff(mm,0,GETDATE())-1,0))
AND table2.business_date< dateadd(mi,5,dateadd(mm,datediff(mm,0,GETDATE()),0))

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mberggren
Starting Member

15 Posts

Posted - 2011-12-15 : 08:16:10

I´ll get a result with this, even though the statement is wrong.

WHERE DATEPART(mm,table2.business_date) = datepart(mm,GETDATE())-1

Running the code you wrote gives me an error, wonder what that could be, "Cannot convert 0 to a date"

WHERE table2.business_date> = dateadd(mi,5,dateadd(mm,datediff(mm,0,GETDATE())-1,0))
AND table2.business_date< dateadd(mi,5,dateadd(mm,datediff(mm,0,GETDATE()),0))

Regards

quote:
Originally posted by visakh16

quote:
Originally posted by mberggren

Scenario,

When using DATEPART in this case I guess it takes sales (november) from
'2011-01-10 00:00:00' to '2011-30-10 00:00:00' starting at 00:00:00.

Can you change the way you pull out the data. For example if you want to get sales from '2011-01-10 00:05:00' to '2011-30-10 00:05:00'.

If you want to start new "business date" + 5 hours from 00:00:00 to 00:05:00, to get sales on the correct day.

Regards

for that you just need to add required part to start and end values

WHERE table2.business_date> = dateadd(mi,5,dateadd(mm,datediff(mm,0,GETDATE())-1,0))
AND table2.business_date< dateadd(mi,5,dateadd(mm,datediff(mm,0,GETDATE()),0))

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-15 : 09:08:58

whats the datatype of business_date?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mberggren
Starting Member

15 Posts

Posted - 2011-12-15 : 09:25:12

@visakh16
Datatype is datetime

quote:
Originally posted by visakh16

whats the datatype of business_date?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-15 : 09:29:57

then it shouldnt throw any error. post the full query

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mberggren
Starting Member

15 Posts

Posted - 2011-12-16 : 06:44:02

quote:
Originally posted by visakh16

then it shouldnt throw any error. post the full query

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/


SELECT
table1.name AS Catagory,
COUNT(table2.sales_count) AS Qty,
SUM(table2.sales_total) AS Total
FROM table2 
JOIN table1 
ON table2.seq = table1.seq
WHERE table2.business_date> = dateadd(mi,5,dateadd(mm,datediff(mm,0,GETDATE())-1,0))
AND table2.business_date< dateadd(mi,5,dateadd(mm,datediff(mm,0,GETDATE()),0))
GROUP BY table1.name
ORDER BY table1.name ASC

I also tried this


WHERE table2.business_date = dateadd(mi,5,DATEPART(mm,GETDATE())-1)

Giving me

Cannot convert 11 to date

Regards

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-16 : 06:49:07

I doubt whether business_date is of type datetime

Anyways, what does this return?

SELECT dateadd(mi,5,dateadd(mm,datediff(mm,0,GETDATE())-1,0)),dateadd(mi,5,dateadd(mm,datediff(mm,0,GETDATE()),0))

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mberggren
Starting Member

15 Posts

Posted - 2011-12-20 : 10:04:21

@visakh16

I finally got it to work. The problem was me using the statement in the wrong environment (sybase). Since T-SQL 2005 automatically converts 0 to 1900-01-01 00:00:00.000 I manually had to this to the statement.


SELECT dateadd(mi,5,dateadd(mm,datediff(mm,'1900-01-01 00:00:00.000',GETDATE())-1,'1900-01-01 00:00:00.000')),dateadd(hh,5,dateadd(mi,datediff(mm,'1900-01-01 00:00:00.000',GETDATE()),'1900-01-01 00:00:00.000'))

Regards

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-20 : 11:56:09

good that you sorted it out

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-12-21 : 02:37:01

quote:
Originally posted by mberggren

@visakh16

I finally got it to work. The problem was me using the statement in the wrong environment (sybase). Since T-SQL 2005 automatically converts 0 to 1900-01-01 00:00:00.000 I manually had to this to the statement.

SELECT dateadd(mi,5,dateadd(mm,datediff(mm,'1900-01-01 00:00:00.000',GETDATE())-1,'1900-01-01 00:00:00.000')),dateadd(hh,5,dateadd(mi,datediff(mm,'1900-01-01 00:00:00.000',GETDATE()),'1900-01-01 00:00:00.000'))

Regards

Also make sure to use unambigious date format
http://beyondrelational.com/blogs/madhivanan/archive/2010/06/03/understanding-datetime-column-part-ii.aspx

Madhivanan

Failing to plan is Planning to fail

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources