开发者

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

开发者 https://www.devze.com 2022-12-23 05:14 出处:网络
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:

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.

0

精彩评论

暂无评论...
验证码 换一张
取 消

关注公众号