Click here to Skip to main content
Rate this: bad
good
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 10:15am
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

update mytable set col3 = datediff(datepart, date1, date2)
 
datediff[^]
  Permalink  
Comments
syed armaan hussain at 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 at 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 at 6-Jan-13 16:44pm
   
thanks alot
Rate this: bad
good
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.
  Permalink  
Comments
Christian Graus at 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 at 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
0 Sergey Alexandrovich Kryukov 766
1 OriginalGriff 452
2 sanket saxena 329
3 Tadit Dash 310
4 Peter Leow 228
0 Sergey Alexandrovich Kryukov 11,990
1 OriginalGriff 7,349
2 Peter Leow 5,003
3 Abhinav S 4,003
4 Maciej Los 3,575


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