Searching dates - 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

General SQL Server Forums

New to SQL Server Programming

Searching dates

Author

Topic

Eclipse17
Starting Member

1 Post

Posted - 2007-10-17 : 20:50:21

Hi,

I have a table that looks similar to this. How would i write a query that will show me the price of the product on mondays?

Thanks

Date Price
16/10/2007 16.75
15/10/2007 16.25
12/10/2007 15.7
11/10/2007 15.64
10/10/2007 15.17
09/10/2007 14.9
05/10/2007 14.75
04/10/2007 14.52
03/10/2007 14.84
02/10/2007 14.9
01/10/2007 14.84
28/09/2007 15.3

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-10-17 : 21:35:50

perhaps there's a better way but check out the DATENAME function in books online, meanwhile.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Kristen
Test

22859 Posts

Posted - 2007-10-17 : 23:55:32

DATEPART(Weekday, MyDate) might be faster (than DATENAME - i.e. its probably only doing numeric work, no string conversion).

Kristen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-18 : 01:31:37

Datepart function seems to be relying on SET DATEFIRST setting whereas datename do not.

E 12°55'05.25"
N 56°04'39.16"

Kristen
Test

22859 Posts

Posted - 2007-10-18 : 02:01:16

Just do Modulo 7 then maybe?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-18 : 02:33:12

[code]DECLARE @Sample TABLE (dt DATETIME, Price MONEY)

SET DATEFORMAT DMY

INSERT @Sample
SELECT '16/10/2007', 16.75 UNION ALL
SELECT '15/10/2007', 16.25 UNION ALL
SELECT '12/10/2007', 15.7 UNION ALL
SELECT '11/10/2007', 15.64 UNION ALL
SELECT '10/10/2007', 15.17 UNION ALL
SELECT '09/10/2007', 14.9 UNION ALL
SELECT '05/10/2007', 14.75 UNION ALL
SELECT '04/10/2007', 14.52 UNION ALL
SELECT '03/10/2007', 14.84 UNION ALL
SELECT '02/10/2007', 14.9 UNION ALL
SELECT '01/10/2007', 14.84 UNION ALL
SELECT '28/09/2007', 15.3

-- Datepart
SELECT dt,
Price
FROM @Sample
WHERE DATEPART(WEEKDAY, dt) = 2

-- Datename
SELECT dt,
Price
FROM @Sample
WHERE DATENAME(WEEKDAY, dt) = 'Monday'

-- Modulo 7
SELECT dt,
Price
FROM @Sample
WHERE DATEDIFF(DAY, 0, dt) % 7 = 0[/code]

E 12°55'05.25"
N 56°04'39.16"

Kristen
Test

22859 Posts

Posted - 2007-10-18 : 03:10:59

Would

WHERE CONVERT(int, dt) % 7 = 0

be "safe" ? And any quicker than the DATEDIFF?

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-18 : 05:22:39