-2

I have a field of dates stored as DDDDMMYY and want to calculate the number of days between this date and the current date.

I am using WINSQL and DB2.

I get a result using the below statement

days(curdate())-days(date(substr(lsdate+100000000,2,4)||'-'||substr(lsdate+100000000,6,2)||'-'||substr(lsdate+100000000,8,2)))

but is it possible to use this as a WHERE clause, ie

WHERE days(curdate())-days(date(substr(lsdate+100000000,2,4)||'-'||substr(lsdate+100000000,6,2)||'-'||substr(lsdate+100000000,8,2))) < 10
Peter Schuetze
  • 16,185
  • 4
  • 44
  • 58
user2990696
  • 1
  • 2
  • Add the technology you are using to the tags please. – ddoor Nov 14 '13 at 06:00
  • why does anyone in the world store a date value NOT in a date typed field?!? – Peter Miehle Nov 14 '13 at 08:48
  • what is the difference (you expect) between 3112(19)99 and 0101(20)00 or between 0101(19)50 and 0101(20)49? – Peter Miehle Nov 14 '13 at 08:50
  • "but is it possible to use this as a WHERE clause, ie `WHERE days(curdate())-days(date(substr(lsdate+100000000,2,4)||'-'||substr(lsdate+100000000,6,2)||'-'||substr(lsdate+100000000,8,2))) < 10`" -- Is there any reason not to try this out yourself. Then you should now if it works in a where clause. ... or did you intent to ask a different question? – Peter Schuetze Dec 10 '13 at 19:46

3 Answers3

0

try:

SELECT DATEDIFF('d', 'date_column', GetDate()) as difference;

SQL has operations to do on date columns and the GetDate() function should give the current date.

http://www.w3schools.com/sql/func_datediff.asp

ddoor
  • 5,819
  • 9
  • 34
  • 41
0

if you are trying in c# then You can use Total days property for get total day between two date.

int totalDays= (firstDate - secondDate).TotalDays;
J R B
  • 2,069
  • 4
  • 17
  • 32
0

with DDMMYY you are not able to calculate the difference, unless you have some side restrictions. even if you say, the value stored is a birthdate (so it is less than the current date) you have a problem.

say today is 141113 and you want to calculate the difference against 131112. Is the result "1 year and 1 day" or is it "101 years and 1 day". will you send a "welcome-to-the-world package" or a "make-the-rest-of-your-life-happy package"?

Peter Miehle
  • 5,984
  • 2
  • 38
  • 55