sunday is my week ending day

Hi All,

When I use DATEDIFF(week, '6/1/2010', '6/6/2010') the answer I expect should 
be 0 because I consider Sunday my week ending day. The answer I get is 1 
which is incorrect in my situation.

How would I adjust for this?

Thanks,
rodchar
0
Utf
6/1/2010 1:27:01 PM
sqlserver.programming 1873 articles. 0 followers. Follow

3 Replies
544 Views

Similar Articles

[PageSpeed] 42

You can use SET DATEFIRST  to change first day of the week and check it's 
current value using  SELECT @@DATEFIRST.
But I think algorithm if DATEDIFF does not take it into account. Instead you 
can count number of days and divide it by 7.

"rodchar" <rodchar@discussions.microsoft.com> wrote in message 
news:B0DC77EB-7C88-48A1-93C8-E36B1926B41E@microsoft.com...
> Hi All,
>
> When I use DATEDIFF(week, '6/1/2010', '6/6/2010') the answer I expect 
> should
> be 0 because I consider Sunday my week ending day. The answer I get is 1
> which is incorrect in my situation.
>
> How would I adjust for this?
>
> Thanks,
> rodchar 


0
Maria
6/1/2010 2:01:59 PM
Use integer division

SELECT	DATEDIFF(DAY, 0, '6/6/2010') / 7 - DATEDIFF(DAY, 0, '6/1/2010') / 7

//Peso


"rodchar" <rodchar@discussions.microsoft.com> wrote in message 
news:B0DC77EB-7C88-48A1-93C8-E36B1926B41E@microsoft.com...
> Hi All,
>
> When I use DATEDIFF(week, '6/1/2010', '6/6/2010') the answer I expect 
> should
> be 0 because I consider Sunday my week ending day. The answer I get is 1
> which is incorrect in my situation.
>
> How would I adjust for this?
>
> Thanks,
> rodchar 

0
Peso
6/1/2010 3:06:49 PM
thanks all for the help,

rod.

"rodchar" wrote:

> Hi All,
> 
> When I use DATEDIFF(week, '6/1/2010', '6/6/2010') the answer I expect should 
> be 0 because I consider Sunday my week ending day. The answer I get is 1 
> which is incorrect in my situation.
> 
> How would I adjust for this?
> 
> Thanks,
> rodchar
0
Utf
6/1/2010 5:26:01 PM
Reply:

Similar Artilces:

Sending One Mail ends in multiple at the receiver
I'm using Outlook 2002 SP2 and multiple POP accounts (but only one SMTP Server). When I send large attachements (>0,5 MB) I very often have the case, that receivers get this message multiple times, even when I still see the message queing in my outbox. What is the problem? Please help regards Stephan Are you getting any errors sending the message? Sounds like the send is erroring out and resending, resulting in multiple items "Stephan" <dorfmeister@aon.at> wrote in message news:0a5901c39304$68e2eb80$a401280a@phx.gbl... > I'm using Outlook 2002 SP2 and multip...

Connection problem with 60 day trial #2
I'm sorry to keep posting "new" questions, but on top of having problems with Outlook, i'm having problems with figuring out how this "Discussion Group" process works. Every time i open the Discussion Group page, I get a message that there is no profile for me and so I have to re-create my profile each time. I see that Peter Foldes has posted as response to my question. Thank you Peter! However, I cannot get the response to display. When I click on it, I get the message, "No posts available for this thread. Any suggestions on what I can do differen...

Why can't I get a calendar that starts on Sunday?
All the calendars start with Monday as the first day of the week. I need calendars that start on Sunday. What are your regional settings? If you have US selected you should be getting a Sunday calendar. There are calendars on the Office web site. http://office.microsoft.com/en-us/templates/results.aspx?qu=calendar&sc=4&av=ZPB -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "crosstown" <crosstown@discussions.microsoft.com> wrote in message news:0FB64D49-7EB8-4CC0-931B-F21BDE78C9C9@microsoft.com... > All th...

Server and end user errors and crashes
I attempted to post this question earlier but cannot seem to find it anywhere. So if it's duplicated I apologize. I could really use some help here! I have posted this question in another group forum and was directed to Sue Mosner's book which I intend to purchase. However I could use some immediate help. My Exchange server 2003 errors and crashes every hour with the following error; "MSExchange Transport". Failed in reading Connector's DS Info Process Id: 296 Process location: My end user computers are all getting the following error: Windows Operating System Event ID: ...

Weekly recurring tasks
Hi, I have a set of recurring tasks. For Eg: weekly meeting. Its duration is only 1 hr Is there any provision for automatically generating such tasks in ms project after adding one isnatnce of the task? How can I link these tasks? Thanks! Hi Maanu Insert, Recurring Task Each meetig tasks is generated with a Start No Earlier Than Constraint, no need to link them between each other. If you want to link them to other tasks, no problem, they behave just like any other task. HTH -- Jan De Messemaeker Microsoft Project Most Valuable Professional +32 495 300 620 ...

Day formula
What's going on with the Day formula for 2010? I am working in Excel 2003 and the formula that I always use to pull the day of the week isn't working. I thought it might be my spreadsheet but I tried it in a blank one and got the same thing. Cell E3 has date 01/28/2010 formatted as a date. Cell G3 references cell E3 with this formula =DAY(E3) and it is formatted as custom DDDD - so that I see the day of the week. January 28, 2010 is a THursday and it returns a Saturday. When I format as a date only I get January 28th 1900. What am I doing wrong. It is not set...

HYIP-System.com
Investing from as low as $1.00 http://www.HYIP-System.com/?ref=1618643 \Martin ...

problems adding days to a date
Hi, I have a field that sums a date plus 14 days and I am getting some very strange results. Any clue? -- Carys What have you got and what strange results are you getting If there is a 'true' date in A1 (01/01/2006) then =A1+14 will give you 15/01/2006 (UK dd/mm/yyyy) -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England www.nickhodge.co.uk nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS "Carys" <Carys@discussions.microsoft.com> wrote in message news:9695E500-67BA-407C-AF2F-3AEA692F37A2@microsoft.com... > Hi, > > I have a field that sums a date plus...

How do I get DEVERRORS working for end users?
I have deverrors = "on" in web.config, yet one of my users is still getting simple errors: "An error has occurred. For more information, contact your system administrator." How can I get this user to see development errors so I can troubleshoot a problem he is having with creating appointments? Dan ; is it just this one user who cannot see the dev errors, while others do see them? try flushing his IE cache and temp internet files, and see if that helps. alternatively, have this user reproduce the error while logged into a machine that is showing the dev errors. Da...

Exchange 2003: Implementing a policy to delete mail after 30 days..
Two parts to the question... First, If I implement that policy today for 30 days, does the clock start ticking for all messages in my mailbox (that I've selected) starting today? (i.e. all messages with timestamps older than 30 days will not be deleted until the first 30 days is up? Just want to make sure messages dont't start getting immediatley deleted when I turn the policy on... Second question: I think there are 2 diofferent places where I could implement this policy: under administrative groups for the mailbox store, and under Recipient Polices at the top.. I only have 1 ex...

Receiving emails a day after they were sent
Still pretty new to supporting email: Two part question: How do I find out why we are receiving some emails anywhere from 8 hours to 24 hours after they were sent? I have a copy of one of the email headers below: Microsoft Mail Internet Headers Version 2.0 Received: from sears.com (hofsecimgw1.sears.com [198.179.148.4]) by dgntexch.nsamedia.com with SMTP (Microsoft Exchange Internet Mail Service Version 5.5.2657.72) id XRPL9D93; Wed, 24 Nov 2004 20:51:27 -0600 Received: from ([166.76.89.65]) by hofsecimgw1.sears.com with ESMTP id KP-NTG56.37237762; Tue, 23 Nov 2004 08:46:09 -0600 ...

Accessing SQL Server Table-Valued Function from Access front-end
Hi Is subject possible? From SQL Server, I can call the function as Select * From dbo.udf_MyFunction(Parameter1, Parameter2, ...) From Access front-end, I want to call this function, and use the result table as data source for report. My quess is, that I have to use a pass-through query for this. Has someone any experience with this? Thanks in advance! Arvi Laanemets Yes, you need to use a pass-through query using a Select query like the one you have shown us. -- Sylvain Lafontaine, ing. MVP - Windows Live Platform Blog/web site: http://coding-paparazzi.sylvai...

How can the current week be the default starting week in Month View?
Using OL2003... I manually shade the next four weeks (including the current week) in order to display a relevant month view. I check email, return to the calendar view, and the month view has reverted back to a month view where the current month's first week is the initial week displayed. How can one cause the month view to default to the current week and the next X weeks afterward? I do not have a need for historical weeks to be presented in a month view. Thanks, --Howie ...

Calculating 40hr work week
Hello: Using Excel 2002(xp)... I need to find out the total hours based on a 40hr week. The daily figures that I will be entering into the cells are in HR:MIN format (it's in this format to begin with). How can I total this daily time, to display it in HR:MIN? The total would look like 40:10, which is 40hr and 10min... Also: Is there away to convert 40.05hrs to HR:MIN? I like the answer to display 40:03, which is 40hrs and 3 minutes. (40.05 = 40:03) And (vice-versa, 40:03 = 40.05) Thanks, Mark One way: Use SUM(A1:A10), for example, but format your cell with Form...

How to manage email past 90 days
Hello, I am looking to be able to move all items past 90 days to the Deleted Items folder, then delete any items past 120 days from Deleted Items. I've been looking at Recipients Policy but see the following problem: 1. Only 1 Recipient Policy is applied to an object/user. 2. With the Policy, there are only global options (Report,Delete,Move to Deleted Items, Move to System Cleanup Folder) that apply to folders, so I cant set Move to Deleted Items for Inbox, Sent, etc, and apply Delete to Deleted Items. Anyways, is there a better procedure or one that anyone uses that can allow f...

Include day of the week with date
Is there a way in Excel 2000 to display the day of the week with the date? Example: Thursday, November 11, 2004 I know its a choice in Excel 2003 but I can't find it in Excel 2000. Thank You! Carin Carin, one way format the cell as dddd, mmmm d, yyyy -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2000 & 2003 "Carin Mac" <CarinMac@discussions.microsoft.com> wrote in message news:F927B755-A4D3-49A3-94AF-76CF9AD3FDEC@micr...

Fax Line utilization with Start and END time data
I have large list from our fax servers that produces a lof with : Start of FAX Connect date/time, End of Fax Connect date/time I know I have 24 lines but need to find how many lines have I bee using historicaly to do capacity planning. Any suggestions? M -- Message posted from http://www.ExcelForum.com ...

Outlook 2000 saves calendar as webpage, but dates dont match days ???
when i save an outlook 2000 calendar as a web page all the 2005 dates (the 2004 dates seem ok) get shifted, so the 30th of may 2005 shows up as a sunday instead of a monday. any ideas? im wondering, is there a better place to post this question? seems like 'real' problem with the outlook program... similar questions in other forums (and no answers). could find nothing in ms 'knowledgebase'. no ideas on how to fix or work-a-round. thank you. "akm" wrote: > when i save an outlook 2000 calendar as a web page all the > 2005 dates (the 2004 dates seem ok) get ...

Simple front end to access pop3 email from Exchange 2003 (SBS 2003)
Hello all! I apologize in advance for the cross posting but I'm in a bit of a time crunch. I recently set up an SBS 2003 Server for a customer who owns a retirement community in Florida. They had been using hosted pop3 accounts from Interland (now Web.com) for their business email. They had also been handing out email accounts to their residence under the same domain as their business. I went ahead and changed the MX record for their domain to point to the Exchange Server and added the business accounts. Of course, this now prevents the residence from getting email unless I create...

Front End Code is Gone
Hi Groupies: I have created an Access 2007 split database for my client. They called me today to say that everything is frozen and nothing happens when they push buttons etc. I had them send me the FE and it appears that all of the code has been completely wiped out! Events still have the text [Event Procedure] but when I go to look at the VB there is nothing there!! They have experienced 2 issues that they mentioned to me. 1. One report needs to print on a dot matrix printer. If the report is more than one page, the printer throws out junk. I think it's a driver issue but they s...

Outlook 2002 as a day planner?
Hi - I've been using Outlook Express for email, but I'm new to Outlook. I wanted to use Outlook in place of my (paper) day planner...is there a way to save individual days? For example, if I want to see what my day was on the 12th of last month, I'd like to be able to see my task list and notes for that day too? -- KJ you can print it out or just never delete things from it. -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Coauthor, OneNote 2003 for Windows (Visual QuickStart Guide) Author, Google and Other Search Engines (Visual QuickStart...

First Day of Next Quarter
Hi, how would one create a cell formula to return the first day of the next quarter? Brian, Try this =EOMONTH(TODAY(),MOD(MONTH(TODAY())*2,3))+1 If your using E2003 and get a NAME error then Tools|Addins and select the analysis toolpak and re-enter the formula Mike "Brian" wrote: > Hi, how would one create a cell formula to return the first day of the next > quarter? .... or =DATE(YEAR(A1),MONTH(A1)+3-MOD(MONTH(A1)+2,3),1) if you don't want to bother with the ATP. -- David Biddulph Mike H wrote: > Brian, > > Try this > &g...

WinXP Pro shutdown message "End program
Hello All, How can I fix the "End program - WMS idle" message when shutting down WinXP Pro? When I shut down WinXP Pro SP2 I receive the above message. Googling I've found http://support.microsoft.com/kb/921113 however it appears to apply to Contact Manager 2005. I do have the Contact Manager installed with Office Professional 2003, so I'm not sure this applies. Any ideals? TIA! -- Regards, Greg Strong That article doesn't mention Contact Manager at all...it applies to Communicator 2005. When you shut down Outlook before shutting down Windows, go to the Task ...

Referring to Back End Database
Hi All, I have a split database which has the following VBA macro to compact/repair the back end database: Private Sub Form_Unload(Cancel As Integer) Dim strBackupBackend As String Dim strCurrBackend As String Dim strCurrLockFile As String strCurrBackend = "H:\Data\Doc Tracker Master 00000_be.mdb" strCurrLockFile = "H:\Data\Doc Tracker Master 00000_be.ldb" strBackupBackend = "H:\Data\Doc Tracker Master 00000_be.bak" If Len(Dir(strCurrLockFile)) > 0 Then MsgBox strCurrBackend & " is still in use. " & _ &...

Add start of new week date when new week starts
Hi all, I am hoping someone can help me with the following problem. In column A, I have the column formatted as a date. I would like for Excel to populate the column and the cell everytime the date from the computer system changes to a Monday. i.e. Column A row 1 cell should read 13 Jun 05 when the date of my pc reaches Monday 13 June. The same should go for row 2 that when my pc reaches Monday 20 June, then the cell in column A row 2 should populate with 20 Jun 05 etc ... Thank you all in advance. One way: A1: =IF(TODAY()>=DATE(2005,6,13),DATE(2005,6,13),"") A2: ...