Post reply

Workaround for DATEDIFF

  • May 7, 2003 at 4:26 pm

    #80889

    I need a workaround for SQLServer's version of this function. I am replacing VB code that uses its version of DATEDIFF; in that version, you can give it an extra parm, one that tells it the first day of the week. I want to use the function to tell me how many weeks I have in a given period, but have the week begin on day X, where X is a parameter.

    I have tried setting the @@DATEFIRST value in the manner used in the VB function, but it doesn't seem to work. Any ideas?

  • May 7, 2003 at 7:25 pm

    #456470

    SQLServer has Sunday as day 1 of the week, so if you want your week to start on Monday, it can be a pain.

    I had a similar problem a few years ago. I set up a simple two column, 7 record table, with the SQL Server numeric day of the week, and the numeric day of the week the way I wanted.

    You'll need to grab the datapart(weekday, your date) then translate that using the new table -> then go from there.

    Other options would be to set up a new external (extended) procedure, or use sp_OACreate to call an object you create in VB.

    Hope this helps. No easy answer.


    What's the business problem you're trying to solve?

  • May 7, 2003 at 8:39 pm

    #456473

    Thanks for the quick reply. I'll have to look into the ideas you've suggested.

  • May 7, 2003 at 9:34 pm

    #456474

    See BOL 2000: SET DATEFIRST

  • May 8, 2003 at 3:53 am

    #456504

    I agree make sure you are using SET DATEFIRST to set the first day. However if you still have troubles then consider creating a sliding process to slide the dates back the number of days to bring in line for the calculation only.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply