Click here to Skip to main content
12,295,482 members (57,478 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 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 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
 
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 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.160525.2 | Last Updated 6 Jan 2013
Copyright © CodeProject, 1999-2016
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