The Brave Programmer - Blogging and coding
Not for the faint hearted
 

Blog Posts From The Brave Programmer

Minimize

Select day of the week in an SQL Query

Sep4

Written by:
2009/09/04 10:13 AM RssIcon

Just the other day I was analysing my blog stats and noticed that I had a few large peeks on Wednesdays. Obviously I could not remember what posts were done on Wednesdays. So I needed a way to select data from the database by filtering it and returning only posts posted on Wednesdays. So I used this little gem in SQL.

Diary If you have any serious data in a database you will most likely work a lot with dates. Date values are stored in date table columns in the form of a timestamp. An SQL timestamp is a record containing date/time data, such as the month, day, year, hour, and minutes/seconds.

There is a neat little function in MS SQL called DatePart. It’s syntax is Datepart([date part (Varchar)],[Expression(Datetime)])

So today is Friday, and  a simple test would reveal that.

SELECT DATEPART(WEEKDAY, GETDATE())

Which would return 6. 6 Being day six, if your first day is Sunday, that being day 1. You can check this out by using:

Select @@datefirst

The weekday 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.

So with this in hand you can write a little function to return the name of the week days.

----------------------------------------
--- GetWeekDayNameOfDate
--- Returns Week Day Name in English
--- Takes @@DATEFIRST into consideration
--- You can edit udf for other languages
--------------------------------------
CREATE FUNCTION GetWeekDayNameOfDate ( @Date datetime )
RETURNS nvarchar(50)
 
BEGIN DECLARE @DayName nvarchar(50)
SELECT
@DayName =
CASE (DATEPART(dw, @Date) + @@DATEFIRST) % 7
WHEN 1 THEN 'Sunday'
WHEN 2 THEN 'Monday'
WHEN 3 THEN 'Tuesday'
WHEN 4 THEN 'Wednesday'
WHEN 5 THEN 'Thursday'
WHEN 6 THEN 'Friday'
WHEN 0 THEN 'Saturday'
END
RETURN @DayName END GO

Thanks to http://www.kodyaz.com/articles/get-week-day-name-of-date-using-t-sql.aspx for the code above.

We can use that by issuing the following t-sql command which would return Friday for today:

SELECT GetWeekDayNameOfDate(GETDATE()) as WeekDayName

The nice thing about this is that you can now create a function that returns the week day name in your own particular language by just substituting the strings.

But wait. There is an easier way. MSSQL has another neat little date function, it’s called Datename. This can return the actual given weekday name. It has the same syntax as Datepart, Datepart([date part (Varchar)],[Expression(Datetime)])

So to find out what today's name is just issue this command:

Select DATENAME(WEEKDAY, GETDATE()) as Weekday

So my problem was solved, I could now find out which blog posts were causing those fantastic spikes. By doing this, perhaps I could learn something. I could find out what type of posts my readers enjoyed.

I issued the following command on my blog database:

select * from Blog_Entries
where DateName(Weekday,addeddate) = 'Wednesday'
order by addeddate desc

or

select * from Blog_Entries
where DatePart(Weekday,addeddate) = 4
order by AddedDate

Both returned the posts I needed. Problem solved.

New here, or perhaps you've been here a few times? Like this post? Why not subscribe to this blog and get the most up to date posts as soon as they are published.

 

Technorati Tags: ,,,,


Tags:
Categories:
blog comments powered by Disqus

1 comment(s) so far...


Gravatar

Re: Select day of the week in an SQL Query

http://www.kodyaz.com/articles/get-week-day-name-of-date-using-t-sql.aspx

By which came first? on  2009/09/23 07:08 PM
 
Blog Updates Via E-mail
 Blog Updates Via E-mail
Minimize

Do you want to receive blog updates via e-mail. Then just click on the link below. You will be redirected to Google's feed burner, where you can fill out a form. Supplying your e-mail address.

The subscription is managed entirely by Google's Feedburner. We cannot and do not collect your email address.

Subscribe to The Brave Programmer by Email

Print  
 

 

Latest Comments
 Latest Comments
Minimize
Powered by Disqus

Sign up with Disqus to enjoy a  surprise box of features

Print  
 
Blog Roll
 Blog Roll
Minimize
Print  
 
Categories
 Categories
Minimize
Print  
 
<h1>Search Blogs From The Brave Programmer</h1>
 

Search Blogs From The Brave Programmer

Minimize
Print  
 
Archive
 Archive
Minimize
Archive
<December 2014>
SunMonTueWedThuFriSat
30123456
78910111213
14151617181920
21222324252627
28293031123
45678910
Monthly
Go
Print  
 
<h1>News Feeds (RSS)</h1>
 

News Feeds (RSS)

Minimize

Mon, 26 May 2014 13:39:24 -0500

Mon, 19 May 2014 13:10:38 -0500

Tue, 13 May 2014 05:00:08 -0500

Mon, 12 May 2014 05:00:43 -0500

Sun, 11 May 2014 05:00:26 -0500

Tue, 06 May 2014 05:00:02 -0500

Mon, 05 May 2014 05:00:34 -0500

Fri, 02 May 2014 05:00:52 -0500

Thu, 01 May 2014 05:00:00 -0500

Fri, 18 Apr 2014 05:00:38 -0500

Print  
 

Follow robertbravery on Twitter

Blog Engage Blog Forum and Blogging Community, Free Blog Submissions and Blog Traffic, Blog Directory, Article Submissions, Blog Traffic

View Robert Bravery's profile on LinkedIn

Mybyte

 

Robert - Find me on Bloggers.com

Tags
 Tags
Minimize
Print  
 
Contact Us Now
 Contact Us Now
Minimize
 

Email  us now or call us on 082-413-1420,  to host your website.

We design and develop websites. We develop websites that make a difference. We do Dotnetnuke Module development.

Web Masters Around The World
Power By Ringsurf
Print