Current Date

[deucesp97]

Does anyone know how I can perform function in SQL that will subtract the current date from a date in a specific column. What I really want to do is a date difference function and then update the difference to a column.

Any suggestions?


[sig][/sig]

 

[THoey]

I am sure this varies from SQL to SQL, but in Oracle, you can subtract or add dates together:

SQL>r
  1* select to_date('11-SEP-00') - SYSDATE from dual

TO_DATE('11-SEP-00')-SYSDATE
----------------------------
                   -9.621343

Hope this helps...
[sig]<p>Terry M. Hoey<br><a href=mailto:th3856@txmail.sbc.com>th3856@txmail.sbc.com</a><br><a href= > </a><br>Ever notice that by the time that you realize that you ran a truncate script on the wrong instance, it is too late to stop it?[/sig]

 

[THoey]

Woops... You can not add dates to dates. You can add numbers to dates.
[sig]<p>Terry M. Hoey<br><a href=mailto:th3856@txmail.sbc.com>th3856@txmail.sbc.com</a><br><a href= > </a><br>Ever notice that by the time that you realize that you ran a truncate script on the wrong instance, it is too late to stop it?[/sig]

 

[carp]

So, to update a second column, you could do:

UPDATE my_table SET second_column = first_column - SYSDATE;

Of course, if you want to avoid updating every row every time you run this, you would need to add a WHERE clause - something like:
WHERE second_column IS NULL

Again, this is for Oracle. The same logic should work in just about any RDBMS - you just need to find the equivilent of SYSDATE. [sig][/sig]

 

[deucesp97]

My question is that when I update that column does it need to be of a date type, I currently have the column as a character type and I am receiving and error:

New value has wrong data type.

I am using a DB2 database.

[sig][/sig]

 

[carp]

Since the difference between two dates is a number, I think that is the datatype I would try! [sig][/sig]

 

[bigscouse]

If the colum to be updated has to be character then you will need to do something like this (ingres version): -

UPDATE tablea a
FROM tableb b
SET char_column = varchar(b.date_column - date('today'))

I am a little rusty but it is along those lines. [sig]<p>Ged Jones<br><a href=mailto:gedejones@hotmail.com>gedejones@hotmail.com</a><br><a href= > </a><br>Top man[/sig]

 

[carp]

And in Oracle it would be
to_char(columnA - SYSDATE) [sig][/sig]

 

[deucesp97]

Is there a similar way to do a datediff function in DB2? [sig][/sig]

 

[karluk]

I think you are fairly close to what you need. An expression like
'2000-12-25' - current date
is valid in DB2. It returns the number of days until Christmas.

Probably your problem is that you are using this date calculation to update a column of the wrong data type. You need to either store the result in a column of the right data type or do a conversion. For example if you want to update a character column use the char function:

char('2000-12-25' - current date) [sig][/sig]

 

[deucesp97]

I have the column currently set as a character datatype and I am receiving the error, new value has wrong data type. I don't understand why character would be the wrong data type, should I try integer? [sig][/sig]

 

[carp]

As previously stated, the result will be a NUMBER! Numbers are NOT characters.
An integer will probably give you problems also, since if you subtract midnight,yesterday from noon,today, the result is 1.5 which is not an integer. [sig][/sig]

 

[deucesp97]

Is NUMBER a valid DB2 datatype? I am not sure? [sig][/sig]

 

[carp]

I don't know - I'm an Oracle grunt. Hopefully you have some documentation that will tell you. [sig][/sig]

 

[karluk]

According to the DB2 documentation, the result of this type of date calculation has datatype DECIMAL(8,0). You can store the result in any column that has this format or one that is compatible. You can also perform an explicit data type conversion, for example using the CHAR function.

The 8 digit decimal is in YYYYMMDD format, so you will have to interpret the result. By the way, that also means that my previous post was incorrect. '2000-12-25' - current_date evaluates to 304. That means three months and four days, not 304 days. Similarly '2001-12-25' - current_date evaluates to 10304, meaning one year, three months, and four days. [sig][/sig]

 

[deucesp97]

THANKS so much for all of your help, I am going to try the DECIMAL datatype. I really appreciate it. [sig][/sig]

 

[karluk]

I can see lots of problems storing the date difference in yyyymmdd format. Because of the variable number of days in a month there is no way of going back and figuring out exactly how many days are represented by the calculate value.

If you want to calculate the difference in days, try the DAYS function:

days('2000-12-25') - days(current_date) evaluates to 95, which really is the number of days until Christmas (today not included). I haven't checked the documentation, but I think this result is an integer. [sig][/sig]