Calculating weeks off of a date field

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

General SQL Server Forums

New to SQL Server Programming

Calculating weeks off of a date field

Author

Topic

LarryJ
Starting Member

4 Posts

Posted - 2010-04-28 : 14:17:35

Is it possible to calculate a number of weeks to a specific date with a date field using DATEDIFF.

I have a table dbo.Orders with a field ORDER_DATE
How do I select it and then calculate how many weeks this order was placed to an upcoming date using a SELECT statment?

Larry J

Sachin.Nand

2937 Posts

Posted - 2010-04-28 : 14:34:06

Yes use datediff(wk,date1,date2)

PBUH

LarryJ
Starting Member

4 Posts

Posted - 2010-04-28 : 14:44:45

Can you show me the syntax i am having troubles withe the select statment and the showing the number of weeks to the date 7/26/2010

Larry J

Sachin.Nand

2937 Posts

Posted - 2010-04-28 : 14:51:23

What is your query?Please post it.

PBUH

LarryJ
Starting Member

4 Posts

Posted - 2010-04-28 : 16:06:02

SELECT TOP (100) PERCENT COUNT(dbo.Order_Lines.ORDER_NUMBER) AS Count, dbo.Order_Lines.DESCRIPTION, dbo.Order_Lines.UNIT_PRICE,
dbo.Order_Meet.REGISTRANT_CLASS
FROM dbo.Order_Lines INNER JOIN
dbo.Product ON dbo.Order_Lines.PRODUCT_CODE = dbo.Product.PRODUCT_CODE INNER JOIN
dbo.Order_Meet ON dbo.Order_Lines.ORDER_NUMBER = dbo.Order_Meet.ORDER_NUMBER INNER JOIN
dbo.Orders ON dbo.Order_Lines.ORDER_NUMBER = dbo.Orders.ORDER_NUMBER
WHERE (dbo.Product.PRODUCT_MAJOR = 'REG_2010')
GROUP BY dbo.Order_Lines.DESCRIPTION, dbo.Order_Lines.UNIT_PRICE, dbo.Order_Meet.REGISTRANT_CLASS, dbo.Orders.STATUS,
dbo.Product.PRODUCT_CODE, dbo.Orders.ORDER_DATE
HAVING (dbo.Orders.STATUS <> 'C') AND (dbo.Orders.ORDER_DATE = CASE WHEN ORDER_DATE < '07/26/2010' THEN DATEDIFF(week, '08/01/2010',
dbo.Orders.ORDER_DATE) END) AND (dbo.Product.PRODUCT_CODE = 'REG_2010/A_REG')
ORDER BY dbo.Order_Lines.DESCRIPTION

Larry J

Sachin.Nand

2937 Posts

Posted - 2010-04-28 : 16:09:50

[code]
(dbo.Orders.ORDER_DATE = CASE WHEN ORDER_DATE < '07/26/2010' THEN DATEDIFF(week, '08/01/2010',
dbo.Orders.ORDER_DATE) END)
[/code]

Above you are comparing the date value i.e ORDER_DATE to datediff value whose output will be an integer.It will throw an error.

PBUH

LarryJ
Starting Member

4 Posts

Posted - 2010-04-28 : 16:34:06

Thanks got it

Larry J

Sachin.Nand

2937 Posts

Posted - 2010-04-28 : 16:54:02

You are welcome.

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources