- Community Home
- >
- Servers and Operating Systems
- >
- Operating Systems
- >
- Operating System - HP-UX
- >
- Re: select between returns funny results
Operating System - HP-UX
1753710
Members
4579
Online
108799
Solutions
Forums
Categories
Company
Local Language
юдл
back
Forums
Discussions
Forums
- Data Protection and Retention
- Entry Storage Systems
- Legacy
- Midrange and Enterprise Storage
- Storage Networking
- HPE Nimble Storage
Discussions
Discussions
Discussions
Forums
Forums
Discussions
юдл
back
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
- BladeSystem Infrastructure and Application Solutions
- Appliance Servers
- Alpha Servers
- BackOffice Products
- Internet Products
- HPE 9000 and HPE e3000 Servers
- Networking
- Netservers
- Secure OS Software for Linux
- Server Management (Insight Manager 7)
- Windows Server 2003
- Operating System - Tru64 Unix
- ProLiant Deployment and Provisioning
- Linux-Based Community / Regional
- Microsoft System Center Integration
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Blogs
Information
Community
Resources
Community Language
Language
Forums
Blogs
Go to solution
Topic Options
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО05-02-2006 09:44 AM
тАО05-02-2006 09:44 AM
I have data that is a char field but holds dates.
It looks like this.
20060428AprFri095748095748AM
20060428AprFri103723103723AM
20060428AprFri104758104758AM
20060428AprFri162216042216PM
20060430AprSun010335010335AM
20060501MayMon101308101308AM
20060501MayMon121534121534PM
20060501MayMon134633014633PM
20060501MayMon145649025649PM
20060501MayMon173210053210PM
20060502MayTue145323025323PM
20060502MayTue145831025831PM
20060502MayTue150607030607PM
20060502MayTue151447031447PM
20060502MayTue155103035103PM
yada...
I want to run a select for: between 20060430 and 20060501
But I need to get more detailed in the time stamp, like 000000 and 235959
As you can see, it has the time stamp to show:
YYYYMMDDMonDay then
HHMMSI24HHMMSIAM
So the same time is displayed in 22hr and 12AM/PM
I can run this query:
SELECT
COMPLETED_DT
FROM TABLE
WHERE TABLE.COMPLETED_DT BETWEEN '20060428' AND '20060504'
Still returned results, when I would have thought I would need a wild card.
I tried using the ' - ' So I would queury on 20060428------16% but I still got everything.
This is on an Oracle 7 database
It looks like this.
20060428AprFri095748095748AM
20060428AprFri103723103723AM
20060428AprFri104758104758AM
20060428AprFri162216042216PM
20060430AprSun010335010335AM
20060501MayMon101308101308AM
20060501MayMon121534121534PM
20060501MayMon134633014633PM
20060501MayMon145649025649PM
20060501MayMon173210053210PM
20060502MayTue145323025323PM
20060502MayTue145831025831PM
20060502MayTue150607030607PM
20060502MayTue151447031447PM
20060502MayTue155103035103PM
yada...
I want to run a select for: between 20060430 and 20060501
But I need to get more detailed in the time stamp, like 000000 and 235959
As you can see, it has the time stamp to show:
YYYYMMDDMonDay then
HHMMSI24HHMMSIAM
So the same time is displayed in 22hr and 12AM/PM
I can run this query:
SELECT
COMPLETED_DT
FROM TABLE
WHERE TABLE.COMPLETED_DT BETWEEN '20060428' AND '20060504'
Still returned results, when I would have thought I would need a wild card.
I tried using the ' - ' So I would queury on 20060428------16% but I still got everything.
This is on an Oracle 7 database
Solved! Go to Solution.
3 REPLIES 3
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО05-02-2006 01:11 PM
тАО05-02-2006 01:11 PM
Solution
Hi LHradowy,
You can select the COMPLETED_DT from the table with the comparison operator 'like' with the pattern match character '_' or '%'.
The '_' in the pattern match matches exactly one character, and '%' can match zero or more characters except null.
Here are few examples that I tried with your data.
Select the records that has COMPLETED_DT between '20060430' and '20060501'. As you can see there is only one record. If you need 20060501 included then give bweteen '20060430' and '20060502'
SQL> select COMPLETED_DT from table where COMPLETED_DT between '20060430' and '20060501';
COMPLETED_DT
----------------------------------------
20060430AprSun010335010335AM
Select the records that has COMPLETED_DT starts from 20060428, i.e that date and any time for that date. As you can see your data has 3 records with Date 20060428 and different times. Here is the output.
SQL> select COMPLETED_DT from table where COMPLETED_DT like '20060428%';
COMPLETED_DT
----------------------------------------
20060428AprFri103723103723AM
20060428AprFri104758104758AM
20060428AprFri162216042216PM
Select the records that has COMPLETED_DT between ├в 20060430├в and ├в 20060501├в in a formatted way like date and time components
SQL> select substr(cdt,1,14)||' '||substr(cdt,21,8) from indira where cdt between '20060430%' and '20060501%';
SUBSTR(COMPLETED_DT
------------------------
20060430AprSun 010335AM
Select the records that has COMPLETED_DT between ├в 20060430├в and ├в 20060501├в in a more formatted way to get the date, Month, Week-day and time separately.
SQL> select substr(COMPLETED_DT,1,8)||' '||substr(COMPLETED_DT,9,3)||' '||
substr(COMPLETED_DT,12,3)||' '||substr(COMPLETED_DT,21,2)||':'||
substr(COMPLETED_DT,23,2)||':'||substr(COMPLETED_DT,25,2)
from table where COMPLETED_DT between '20060430%' and '20060501%'
SUBSTR(COMPLETED_DT
--------------------------
20060430 Apr Sun 01:03:35
I hope this helps
Indira A
You can select the COMPLETED_DT from the table with the comparison operator 'like' with the pattern match character '_' or '%'.
The '_' in the pattern match matches exactly one character, and '%' can match zero or more characters except null.
Here are few examples that I tried with your data.
Select the records that has COMPLETED_DT between '20060430' and '20060501'. As you can see there is only one record. If you need 20060501 included then give bweteen '20060430' and '20060502'
SQL> select COMPLETED_DT from table where COMPLETED_DT between '20060430' and '20060501';
COMPLETED_DT
----------------------------------------
20060430AprSun010335010335AM
Select the records that has COMPLETED_DT starts from 20060428, i.e that date and any time for that date. As you can see your data has 3 records with Date 20060428 and different times. Here is the output.
SQL> select COMPLETED_DT from table where COMPLETED_DT like '20060428%';
COMPLETED_DT
----------------------------------------
20060428AprFri103723103723AM
20060428AprFri104758104758AM
20060428AprFri162216042216PM
Select the records that has COMPLETED_DT between ├в 20060430├в and ├в 20060501├в in a formatted way like date and time components
SQL> select substr(cdt,1,14)||' '||substr(cdt,21,8) from indira where cdt between '20060430%' and '20060501%';
SUBSTR(COMPLETED_DT
------------------------
20060430AprSun 010335AM
Select the records that has COMPLETED_DT between ├в 20060430├в and ├в 20060501├в in a more formatted way to get the date, Month, Week-day and time separately.
SQL> select substr(COMPLETED_DT,1,8)||' '||substr(COMPLETED_DT,9,3)||' '||
substr(COMPLETED_DT,12,3)||' '||substr(COMPLETED_DT,21,2)||':'||
substr(COMPLETED_DT,23,2)||':'||substr(COMPLETED_DT,25,2)
from table where COMPLETED_DT between '20060430%' and '20060501%'
SUBSTR(COMPLETED_DT
--------------------------
20060430 Apr Sun 01:03:35
I hope this helps
Indira A
Never give up, Keep Trying
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО05-02-2006 01:57 PM
тАО05-02-2006 01:57 PM
Re: select between returns funny results
Hi there,
Kindly re-read your question.
It is not entirely clear what you are trying to select.
At the very least indicate the intend rows to return.
It is specifically confusing to sea the description: between 20060430 and 20060501
and the actual command: BETWEEN '20060428' AND '20060504'
It seems pretty clear that this particular query should return all sample row shown no?
Like Indira I suspect you intend it to return just that single 4/30 row.
For that: BETWEEN '20060430' AND '20060504'
Those 'short' character values will effectively be expanded with all low values, and will thus match any time in the day.
If you put in BETWEEN '20060430' AND '20060501'
THen you get just the 4/30 records.
Now if you also wanted the 5/1 record you can ask for:
BETWEEN '20060430' AND '20060502'
or...
BETWEEN '20060430' AND '20060501Z'
That Z is higher then any first char for a Month and thus hight than any time to tday for any date.
Ditto on the other end.
If you specify: BETWEEN '20060430??????16' AND '20060504'
Then you are really effectively saying '20060430 16'.. which is lower than eny time because the first (most significant) Month char is lower then any real month.
If you really wanted the records for 4/28 after 16:00, then you pretty much must hard-code the redundant MonDay:
BETWEEN '20060428AprFri16' AND '20060501'
ps... my condolencences on the miserable data design you have to work with.
What a hoorible field, full of redundancies this is. What a lousy seperation of form and function (none). What terrible multi-lingual attributes. What a horrible missed opportunity in sorting order:
YYYYMMDDHHMMSI24MonDayHHMMSIAM would have made your query question trivial!
The Month and Day are entirely redundant with the date itself of coruse and can readily be calculated on the fly instead of being stored. Ditto for the 24H vs 12H time. Yikes.
As they say in France (and Canada I suppose :-) "Bon courage"... you'll need it with data like that.
Cheers,
Hein.
Kindly re-read your question.
It is not entirely clear what you are trying to select.
At the very least indicate the intend rows to return.
It is specifically confusing to sea the description: between 20060430 and 20060501
and the actual command: BETWEEN '20060428' AND '20060504'
It seems pretty clear that this particular query should return all sample row shown no?
Like Indira I suspect you intend it to return just that single 4/30 row.
For that: BETWEEN '20060430' AND '20060504'
Those 'short' character values will effectively be expanded with all low values, and will thus match any time in the day.
If you put in BETWEEN '20060430' AND '20060501'
THen you get just the 4/30 records.
Now if you also wanted the 5/1 record you can ask for:
BETWEEN '20060430' AND '20060502'
or...
BETWEEN '20060430' AND '20060501Z'
That Z is higher then any first char for a Month and thus hight than any time to tday for any date.
Ditto on the other end.
If you specify: BETWEEN '20060430??????16' AND '20060504'
Then you are really effectively saying '20060430 16'.. which is lower than eny time because the first (most significant) Month char is lower then any real month.
If you really wanted the records for 4/28 after 16:00, then you pretty much must hard-code the redundant MonDay:
BETWEEN '20060428AprFri16' AND '20060501'
ps... my condolencences on the miserable data design you have to work with.
What a hoorible field, full of redundancies this is. What a lousy seperation of form and function (none). What terrible multi-lingual attributes. What a horrible missed opportunity in sorting order:
YYYYMMDDHHMMSI24MonDayHHMMSIAM would have made your query question trivial!
The Month and Day are entirely redundant with the date itself of coruse and can readily be calculated on the fly instead of being stored. Ditto for the 24H vs 12H time. Yikes.
As they say in France (and Canada I suppose :-) "Bon courage"... you'll need it with data like that.
Cheers,
Hein.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО05-03-2006 03:57 AM
тАО05-03-2006 03:57 AM
Re: select between returns funny results
Yes, vendor apps can be a nightmare.
I knew I would have difficulty parsing down to the time field.
Appreciate all your help!
I knew I would have difficulty parsing down to the time field.
Appreciate all your help!
The opinions expressed above are the personal opinions of the authors, not of Hewlett Packard Enterprise. By using this site, you accept the Terms of Use and Rules of Participation.
News and Events
Support
© Copyright 2024 Hewlett Packard Enterprise Development LP