Message from: Teleki
Thank you norm. I’m sorry about the typo. It was right in the query, but I
guess I didn something weird when pasting it. Thank you for the suggestion on
the storage of dates. In this case I didn’t have an option, because my
permissions in the db are very limited, but will keep it in mind for the future.
Keep it up guys! Really good thing you do!
Historical Messages
Author: Romualda García
Date: Wed Apr 06 21:43:46 PDT 2011
Thank you norm. I’m sorry about the typo. It was right in the query, but I
guess I didn something weird when pasting it. Thank you for the suggestion on
the storage of dates. In this case I didn’t have an option, because my
permissions in the db are very limited, but will keep it in mind for the future.
Keep it up guys! Really good thing you do!
__
Author: Norman Dunbar
Date: Mon Apr 04 00:32:39 PDT 2011
Morning Teleki, >> Thank you! I am really learning a lot through these posts, >>
not only about my particular query but of Oracle in general! Ok, here’s one that
will stand you in good stead for the future - when you need to store a data or a
time then use a DAT data type, not a number, not a varchar. You also have the
choice of TIMESTAMP and/or TIMESTAMP WITH TIMEZONE too. Dates stored as
character strings, depending on the format, cannot be sorted for example - is
01042011 bigger or smaller than 02011922? The optimizer may have a better chance
of creating a more efficient execution plan when it knows that a date is a date
and not a string. With your dates as strings, you will need to convert them to
strings to compare them. If you put the conversion of the column, any indexes on
that column can no longer be used (unless, possibly, they are function based
indexes). So, always store a data item in a data type that matches what that
item of data is. Dates are dates, strings are strings, never the twain shall
meet. >> So for future reference, I found out that my problem was the >> string
containing the date could be of either 7 or 8 >> characters long. Everyting got
solved using a previous >> padding. I leave here the result in case it is useful
for >> someone some day >> >> To_DATE((LPAD(Mat_date,8’0’)),‘mmddyyyy’) AS
NewMatDate Slight typo there! It should have a comma before ‘0’ in the LPAD
call: To_DATE((LPAD(Mat_date,8,‘0’)),‘mmddyyyy’) AS NewMatDate Cheers, Norm.
[TeamT] Information in this message may be confidential and may be legally
privileged. If you have received this message by mistake, please notify the
sender immediately, delete it and do not copy it to anyone else. We have checked
this email and its attachments for viruses. But you should still check any
attachment before opening it. We may have to make this message and any reply to
it public if asked to under the Freedom of Information Act, Data Protection Act
or for litigation. Email messages and attachments sent to or from any
Environment Agency address may also be accessed by someone other than the sender
or recipient, for business purposes. If we have sent you information and you
wish to use it please read our terms and conditions which you can get by calling
us on 08708 506 506. Find out more about the Environment Agency at
www.environment-agency.gov.uk
__
Author: Romualda García
Date: Fri Apr 01 07:39:42 PDT 2011
Awesome guys!
Thank you! I am really learning a lot through these posts, not only about my
particular query but of Oracle in general! So for future reference, I found out
that my problem was the string containing the date could be of either 7 or 8
characters long. Everyting got solved using a previous padding. I leave here the
result in case it is useful for someone some day
To_DATE((LPAD(Mat_date,8’0’)),‘mmddyyyy’) AS NewMatDate
Thanks everyone!
__
Author: Jeff Smith
Date: Fri Apr 01 07:39:42 PDT 2011
And going the other direction, from date to string
gh-days-and-holidays/
__
Author: Romualda García
Date: Fri Apr 01 07:39:42 PDT 2011
Message from: Teleki Awesome guys! Thank you! I am really learning a lot through
these posts, not only about my particular query but of Oracle in general! So for
future reference, I found out that my problem was the string containing the date
could be of either 7 or 8 characters long. Everyting got solved using a previous
padding. I leave here the result in case it is useful for someone some day
To_DATE((LPAD(Mat_date,8’0’)),‘mmddyyyy’) AS NewMatDate Thanks
everyone! _______________________________________ Historical Messages Author:
Romualda García Date: Fri Apr 01 07:39:42 PDT 2011 Awesome guys! Thank you! I
am really learning a lot through these posts, not only about my particular query
but of Oracle in general! So for future reference, I found out that my problem
was the string containing the date could be of either 7 or 8 characters long.
Everyting got solved using a previous padding. I leave here the result in case
it is useful for someone some day
To_DATE((LPAD(Mat_date,8’0’)),‘mmddyyyy’) AS NewMatDate Thanks
everyone! __ Author: Norman Dunbar Date: Fri Apr 01 06:51:33 PDT 2011 Erwin,
still retired eh? It’s TO_DATE(). Cheers, Norm. [TeamT] Information in this
message may be confidential and may be legally privileged. If you have received
this message by mistake, please notify the sender immediately, delete it and do
not copy it to anyone else. We have checked this email and its attachments for
viruses. But you should still check any attachment before opening it. We may
have to make this message and any reply to it public if asked to under the
Freedom of Information Act, Data Protection Act or for litigation. Email
messages and attachments sent to or from any Environment Agency address may also
be accessed by someone other than the sender or recipient, for business
purposes. If we have sent you information and you wish to use it please read our
terms and conditions which you can get by calling us on 08708 506 506. Find out
more about the Environment Agency at www.environment-agency.gov.uk __ Author:
21bobdog Date: Fri Apr 01 06:24:14 PDT 2011 First of all a date field has no
format. It is a date field. So what you need to do is convert to a date your
string field. For example assuming your string field containing a date is called
ABC you would do the following in a WHERE clause WHERE to_date(ABC,‘mmddyyyy’) >
SYSDATE or WHERE to_date(ABC,‘mmddyyyy’) = trunc(SYSDATE) PS I have been retired
for 2 months and have not done any Oracle so I can’t remember whether it is
TODATE or TO_DATE When comparing strings with dates always convert the string to
a date. NEVER try to convert a date to a date, it is already a date, by trying
to convert it you can actually cause a major bug. ETRG (Erwin The Retired Guy)
________________________________ __ Author: SCHROEDER, NATHAN E [AG/1000] Date:
Fri Apr 01 05:38:32 PDT 2011 RIGHT(string,n) is in Oracle SUBSTR(string,-n,n)
– the negative number means count from the end of the string. Try this
formula: to_date(substr( ‘0’ ||dt ,- 8 , 8 ), ‘mmddyyyy’ ) Nate Schroeder
Enterprise Services - Data Management Team Monsanto Company 800 N. Lindbergh
Blvd. LC4D - Saint Louis, MO - 63167 314-694-2592 __ Author: Phyllis Helton
Date: Fri Apr 01 05:21:48 PDT 2011 Because your “date” field is a
character field, you’ll want to convert your date to a character in the
same format. If you convert your date field to a real date you will lose the
benefit of your indexes and slow the query down. It ends up being a simple
query: Select field_name From table_name Where date_field =
‘4012011’; If you are trying to compare it to the system date or a
date field, then you’ll have to convert that date. This again is easy.
Select field_name From table_name Where date_field = to_char(sysdate,
‘MDDYYYY’); __ Author: Steve Booth Date: Fri Apr 01 05:17:51 PDT
2011 If talking about the second paragraph (I believe that this is a typo)…
WHERE MyDate = To_Date(MyVarchar, ‘MMDDYYY’) or (if no typo): WHERE MyDate =
To_Date(MyVarchar, ‘MMDDYYYY’) Steve Booth __ Author: Wim de lange Date: Fri Apr
01 00:14:23 PDT 2011 Use the TO_DATE function TO_DATE(myVarChar,
‘mmddyyyy’) to transfrom your string to a date and compare it with the
date. And you can emulate a right function using SUBSTR with the correct
parameters. Groetjes, Wim On Fri, Apr 1, 2011 at 02:09, Romualda García
wrote: Message from: Teleki Hi everyone, I am pretty sure this question has
been asked before, but I’ve been going through the blog for quite a while
and can’t seem to find anything! Here is my problem. I am trying to compare
a field in my database with acertain date, but the field is a varchar in the
format mddyyyy, withoutcomas or bars or anything, and I need to campare it with
a date of the format mm/dd/yyy. In Excel I used a quite complicated formula:
Right(‘0000000000’ & [Maturity_Date],8) AS [New Mat #], CDate(Left([New
Mat #],2) & “/” & Mid([New Mat #],3,2) & “/” & Right([New Mat #],4)) AS [Mat
Date], But, to start with, Oracle doesn’t seem to have the “right”
operator. Anyone could give me an idea. Thanks for your time!!
_______________________________________ Historical Messages Author: Romualda
García Date: Thu Mar 31 17:09:36 PDT 2011 Hi everyone, I am pretty sure this
question has been asked before, but I’ve been going through the blog for
quite a while and can’t seem to find anything! Here is my problem. I am
trying to compare a field in my database with acertain date, but the field is a
varchar in the format mddyyyy, withoutcomas or bars or anything, and I need to
campare it with a date of the format mm/dd/yyy. In Excel I used a quite
complicated formula: Right(‘0000000000’ & [Maturity_Date],8) AS [New Mat
#], CDate(Left([New Mat #],2) & “/” & Mid([New Mat #],3,2) & “/” & Right([New
Mat #],4)) AS [Mat Date], But, to start with, Oracle doesn’t seem to have
the “right” operator. Anyone could give me an idea. Thanks for your
time!! __ _______________________________________ __ Author: Romualda García
Date: Thu Mar 31 17:09:36 PDT 2011 Hi everyone, I am pretty sure this question
has been asked before, but I’ve been going through the blog for quite a
while and can’t seem to find anything! Here is my problem. I am trying to
compare a field in my database with acertain date, but the field is a varchar in
the format mddyyyy, withoutcomas or bars or anything, and I need to campare it
with a date of the format mm/dd/yyy. In Excel I used a quite complicated
formula: Right(‘0000000000’ & [Maturity_Date],8) AS [New Mat #],
CDate(Left([New Mat #],2) & “/” & Mid([New Mat #],3,2) & “/” & Right([New Mat
#],4)) AS [Mat Date], But, to start with, Oracle doesn’t seem to have the
“right” operator. Anyone could give me an idea. Thanks for your time!!
__
Author: Norman Dunbar
Date: Fri Apr 01 06:51:33 PDT 2011
Erwin, still retired eh? It’s TO_DATE(). Cheers, Norm. [TeamT] Information in
this message may be confidential and may be legally privileged. If you have
received this message by mistake, please notify the sender immediately, delete
it and do not copy it to anyone else. We have checked this email and its
attachments for viruses. But you should still check any attachment before
opening it. We may have to make this message and any reply to it public if asked
to under the Freedom of Information Act, Data Protection Act or for litigation.
Email messages and attachments sent to or from any Environment Agency address
may also be accessed by someone other than the sender or recipient, for business
purposes. If we have sent you information and you wish to use it please read our
terms and conditions which you can get by calling us on 08708 506 506. Find out
more about the Environment Agency at www.environment-agency.gov.uk
__
Author: 21bobdog
Date: Fri Apr 01 06:24:14 PDT 2011
First of all a date field has no format. It is a date field. So what you need to
do is convert to a date your string field. For example assuming your string
field containing a date is called ABC you would do the following in a WHERE
clause WHERE to_date(ABC,‘mmddyyyy’) > SYSDATE or WHERE to_date(ABC,‘mmddyyyy’)
= trunc(SYSDATE) PS I have been retired for 2 months and have not done any
Oracle so I can’t remember whether it is TODATE or TO_DATE When comparing
strings with dates always convert the string to a date. NEVER try to convert a
date to a date, it is already a date, by trying to convert it you can actually
cause a major bug. ETRG (Erwin The Retired Guy) ________________________________
__
Author: SCHROEDER, NATHAN E [AG/1000]
Date: Fri Apr 01 05:38:32 PDT 2011
RIGHT(string,n) is in Oracle SUBSTR(string,-n,n) – the negative number
means count from the end of the string. Try this formula: to_date(substr( ‘0’
||dt ,- 8 , 8 ), ‘mmddyyyy’ ) Nate Schroeder Enterprise Services - Data
Management Team Monsanto Company 800 N. Lindbergh Blvd. LC4D - Saint Louis, MO -
63167 314-694-2592
__
Author: Phyllis Helton
Date: Fri Apr 01 05:21:48 PDT 2011
Because your “date” field is a character field, you’ll want to
convert your date to a character in the same format. If you convert your date
field to a real date you will lose the benefit of your indexes and slow the
query down. It ends up being a simple query: Select field_name From table_name
Where date_field = ‘4012011’; If you are trying to compare it to the
system date or a date field, then you’ll have to convert that date. This
again is easy. Select field_name From table_name Where date_field =
to_char(sysdate, ‘MDDYYYY’);
__
Author: Steve Booth
Date: Fri Apr 01 05:17:51 PDT 2011
If talking about the second paragraph (I believe that this is a typo)… WHERE
MyDate = To_Date(MyVarchar, ‘MMDDYYY’) or (if no typo): WHERE MyDate =
To_Date(MyVarchar, ‘MMDDYYYY’) Steve Booth
__
Author: Wim de lange
Date: Fri Apr 01 00:14:23 PDT 2011
Use the TO_DATE function TO_DATE(myVarChar, ‘mmddyyyy’) to transfrom
your string to a date and compare it with the date. And you can emulate a right
function using SUBSTR with the correct parameters. Groetjes, Wim On Fri,
Apr 1, 2011 at 02:09, Romualda García wrote: Message from: Teleki Hi
everyone, I am pretty sure this question has been asked before, but I’ve
been going through the blog for quite a while and can’t seem to find
anything! Here is my problem. I am trying to compare a field in my database with
acertain date, but the field is a varchar in the format mddyyyy, withoutcomas or
bars or anything, and I need to campare it with a date of the format mm/dd/yyy.
In Excel I used a quite complicated formula: Right(‘0000000000’ &
[Maturity_Date],8) AS [New Mat #], CDate(Left([New Mat #],2) & “/” & Mid([New
Mat #],3,2) & “/” & Right([New Mat #],4)) AS [Mat Date], But, to start with,
Oracle doesn’t seem to have the “right” operator. Anyone could
give me an idea. Thanks for your time!! _______________________________________
Historical Messages Author: Romualda García Date: Thu Mar 31 17:09:36 PDT 2011
Hi everyone, I am pretty sure this question has been asked before, but I’ve
been going through the blog for quite a while and can’t seem to find
anything! Here is my problem. I am trying to compare a field in my database with
acertain date, but the field is a varchar in the format mddyyyy, withoutcomas or
bars or anything, and I need to campare it with a date of the format mm/dd/yyy.
In Excel I used a quite complicated formula: Right(‘0000000000’ &
[Maturity_Date],8) AS [New Mat #], CDate(Left([New Mat #],2) & “/” & Mid([New
Mat #],3,2) & “/” & Right([New Mat #],4)) AS [Mat Date], But, to start with,
Oracle doesn’t seem to have the “right” operator. Anyone could
give me an idea. Thanks for your time!! __
__
Author: Romualda García
Date: Thu Mar 31 17:09:36 PDT 2011
Hi everyone,
I am pretty sure this question has been asked before, but I’ve been going
through the blog for quite a while and can’t seem to find anything!
Here is my problem. I am trying to compare a field in my database with acertain
date, but the field is a varchar in the format mddyyyy, withoutcomas or bars or
anything, and I need to campare it with a date of the format mm/dd/yyy.
In Excel I used a quite complicated formula:
Right(‘0000000000’ & [Maturity_Date],8) AS [New Mat #],
CDate(Left([New Mat #],2) & “/” & Mid([New Mat #],3,2) & “/” & Right([New Mat
#],4)) AS [Mat Date],
But, to start with, Oracle doesn’t seem to have the “right”
operator.
Anyone could give me an idea.
Thanks for your time!!
__
