SQLTeam.com | Weblogs | Forums

How to Add Two DateTiime Columns

sql2014

#1

I have a column named Duration and for it's default value I have created a UDF that Adds two DateTime data value columns named StartDate and EndDate.

With my script below will it return the correct DateTime Value?

My Table

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [CompanyWorking].[WorkCompleted](
	[ID] [int] IDENTITY(212,1) NOT NULL,
	[InvestorSeekerID] [int] NOT NULL,
	[Team] [nvarchar](50) NOT NULL,
	[WorkingOn] [nvarchar](150) NOT NULL,
	[StartDate] [datetime] NOT NULL,
	[EndDate] [datetime] NOT NULL,
	[Percentage] [decimal](3, 2) NOT NULL,
	[Duration] [datetime] NULL,
	[Note] [nvarchar](150) NOT NULL,
	[Created] [datetime] NOT NULL,
 CONSTRAINT [PK_WorkCompleted] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [CompanyWorking].[WorkCompleted] ADD  CONSTRAINT [DF_WorkCompleted_Percentage]  DEFAULT ((0.00)) FOR [Percentage]
GO

ALTER TABLE [CompanyWorking].[WorkCompleted] ADD  CONSTRAINT [DF_WorkCompleted_Duration]  DEFAULT ([CompanyWorking].[GetDurationOfTimeSpent]()) FOR [Duration]
GO

ALTER TABLE [CompanyWorking].[WorkCompleted] ADD  CONSTRAINT [DF_WorkCompleted_Note]  DEFAULT (' ') FOR [Note]
GO

ALTER TABLE [CompanyWorking].[WorkCompleted] ADD  CONSTRAINT [DF_WorkCompleted_Created]  DEFAULT (getdate()) FOR [Created]
GO

ALTER TABLE [CompanyWorking].[WorkCompleted]  WITH CHECK ADD  CONSTRAINT [FK_WorkCompleted_InvestmentSeeker_ID_InvestmentSeekerID] FOREIGN KEY([InvestorSeekerID])
REFERENCES [CompanyWorking].[InvestmentSeeker] ([ID])
GO

ALTER TABLE [CompanyWorking].[WorkCompleted] CHECK CONSTRAINT [FK_WorkCompleted_InvestmentSeeker_ID_InvestmentSeekerID]
GO

UDF

CREATE FUNCTION CompanyWorking.GetDurationOfTimeSpent()
RETURNS DateTime
AS
BEGIN

	DECLARE @Duration DateTime

	SELECT @Duration = CAST(StartDate as DATETIME) + CAST(EndDate as DATETIME) FROM CompanyWorking.WorkCompleted

	-- Return the result of the function
	RETURN @Duration
END
GO

Will the UDF above return the correct date difference with the correct column value DateTime?


#2

See this stackoverflow post
Why do you add the dates? From the wording of your code it looks like you would like to know how long something took. You can use the datediff (datepart, startdate , enddate ) to calculate the difference between to dates.
Example:

Select datediff(day,'2017-01-01','2017-01-31')


#3

How about this? Yes it does get confusing to me from time to time working with dates.

Now that I look at it this is not correct either. If I do return the day, then my column value should be set to smallint?

ALTER FUNCTION [CompanyWorking].[GetDurationOfTimeSpent]()
RETURNS smallint
AS
BEGIN

	DECLARE @Duration smallint

	SELECT @Duration = datediff(day,StartDate, EndDate) FROM CompanyWorking.WorkCompleted

	-- Return the result of the function
	RETURN @Duration
END

#4

I don't think your UDF will do what you intend.

SELECT @Duration = datediff(day,StartDate, EndDate) FROM CompanyWorking.WorkCompleted

Will perform the DATEDIFF on every row in CompanyWorking.WorkCompleted ... and then the last row processed (which will be random) will be stored in @Duration (well ... all the rows will be, but when the SELECT finishes just the result of the last one will remain)

If you just want the DEFAULT for [Duration] to be the difference between [StartDate] and [EndDate], in Days, then you could just put that "formula" in the DEFAULT CONSTRAINT

ALTER TABLE [CompanyWorking].[WorkCompleted]
    ADD  CONSTRAINT [DF_WorkCompleted_Duration]
    DEFAULT  datediff(day, StartDate, EndDate) FOR [Duration]

Are you OK that this is just the default, when the row is very first INSERTed, provided that the INSERT statement does not, also, provide a value for [Duration]?

And also that the value of [Duration] is not automatically changed if the row is UPDATEd (and [StartDate] or [EndDate] are changed)?

Also, that [Duration] can be changed in an UPDATE without [StartDate] and/or [EndDate] being changed to match?

All that might be fine, I just wanted to check.

If you want a column for [Duration] in the table which always reflects the DataDiff of [StartDate] and [EndDate] then you might be better off with a computed column. You'll still have your [Duration] column in the table, but it won't be updatable and will always reflect the DateDiff() value.

Given that your [StartDate] and [EndDate] are DateTIME one other thing to be aware of is what "rounding" DateDiff(Day, ...) will perform. I expect it will be fine for your purpose, but worth just considering that

SELECT DateDiff(Day, '20170311 23:59:59', '20170312 00:00:01')
SELECT DateDiff(Day, '20170311 00:00:01', '20170312 23:59:59')

both return "one day"


#5

Kristen,

Thank you for your expanded proposal to this task that is before me!

Let me explain the purpose and the absolute end result and please advise me if this will produce the result I am attempting to achieve.

This table is for a specific Pie and Bar Chart graph.

I do not want to ever insert or update this attribute Duration, I want it to all be automation without fail.

When a Team is working on a specific Task this will be their StartDate when they've completed the task this will be their EndDate; moreover, when an Investor checks the progress of a specific Task and there is no EndDate I can only assume this will cause a fail due to the face that the UDF will try to access a null column;therefore, I can only assume that it would be wise to place a default value for the EndDate that is in the past before the StartDate to return a negative day.

Now that I think about it this is not logical as to what I am attempting to achieve. This attribute should not be called EndDate it should be call CurrentDate and give it a default value of (GETDATE()), This is the more logical way I should implement this.

Now, I ask of you, is this the proper way to implement this entity?

The only time I need to access the value of [Duration] is in a SELECT statement.

So to begin with this Entity for the first time I should Insert StartDate, for CurrentDate is will have a default value of (GETDATE()), and when I run a SELECT statement Duration will have a default value that is returned by the UDF of datediff(day,wc.StartDate, wc.CurrentDate) FROM CompanyWorking.WorkCompleted wc WHERE wc.Team = 'Web Design'.

And also the attribute Duration is OK to have a data value set to smallint?

Please advise if this sounds properly implemented to you.

Erik


#6

I think you are misunderstanding what a default value in a table does - all a default value does is provides a value if one is not provided during INSERT of the data to the table. Once the data has been inserted - the default value doesn't do anything...

It does not update the value in the column every time someone selects from the table.

Based on your description - you have 3 options:

  1. Create a computed column on the table that computes the value you want based on the existing columns in the table. Something like (datediff(hour, [StartDate], coalesce([EndDate], getdate()) / 24.0)

  2. Create a view and select from the view:

CREATE VIEW dbo.MyView AS
SELECT ...
     , datediff(hour, wc.StartDate, coalesce(wc.EndDate, getdate()) / 24.0 As Duration
  FROM yourTable
  1. Use the above SELECT statement in your stored procedure/queries to calculate the Duration every time you execute the procedure/query.

If you use option 1 - the data type for the Duration would be decimal and I would use at least decimal(5,2) to show the Duration depending on how long this could stay open. The calculation I use would break the Duration out to days and partial days (e.g. 1.25 days).

In the calculation above - we assume the EndDate column is NULL until a value has been defined and the table updated. Once the table is updated with the EndDate the calculation will use that value instead of the current date/time.

If you want more precision for calculating the duration you can use seconds - and if you want the value to be a part of the table you could store the integer value of the difference in seconds and then convert that value to a duration in days, hours, minutes, seconds for display.


#7

Yes, this is perfect, hours make much more logical sense thank you for advising this solution, because it is all about the hours in the first place.

Thank you for your expertise!