5

Is it possible for a column in to be auto-calculated by SQL Server 2012 itself?

For example: I have three columns START_DATE, END_DATE and DURATION.

I want to get the duration by doing this :

DURATION = END_DATE - START_DATE

So I get the duration in number of days.

Is it possible for SQL Server 2012 to do it automatically on record creation ?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Brian
  • 1,951
  • 16
  • 56
  • 101

2 Answers2

7

Yes, sure - just define Duration to be a computed column:

ALTER TABLE dbo.YourTable
ADD Duration AS DATEDIFF(DAY, START_DATE, END_DATE) PERSISTED

and off you go. Now Duration will always show the difference (in days) between these two other columns. The value computed will be stored along side your other column values, if you use the PERSISTED keyword. This column will be updated if any of the two "dependent" columns changes, too.

Update: if you want to get the difference between a date and today, you can use

ALTER TABLE dbo.YourTable
ADD Duration AS DATEDIFF(DAY, START_DATE, GETDATE())

but unfortunately, since the GETDATE() function is non-deterministic (after all - it's return value changes every time you call it), you cannot use the PERSISTED keyword. This means: every time you access the column (ask for its value), the calculation will be done again.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • Thank you very much. And how, for example, If I wanted today's date, how do you perform the same calculation instead of END_DATE , i.e. `today's date - start_date` ? – Brian Apr 14 '13 at 12:08
  • What I tried is this: `ALTER TABLE dbo.users ADD age AS DATEDIFF(YEAR, CAST(GETDATE() AS DATE), date_of_birth) PERSISTED` but I get this error: ` Computed column 'age' in table 'users' cannot be persisted because the column is non-deterministic.` – Brian Apr 14 '13 at 12:12
  • @Brian: using `GETDATE()` is the problem - that's non-deterministic, so you cannot persist the value. If you **must** use `GETDATE()`, then you must leave out the `PERSISTED` keyword and the value will be calculated every time you access the column – marc_s Apr 14 '13 at 12:16
  • So is there any alternative to `GETDATE()` so that I could get the date automatically with `PERSISTED`? – Brian Apr 14 '13 at 12:23
  • 1
    @Brian: no, unfortunately not. Since the current date (with the time portion included) is **always** non-deterministic, you really can't do this.... – marc_s Apr 14 '13 at 12:55
  • 1
    @Brian no, but do you absolutely need it to be persisted? It doesn't make sense to persist this value, since it changes every day. – Aaron Bertrand Apr 14 '13 at 13:09
  • 1
    @Brian also you know that taking `DATEDIFF(YEAR` is not a reliable way to calculate an age, right? It essentially treats everyone's birthday as January 1st. – Aaron Bertrand Apr 14 '13 at 13:11
1

In your Table Design, enter any of these in (Formula) under Computed Column Specification for your required result.

For Date Difference:

CAST(job_end - job_start) AS TIME(0))

For Time Duration:

SUBSTRING(CONVERT(VARCHAR(20),(END_DATE-START_DATE),120),12,8)

Similarly you can compute for Second, Minute, Hour, Month, Year differences.

Syntax for using DATEDIFF:

DATEDIFF ( datepart , startdate , enddate )

For your Reference:

DATEDIFF(SECOND, GETDATE(), GETDATE() + 1) AS SecondDifference
DATEDIFF(MINUTE, GETDATE(), GETDATE() + 1) AS MinuteDifference
DATEDIFF(HOUR, GETDATE(), GETDATE() + 1) AS HourDifference
DATEDIFF(DAY, GETDATE(), GETDATE() + 1) AS DayDifference
DATEDIFF(WEEK, GETDATE(), GETDATE() + 1) AS WeekDifference
Bharath theorare
  • 524
  • 7
  • 27