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 OriginalGriff 540
1 Sergey Alexandrovich Kryukov 422
2 Yogesh Kumar Tyagi 384
3 Prakriti Goyal 280
4 Maciej Los 205
0 OriginalGriff 6,632
1 Sergey Alexandrovich Kryukov 5,404
2 Maciej Los 3,474
3 Peter Leow 3,299
4 DamithSL 2,495


Advertise | Privacy | Mobile
Web01 | 2.8.140721.1 | Last Updated 6 Jan 2013
Copyright © CodeProject, 1999-2014
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