Click here to Skip to main content
12,826,676 members (31,067 online)
Rate this:
Please Sign up or sign in to vote.
See more: C# SQL-Server
how to get difference between two dates in sql server from two different columns and then also store the difference in another column..

lets say .. column 1 has date 1,
column 2 has date 2,

now sql find their differnce i:e , (date2 - date 1) and store the difference in column 3 ..

how can i do this ?
Posted 6-Jan-13 11:15am
Rate this: bad
Please Sign up or sign in to vote.

Solution 1

update mytable set col3 = datediff(datepart, date1, date2)

syed armaan hussain 6-Jan-13 16:23pm
is it neccessary that dates in sql server should be in datetime data type ??? what if i saved it as varchar data type ?? will the date diff function will not work with varchar data type ?
Christian Graus 6-Jan-13 16:26pm
you can cast it to a datetime first, but you should never do this, it's retarded. Store dates as dates, so that you know they are valid dates ( and not random strings ) and so the DB can work efficiently with them.
syed armaan hussain 6-Jan-13 16:44pm
thanks alot
Rate this: bad
Please Sign up or sign in to vote.

Solution 2

There's a rule of thumb that in an OLTP database, you should never store values that are calculated.

If you need the difference between two dates in your queries, you have lot's of other (better) options. For example:
- calculate the difference in the SELECT statement
- create a view for the table where the calculation is performed
- create a calculated column in the table and so on

The problem you're facing if you store calculated values is that you have to make 100% sure that every time any of the dates is modified, the result of the calculation is updated correspondingly. This requires extra work in the database and in most cases, for reliability, cannot be left for client program responsibility.
Christian Graus 6-Jan-13 17:30pm
Yes, I agree, this is also a good point. But sometimes you have to denormalise a database for performance reasons
Mika Wendelius 6-Jan-13 18:07pm
That's true, but if it can be avoided, I think it should :)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

    Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy | Mobile
Web02 | 2.8.170326.1 | Last Updated 6 Jan 2013
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100