I have no control over how users insert.. what I want to see is how I can convert
select to_char( x, 'mm/dd/yyyy' ) from t; into a date with the date format mm/dd/yyyy
is that possible ?
I told you how. alter session. See also
</code> http://asktom.oracle.com/Misc/NLSDateFormat.html <code>
there you go -- and guess what, you DO in fact have control over how they insert -- as their inserts will NOT work in any arbitrary format (and lack of access is the ultimate in control). Hence, they will either need to alter session (bad idea) OR they will need to say what they mean (always a good idea)
TomSo if the Nls_date_format is set to 'dd-mon-yy' , not matter how you enter the date it will insert it in the same format i.e. dd-mon-yy..Is that what you are tryig to say.. I have tested this as below..SQL> create table t( x date);Table created.SQL> insert into t values(sysdate);1 row created.SQL> commit;Commit complete.SQL> select * from t;X---------08-FEB-03SQL> insert into t values(to_date('02/08/2003', 'mm/dd/yyyy') );1 row created.SQL> commit;Commit complete.SQL> select * from t;X---------08-FEB-0308-FEB-03SQL> insert into t values(to_date('2003/02/07','yyyy/mm/dd'));1 row created.SQL> commit;Commit complete.SQL> select * from t;X---------08-FEB-0308-FEB-0307-FEB-03So now if I want to start storing the date in my datbase in 'yyyy/mm/dd' format then I should set my nls_date format to 'yyyy/mm/dd. No, not at all:
<quote>
A date is stored in an internal 7 byte format that the machine likes to use
(bits and bytes).
For us poor humans, who cannot read the bits and bytes -- we have date formats.
The formats are EDITS applied to the bits and bytes that convert them from the
internal format (a DATE) to a string so we can read them.
</quote from above...>
the format string is just an edit, just an edit -- a format, nothing more, nothing less.
the date always has century, year, month, day, hour, minute, second -- regardless of the format used to insert it or retrieve it.
SQL> alter session set nls_date_format='yyyy-mm-dd' ;Session altered.SQL> select * from t;X----------2003-02-082003-02-082003-02-07insert into t values(to_date('02/08/2003', 'mm/dd/yyyy') );SQL> select * from t;X----------2003-02-082003-02-082003-02-072003-02-08How can I know the nls_date_format of our database? TomSQL> select length('dd-mon-yyyy') from dual;LENGTH('DD-MON-YYYY')--------------------- 11A dates internal format should take 11 bytes. How come according to you it is only 7 bytes...What is the format which enables storage of date in 7 bytes, please give insight into it... Nasser, that's really funny! But you could have gone further:SQL> select to_char( sysdate,'DAY DD MONTH YYYY HH:MM:SSAM') from dual 2 /TO_CHAR(SYSDATE,'DAYDDMONTHYYYYHH:MM:S--------------------------------------SATURDAY 08 FEBRUARY 2003 11:02:27PMSQL> select length(to_char( sysdate,'DAY DD MONTH YYYY HH:MM:SSAM')) from dual 2 /LENGTH(TO_CHAR(SYSDATE,'DAYDDMONTHYYYYHH:MM:SSAM'))--------------------------------------------------- 38And it STILL fits into 7 bytes!!! I guess that's the difference between INTERNAL formats and EXTERNAL formats for you!
My question in brief was...
What is the internal format in which the date is stored? and I was just giving an example.
I believe Oracle will store every portion of date ,
The Year (Full year)
Month
Date
Time (AM / PM indicator or 24 hours format)
Am I rigt Tom?
Regards
Nasser,
Sorry my previous answer was a little "tongue in cheek"!
Oracle DATE columns are stored internally as the number of seconds since some date way in the past. We have 7 bytes to store it in - how big is that?
1 byte = 8 bits. This can store numeric values up to (2**8)-1 = 255
7 bytes = 56 bits. This can store numeric values up to (2**56)-1 = 7.2058E+16, i.e. 72,058,000,000,000,000
That is the number of different DATE values, in seconds, there can be. How many years is that?
7.2058E+16 / 60 / 60 / 24 / 365 = 2,284,931,318
i.e. 2 billion years.
So with a 7-byte DATE column you could in theory record any date and time (to the second) from around 1 billion BC to around 1 billion AD.
I'm not saying that's EXACTLY how Oracle dates work, but it is similar, and shows how they could easily accomodate all the dates you'll ever need in 7 bytes or less.
see
</code> http://docs.oracle.com/docs/cd/A87860_01/doc/server.817/a76965/c10datyp.htm#796
it is not the number of seconds since some epoch - but rather the 1 century2 year3 month4 day 5 hour6 minute7 second
http://otn.oracle.com/docs/products/oracle8i/doc_library/817_doc/appdev.817/a76975/oci03typ.htm#421890 <code>
has the exact layout/details.
The year 2000 problem WILL happen again and we'll call it the year 4712 problem.
The date format 'MM RRRR' is returning wrong results where as 'RRRRMM' returning the correctdata. Can you please explain as to why this is happening. Is there a more efficient way of doing the date comparision for the given case.SQL> DESC TEST; Name Null? Type ----------------------------------------- -------- ---------------------------- ID NOT NULL NUMBER BEGIN_DT DATE END_DT DATE VOL NUMBERSQL> SELECT * FROM TEST; ID BEGIN_DT END_DT VOL---------- --------- --------- ---------- 1 26-FEB-03 31-DEC-03 2 01-JAN-03 31-DEC-07 100 3 01-JAN-03 31-DEC-03 3 4 01-JAN-04 31-DEC-08 400 5 01-JAN-03 31-DEC-04 80 6 01-JAN-05 31-DEC-14 1000 7 01-MAY-03 30-SEP-03 1007 rows selected.SQL> select * 2 from TEST 3 where TO_CHAR(BEGIN_dT,'MM RRRR') <= TO_CHAR(SYSDATE, 'MM RRRR') 4 and TO_CHAR(end_dT,'MM RRRR') >= TO_CHAR(SYSDATE, 'MM RRRR') 5 / ID BEGIN_DT END_DT VOL---------- --------- --------- ---------- 1 26-FEB-03 31-DEC-03 2 01-JAN-03 31-DEC-07 100 3 01-JAN-03 31-DEC-03 3 4 01-JAN-04 31-DEC-08 400 5 01-JAN-03 31-DEC-04 80 6 01-JAN-05 31-DEC-14 1000 7 01-MAY-03 30-SEP-03 1007 rows selected.SQL> select * 2 from TEST 3 where TO_CHAR(BEGIN_dT,'RRRRMM') <= TO_CHAR(SYSDATE, 'RRRRMM') 4 and TO_CHAR(end_dT,'RRRRMM') >= TO_CHAR(SYSDATE, 'RRRRMM') 5 / ID BEGIN_DT END_DT VOL---------- --------- --------- ---------- 1 26-FEB-03 31-DEC-03 2 01-JAN-03 31-DEC-07 100 3 01-JAN-03 31-DEC-03 3 5 01-JAN-03 31-DEC-04 80 7 01-MAY-03 30-SEP-03 100
it is giving you 110% what you asked for.
think about it.
think "strings"
you do not have dates -- you have two strings.
tell me, which is "bigger"
12 xxxx
01 xxxx
??? hmmm, now what about
12 1902
01 2001
which is bigger? (same answer, they are STRINGS not dates!!!!!)
you have nice DATES!!! why -- why -- why would you convert them into strings?!?!
where begin_dt < add_months(trunc(sysdate,'mm'),1)
and end_dt >= trunc(sysdate,'mm')
that is what you want.
give me everything where the begin_date happens this month and before AND end_date is in this month or later.
Hi Tom,We have observed a very strange behaviour while converting text to Date in a view and then selecting from it. Hope you would be able to throw some light on it as we are clueless.Following is a Copy/paste from the SQL Plus window. The database version is 8.1.7SQL*Plus: Release 8.0.5.0.0 - Production on Tue Jul 20 16:29:2 2004(c) Copyright 1998 Oracle Corporation. All rights reserved.Connected to:Oracle8i Enterprise Edition Release 8.1.7.3.0 - ProductionWith the Partitioning optionJServer Release 8.1.7.3.0 - ProductionSQL> variable MON_TXT varchar2(8);SQL> exec :MON_TXT := 'FEB-2004';PL/SQL procedure successfully completed.SQL> SELECT * FROM 2 ( 3 SELECT 4 TO_DATE(MONTH,'MON-YYYY') COL_TIME 5 FROM 6 ( 7 SELECT 'JAN-2004' MONTH FROM DUAL 8 UNION ALL 9 SELECT 'FEB-2004' MONTH FROM DUAL 10 ) 11 ) 12 WHERE COL_TIME = TO_DATE(:MON_TXT, 'MON-YYYY');no rows selectedSQL> exec :MON_TXT := 'JAN-2004';PL/SQL procedure successfully completed.SQL> /COL_TIME---------01-FEB-0401-JAN-04SQL> The section where you see the text---------------- 7 SELECT 'JAN-2004' MONTH FROM DUAL 8 UNION ALL 9 SELECT 'FEB-2004' MONTH FROM DUAL ----------------I have put for simulation purposes but the actual business data is obtained via csv files which are loaded into db using SQL Loader. The business data is very similar to the simulated data the only difference being few extra columns present in business data like fact and dimensions which are irrelevant for current problem.Thanks for you help,Shailendra
you are not clueless, it is definitely doing the wrong thing (does not reproduce in 9ir2 btw).
workaround appears to be:
12 WHERE COL_TIME = (select TO_DATE(:MON_TXT, 'MON-YYYY') from dual);
I reproduced in 817 your issue. Please contact support a file a bug with your very simple, clear test case.
Hi Tom,Hmm interesting !How does the "work around" work then? What change does it make to the query plan(as the queries are same semantically)?I did some investigation on query plans and found that the introduction of select from dual forces a join/filter on the resultset , instead of individual filters which were happening before. But not sure hence the question. Below are the explain plan of the two statements:SQL> variable MON_TXT varchar2(8);SQL> exec :MON_TXT := 'JAN-2004';PL/SQL procedure successfully completed.SQL> SELECT * FROM 2 ( 3 SELECT 4 TO_DATE(MONTH,'MON-YYYY') COL_TIME 5 FROM 6 ( 7 SELECT 'JAN-2004' MONTH FROM DUAL 8 UNION ALL 9 SELECT 'FEB-2004' MONTH FROM DUAL 10 ) 11 ) 12 WHERE COL_TIME = TO_DATE(:MON_TXT, 'MON-YYYY');COL_TIME---------01-JAN-0401-FEB-04Execution Plan---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 VIEW 2 1 UNION-ALL 3 2 FILTER 4 3 TABLE ACCESS (FULL) OF 'DUAL' 5 2 FILTER 6 5 TABLE ACCESS (FULL) OF 'DUAL'SQL> SELECT * FROM 2 ( 3 SELECT 4 TO_DATE(MONTH,'MON-YYYY') COL_TIME 5 FROM 6 ( 7 SELECT 'JAN-2004' MONTH FROM DUAL 8 UNION ALL 9 SELECT 'FEB-2004' MONTH FROM DUAL 10 ) 11 ) 12 WHERE COL_TIME = (SELECT TO_DATE(:MON_TXT, 'MON-YYYY') FROM DUAL);COL_TIME---------01-JAN-04Execution Plan---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 FILTER 2 1 VIEW 3 2 UNION-ALL 4 3 TABLE ACCESS (FULL) OF 'DUAL' 5 3 TABLE ACCESS (FULL) OF 'DUAL' 6 1 TABLE ACCESS (FULL) OF 'DUAL'Thanks,Shailendra
it is a bug -- please file one with support. You can use the workaround as a "workaround", for unless this was already bugged (support is best to do the research on this), it take some amount of time to fix it (and you probably don't want to wait). Also, they will fix it in 8174 -- not 8173.
(workaround works by preventing the pushing of the predicate)
FAQs
How do I change the date format in SQL query? ›
- Use the SELECT statement with CONVERT function and date format option for the date values needed.
- To get YYYY-MM-DD use this T-SQL syntax SELECT CONVERT(varchar, getdate(), 23)
- To get MM/DD/YY use this T-SQL syntax SELECT CONVERT(varchar, getdate(), 1)
Discussion: To format a date (or timestamp) in Oracle, use the function to_char() . This function takes a date, formats it to your definition, and returns a string. It requires two parameters: a date value and a format.
How do I display a date in yyyy mm dd format in Oracle? ›In Oracle, you can also specify a DATE value as a string literal using the syntax DATE 'YYYY-MM-DD' . This can be useful in situations where you need to specify a date value in a SQL query without explicitly converting a string to a date using the TO_DATE() function.
How do I insert a date in a specific format in SQL? ›We can also insert date using 'to_date' function in sql. The following syntax can be used: TO_DATE([value], [format]);
How to change the date format to yyyy mm dd in SQL? ›By using format code 112, we can convert the given datetime to yyyymmdd format using the CONVERT function in sql server. By using format code 103, we can convert the given datetime to dd/mm/yyyy format. By using format code 29, we can convert the given datetime to dd-mm-yyyy hh:mm:ss:nnn format.
How to change date to string to format in SQL? ›- the date – it has to be a string. For example, '09-01-2023'
- the format – the format you want the date to get converted to. For example mm-dd-yyyy . You specify the format like this %d-%m-%Y .
- Click the data type icon in the column header and select Date.
- Select the column, then click Transform > Data Type > Date from the Ribbon.
- Right-click on the column header, then click Change Type > Date.
- Change the applied data type in the M code to type date.
Press Ctrl+1 to open the Format Cells dialog. Alternatively, you can right click the selected cells and choose Format Cells… from the context menu. In the Format Cells window, switch to the Number tab, and select Date in the Category list. Under Type, pick a desired date format.
How to change the date format from DD MM YYYY to DD MM YYYY in C#? ›- create a variable of string “yourDate”
- assign yourDate = “27/11/21”
- Output = DateTime.ParseExact(yourDate , dd/MM/yy ,CultureInfo.InvariantCulture,DateTimeStyles.None).ToString(“dd-MMM-yyyy”)
Creating A Simple Date Format
String pattern = "yyyy-MM-dd" ; SimpleDateFormat simpleDateFormat = new SimpleDateFormat(pattern); The specified parameter “pattern” is the pattern used for formatting and parsing dates.
How to check date format of a column in SQL? ›
SQL has IsDate() function which is used to check the passed value is date or not of specified format, it returns 1(true) when the specified value is date otherwise it return 0(false).
What is the date format for DD MMM YYYY in Oracle? ›In Oracle databases, the date is in the format DD-MMM-YY (eg: 21-May-22), while Qlik Replicate from Oracle to Kafka, the default format in Kafka is 2022-05-21 00:00:00 (even source & destination representing the same date but their format differs). In SQLite Data Function there are some types include: YYYY-MM-DD.
How to convert date format to string in Oracle? ›In Oracle, TO_CHAR function converts a datetime value (DATE, TIMESTAMP data types i.e.) to a string using the specified format. In SQL Server, you can use CONVERT or CAST functions to convert a datetime value (DATETIME, DATETIME2 data types i.e.) to a string.
What is the default date format in Oracle query? ›The default date format is DD-MON-YY. SYSDATE is a function returning date and time. DUAL is a dummy table used to view SYSDATE. The default display and input format for any date is DD-MON-YY.