Select day of the week in an SQL Query
Sep
4
Written by:
2009/09/04 10:13 AM
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.
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.
blog comments powered by
1 comment(s) so far...
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
|