i want to set 开发者_JAVA百科the default value in a sql server 2005 datetime field to today + 3 working days.
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
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.
This query stands good for fetching the values between current date and its next 3 dates
SELECT * FROM tableName WHERE columName BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 3 DAY)
This will eventually add extra 3 days of buffer to the current date.
精彩评论