Start of week - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Start of week

Author

Topic

Corobori
Posting Yak Master

105 Posts

Posted - 2006-04-04 : 12:18:37

I have got a view based upon the SQL Statement shown below which works fine but one tiny problem: my hosting's company SQL Server is in the US and it considers the Start of week as Sunday, not Monday. Any way how handling this in my view ?


SELECT     TOP 100 PERCENT SUM(dbo.tblSiteStats.Ss_View) AS iViews, DATEPART(yyyy, dbo.tblSiteStats.Ss_Date) AS sAnio, dbo.tblSiteDesc.Sd_Title AS sTitle, 
                      dbo.tblSite.S_Id, dbo.tblSite.S_Name, dbo.tblSite.S_Logo, dbo.tblSiteDesc.Sd_Desc, DATEPART(wk, dbo.tblSiteStats.Ss_Date) AS sSemana
FROM         dbo.tblSiteDesc INNER JOIN
                      dbo.tblSite ON dbo.tblSiteDesc.Sd_Id = dbo.tblSite.S_Id INNER JOIN
                      dbo.tblSiteStats ON dbo.tblSite.S_Id = dbo.tblSiteStats.Ss_SiteId
WHERE     (dbo.tblSiteDesc.Sd_LanguageId = 5)
GROUP BY dbo.tblSiteStats.Ss_SiteId, DATEPART(yyyy, dbo.tblSiteStats.Ss_Date), DATEPART(wk, dbo.tblSiteStats.Ss_Date), dbo.tblSiteDesc.Sd_Title, 
                      dbo.tblSite.S_Id, dbo.tblSite.S_Name, dbo.tblSite.S_Logo, dbo.tblSiteDesc.Sd_Desc
ORDER BY DATEPART(yyyy, dbo.tblSiteStats.Ss_Date), DATEPART(wk, dbo.tblSiteStats.Ss_Date), SUM(dbo.tblSiteStats.Ss_View) DESC

jean-luc
www.corobori.com

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-04-04 : 12:35:39

I couldn't find anything in ur query which is affected because of the Day of Starting the Week!!

Srinika

Corobori
Posting Yak Master

105 Posts

Posted - 2006-04-04 : 12:51:26

What about this part: "DATEPART(wk, dbo.tblSiteStats.Ss_Date) AS sSemana" ? If I am not mistaken wk gives the week's number, this week is considered as beeing the week number 14. Running part of the query shown above

SELECT     TOP 100 PERCENT DATEPART(yyyy, Ss_Date) AS sAnio, DATEPART(d, Ss_Date) AS sDia, DATEPART(wk, Ss_Date) AS sSemana
FROM         dbo.tblSiteStats
WHERE     (Ss_Date > CONVERT(DATETIME, '2006-04-01 00:00:00', 102))
GROUP BY DATEPART(yyyy, Ss_Date), DATEPART(d, Ss_Date), DATEPART(wk, Ss_Date)

gives me following result for April:
Year Day Weeknumber
2006 1 13
2006 2 14
2006 3 14
2006 4 14

And for me the April 2nd is Sunday and should be considered as being week 13, not 14

jean-luc
www.corobori.com

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-04-04 : 12:59:52

Ok, my error

Check "SET DATEFIRST " in BOL

Srinika

Corobori
Posting Yak Master

105 Posts

Posted - 2006-04-04 : 13:44:04

BOL is Bill Of Landing for me and for you ?

jean-luc
www.corobori.com

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-04-04 : 13:53:36

For me also something like that, just before being a member of this forum.
U have been visiting this forum, for quite a long time and by now it should not be "Best Of Luck"

Also U may go thru the following "AGAIN"
http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=13557