put today + 3 working days (as default value) in a Sql datetime field

Question

Hi,

i want to set the default value in a sql server 2005 datetime field to today + 3 working days.

Answer 1

Hard. SQL defaults are to be simple - and working days are PRETTY complicated to do. Not only do you need to think of day of week, but also of.... holidays.

If you have a holiday table, you could do it with a custom function and/or a triggered stored procedure.

Answer 2

You'll want to set your "Default Value or Binding" to one of the following:

SELECT (dateadd(day,(3),getdate()))

SELECT (dateadd(day,(3),cast(floor(cast(getdate() as float)) as datetime)))

The first one will give you today's date and time and add exactly 3 days to it (so you'll be let with the time value in there as well).

The second will give you today's date with a time value of 00:00:00 (i.e. midnight) and add exact 3 days to it.

EDIT:

Ah.. I see you said "working days". Well, my example will give you 3 chronological days, but to calculate 3 working days will be incredibly hard since the very definition of "working day" can be slightly ambiguous. For example, I'm going to make the assumption that a "working day" is the standard Monday to Friday. However, what happens when one of those Mondays is a national holiday? In the UK, that can change each year (i.e. it's not always on the exact same date).

Calculating "working days" is difficult, and is usually best done via a lookup table using a function (either in code or in SQL).

Here's one link that may provide insight into this... It's not necessarily pretty, though! :)

SQL Server - UDF for Business Days Calculation