SELECT * FROM Table Where Date = Monday

[Upchurch]

I have a table that contains a column of dates. My life would be so much easier if I could query it for just the Mondays.

For the sake of argument, I could located the first monday in the record set and only use every 7th record, but that scares the bejeesus out of me since one missing record will through the whole thing off.

So, is there a way to construct a SQL query that will located the Mondays?

eta: This is on an MS SQL server

[Upchurch]

Nevermind. Answered my own question:

SELECT *
FROM Table
WHERE (DATEPART(dw, date) = 2)
ORDER BY date DESC

[DeviousB]

Try using the DATEPART(part, date) function. A part of "WEEKDAY" returns the day number of the week, where Sunday = 1, etc.

e.g. SELECT * FROM myTable WHERE DATEPART(WEEKDAY, myDate) = 2;

[DeviousB]

Nevermind. Answered my own question:


While I was typing the very same answer too!:eek:

Someone get Rupert Sheldrake on the phone, now!

[boooeee]

Will this work? The "dw" specifies that you just want the day of the week of the given date.

WHERE datename(dw, [date field]) = "Monday"

I'm not sure if datename is a valid function in MS SQL.

[DeviousB]

I'm not sure if datename is a valid function in MS SQL.


It is, but IIRC it returns the localised day name, so if you were writing a query for an international project, 't'ain't so hot!

[Upchurch]

It is, but IIRC it returns the localised day name, so if you were writing a query for an international project, 't'ain't so hot!

It doesn't, so it's fine.

[CriticalThanking]

Please be aware that the day of week (dw) portion of DATEPART(dw,<yourdate>) depends upon a setting and may not be the same in every environment. For a default installation, you are correct that 2 is Monday. From BOL:

The weekday (dw) datepart returns a number that corresponds to the day of the week, for example: Sunday = 1, Saturday = 7. The number produced by the weekday datepart depends on the value set by SET DATEFIRST. This sets the first day of the week.

CT

[Upchurch]

That is server dependant and not client dependant, right?

[CriticalThanking]

That is server dependant and not client dependant, right?It can also be set per session at runtime.
SET DateFirst n
will change it on the fly for your session. The US default is 7 (Sunday is first day). The full SQL2K5 description can be found here (http://msdn2.microsoft.com/en-us/library/ms181598.aspx).

To see the impact, run the following. This is safe as it will not affect any other sessions.

DECLARE @CurrentDateFirst tinyint

select @CurrentDateFirst = @@datefirst
SELECT 'Current @@DateFirst in your session is ' + convert(varchar,@CurrentDateFirst)

select 'DW of today is (by default in your installation) ' + convert(varchar,datepart(dw,getdate()))

SET @CurrentDateFirst = @CurrentDateFirst + 1
IF @CurrentDateFirst > 7
SET @CurrentDateFirst = 1
set datefirst @CurrentDateFirst

select 'DW of today is now ' + convert(varchar,datepart(dw,getdate()))

--TO SET YOUR SESSION BACK TO US DEFAULT, RUN THE FOLLOWING
--(OR JUST DISCONNECT YOUR SESSION).
--SET DATEFIRST 7


If you want to discuss further, PM me and I will send you my work email. It will be faster. Or we can bore everyone to tears with SQL Server internals. :o

CT

[varwoche]

If you want to discuss further, PM me and I will send you my work email. It will be faster. Or we can bore everyone to tears with SQL Server internals. :o I like learning SQL Server stuff.

[Upchurch]

I don't mind the extra info, but my primary concern is that I don't run afoul with some user's local date settings.

[Wudang]

Allow me to recommend the excellent "SQL for smarties" by Joe Celko.