Hi,
I am struggling with something that I believe should be quite simple.
I would like to convert the current date to a serial date in a query to compare with a serialized date and exclude records before this date.
I have tried variations of current(date) but this seems to be an issue with extracting the /'s mid body.
Does anyone have a simple method of doing this, or even a not so simple one.
I need to try and have this work within Query400 though, as I have a number of queries that need this treatment.
Thanks.
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
Hi
another way is by a view in sql
CREATE VIEW mylib/myview AS SELECT * FROM mylib/myfile WHERE mydatefield = current date
you can then:
runqry *n mylib/myview
which will only display records for toays date.
I have a set of UDF's that will convert numerics in to date fields if the fields are not held as date formats although thos does have a performance impact.
Dave
Business Accounts
Answer for Membership
by: Gary_The_IT_ProPosted on 2009-09-29 at 07:04:59ID: 25449136
You just need to get both dates into the same format.
CURRENT(DATE) returns a DATE field (DDS type "L"). If the dates in your table are stored in "L" format, then a simple comparison will work. If they are in a different format, then you will need to convert one side of the equation to match the other.
For example, it is fairly common to store dates in a numeric field in yyyymmdd format. You either need to convert this to DATE format, or convert CURRENT(DATE) to numeric format.
Assume that the job date format is MDY (USA style) and the date separator is "/", then:
DATE(SUBSTR(DIGITS(my
SUBSTR(DIGITS(myYYYYMMDD,7
SUBSTR(DIGITS(myYYYYMMDD,3
If is is stored in a character field in the same format, you just drop all of the DIGITS functions.
Of course, You'll have to rearrange and substitute correct date separators to match your job date.
To go the other way (DATE to numeric), you could:
(YEAR(CURRENT(DATE))
(MONTH(CURRENT(DATE))*100
(DAY(CURRENT(DATE)))
The functions that you need to use for date conversions typically include:
DATE
CHAR
SUBSTR
DIGI
YEAR
MONT
DAY
If you need more help, post the data type and format of the dates in your table, please, and we can give you specific advice.
- Gary Patterson
Check out my EE profile: http://www.experts-exchang