If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.
I think MartinTosney is right. Your database table is using EDTWRD or similar to transform the value for display. So if the table holds the number 1191231 then query displays it as 119/12/31.
But when comparing the value using SQL, then you need to match the stored value 1191231, not the display transformed value 119/12/31.
So I still think this method will work.
select * from myfile where field = (int(current date) - 19000000);
I think MartinTosney is right. Your database table is using EDTWRD or similar to transform the value for display. So if the table holds the number 1191231 then query displays it as 119/12/31.
But when comparing the value using SQL, then you need to match the stored value 1191231, not the display transformed value 119/12/31.
So I still think this method will work.
select * from myfile where field = (int(current date) - 19000000);
Have you tried it?
Thanks I tried it but currently i did not get any data for this sql query and output showed like " No Data selected for output"
When you say "But when comparing the value using SQL, then you need to match the stored value 1191231, not the display transformed value 119/12/31."
do you mean to say this with respect to program or in which terms i did not understand kindly clarify.
Because int(current date):returns the current date as a number in YYYYMMDD format. Your date column is a number in CYYMMDD format. Subtracting 19000000 is a quick and easy way to convert from YYYYMMDD numeric to CYYMMDD numeric.
"current date" is a SQL keyword that returns the current system date in the date data type
int(x) converts x to an integer numeric. Where x is a date, int(x) would convert the date to a number, in format YYYYMMDD
E.g. if the current date is 31st December 2021, int(current date) would return the number 20211231
CYYMMDD date format uses a single digit to represent the century, where 20th century (1900-1999) = 0 and 21st century (2000-2099) = 1.
E.g. if the current date is 31st December 2021, in numeric CYYMMDD it would be 1211231
20211231 minus 1211231 = 19000000. This is true for any date - when the dates are both stored as numbers, the difference between the YYYYMMDD and CYYMMDD is always 19000000.
So converting the current date to a number in YYYYMMDD format and then subtracting 19000000 is a quick and easy way to convert to numeric CYYMMDD
no it's just defined as numeric field with 7 length and zero decimal position and if the same date is in C,YYM,MDD format for example 1,211,217 then how we will fetch for current date's records from same file?
Vectorspace already gave you the query for this in post #19, and you even quote it in post #22. He's also kindly explained the logic in each part of the conversion, from the current date to a 7-digit numeric representation.
Or add 19000000 to your CYYMMDD field in your code to match the YYYYMMDD in current_date. Then it might be easier if you need to calculate # of days between dates.
no it's just defined as numeric field with 7 length and zero decimal position and if the same date is in C,YYM,MDD format for example 1,211,217 then how we will fetch for current date's records from same file?
thanks...
I think we gave you already almost all alternatives for converting a 7 digit numeric date into a real date for to compare it with the current date.
Please read the answers you got before instead of posting the same question over and over again.
Comment