I want to compare two column which has different date format












0














I want to compare two column which has different date format.



REQ_END_TIME = 03-APR-18 06.15.30.000000000 AM
SENDDATE = 4/3/2018



Both have same dates , so i just want to validate date part is same or not by skipping timestamp.
SENDDATE format is M/D/YYYY



How can I change any of it to make it similar to validate with query.



Database - Oracle










share|improve this question




















  • 5




    Convert both to date. If you are storing them with format, you are using a varchar/char, don't do that: always use the appropriate datatype.
    – HoneyBadger
    Nov 20 at 14:28






  • 3




    Don't store dates and time as varchar. Ever. And this will never be a problem you need to solve ever again. It's best that you spend your time now learning how to convert these to store them properly as dates instead of figuring out how to convert them or compare the string representation of the datetimes on the fly.
    – JNevill
    Nov 20 at 14:30












  • How to convert a column to date format?
    – Avinash Singh
    Nov 20 at 14:39






  • 2




    I don't know how the earlier responders know the data type of your columns. It is not clear from your post. Is SENDDATE a string, or is it actually a DATE in the database? You can find out easily with the DESCRIBE command - DESCRIBE TABLE_NAME will show the names and the data types of each column in a table called TABLE_NAME. Please check the data type of the two columns and add your findings to your post.
    – mathguy
    Nov 20 at 14:51






  • 1




    @mathguy, indeed perhaps REQ_END_TIME is a TIMESTAMP and SENDDATE is a DATE value. Default NLS_DATE_FORMAT and NLS_TIMESTAMP_FORMAT can be completely different.
    – Wernfried Domscheit
    Nov 20 at 14:55
















0














I want to compare two column which has different date format.



REQ_END_TIME = 03-APR-18 06.15.30.000000000 AM
SENDDATE = 4/3/2018



Both have same dates , so i just want to validate date part is same or not by skipping timestamp.
SENDDATE format is M/D/YYYY



How can I change any of it to make it similar to validate with query.



Database - Oracle










share|improve this question




















  • 5




    Convert both to date. If you are storing them with format, you are using a varchar/char, don't do that: always use the appropriate datatype.
    – HoneyBadger
    Nov 20 at 14:28






  • 3




    Don't store dates and time as varchar. Ever. And this will never be a problem you need to solve ever again. It's best that you spend your time now learning how to convert these to store them properly as dates instead of figuring out how to convert them or compare the string representation of the datetimes on the fly.
    – JNevill
    Nov 20 at 14:30












  • How to convert a column to date format?
    – Avinash Singh
    Nov 20 at 14:39






  • 2




    I don't know how the earlier responders know the data type of your columns. It is not clear from your post. Is SENDDATE a string, or is it actually a DATE in the database? You can find out easily with the DESCRIBE command - DESCRIBE TABLE_NAME will show the names and the data types of each column in a table called TABLE_NAME. Please check the data type of the two columns and add your findings to your post.
    – mathguy
    Nov 20 at 14:51






  • 1




    @mathguy, indeed perhaps REQ_END_TIME is a TIMESTAMP and SENDDATE is a DATE value. Default NLS_DATE_FORMAT and NLS_TIMESTAMP_FORMAT can be completely different.
    – Wernfried Domscheit
    Nov 20 at 14:55














0












0








0


0





I want to compare two column which has different date format.



REQ_END_TIME = 03-APR-18 06.15.30.000000000 AM
SENDDATE = 4/3/2018



Both have same dates , so i just want to validate date part is same or not by skipping timestamp.
SENDDATE format is M/D/YYYY



How can I change any of it to make it similar to validate with query.



Database - Oracle










share|improve this question















I want to compare two column which has different date format.



REQ_END_TIME = 03-APR-18 06.15.30.000000000 AM
SENDDATE = 4/3/2018



Both have same dates , so i just want to validate date part is same or not by skipping timestamp.
SENDDATE format is M/D/YYYY



How can I change any of it to make it similar to validate with query.



Database - Oracle







sql database oracle






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Nov 20 at 14:30









JNevill

31.3k31544




31.3k31544










asked Nov 20 at 14:27









Avinash Singh

45




45








  • 5




    Convert both to date. If you are storing them with format, you are using a varchar/char, don't do that: always use the appropriate datatype.
    – HoneyBadger
    Nov 20 at 14:28






  • 3




    Don't store dates and time as varchar. Ever. And this will never be a problem you need to solve ever again. It's best that you spend your time now learning how to convert these to store them properly as dates instead of figuring out how to convert them or compare the string representation of the datetimes on the fly.
    – JNevill
    Nov 20 at 14:30












  • How to convert a column to date format?
    – Avinash Singh
    Nov 20 at 14:39






  • 2




    I don't know how the earlier responders know the data type of your columns. It is not clear from your post. Is SENDDATE a string, or is it actually a DATE in the database? You can find out easily with the DESCRIBE command - DESCRIBE TABLE_NAME will show the names and the data types of each column in a table called TABLE_NAME. Please check the data type of the two columns and add your findings to your post.
    – mathguy
    Nov 20 at 14:51






  • 1




    @mathguy, indeed perhaps REQ_END_TIME is a TIMESTAMP and SENDDATE is a DATE value. Default NLS_DATE_FORMAT and NLS_TIMESTAMP_FORMAT can be completely different.
    – Wernfried Domscheit
    Nov 20 at 14:55














  • 5




    Convert both to date. If you are storing them with format, you are using a varchar/char, don't do that: always use the appropriate datatype.
    – HoneyBadger
    Nov 20 at 14:28






  • 3




    Don't store dates and time as varchar. Ever. And this will never be a problem you need to solve ever again. It's best that you spend your time now learning how to convert these to store them properly as dates instead of figuring out how to convert them or compare the string representation of the datetimes on the fly.
    – JNevill
    Nov 20 at 14:30












  • How to convert a column to date format?
    – Avinash Singh
    Nov 20 at 14:39






  • 2




    I don't know how the earlier responders know the data type of your columns. It is not clear from your post. Is SENDDATE a string, or is it actually a DATE in the database? You can find out easily with the DESCRIBE command - DESCRIBE TABLE_NAME will show the names and the data types of each column in a table called TABLE_NAME. Please check the data type of the two columns and add your findings to your post.
    – mathguy
    Nov 20 at 14:51






  • 1




    @mathguy, indeed perhaps REQ_END_TIME is a TIMESTAMP and SENDDATE is a DATE value. Default NLS_DATE_FORMAT and NLS_TIMESTAMP_FORMAT can be completely different.
    – Wernfried Domscheit
    Nov 20 at 14:55








5




5




Convert both to date. If you are storing them with format, you are using a varchar/char, don't do that: always use the appropriate datatype.
– HoneyBadger
Nov 20 at 14:28




Convert both to date. If you are storing them with format, you are using a varchar/char, don't do that: always use the appropriate datatype.
– HoneyBadger
Nov 20 at 14:28




3




3




Don't store dates and time as varchar. Ever. And this will never be a problem you need to solve ever again. It's best that you spend your time now learning how to convert these to store them properly as dates instead of figuring out how to convert them or compare the string representation of the datetimes on the fly.
– JNevill
Nov 20 at 14:30






Don't store dates and time as varchar. Ever. And this will never be a problem you need to solve ever again. It's best that you spend your time now learning how to convert these to store them properly as dates instead of figuring out how to convert them or compare the string representation of the datetimes on the fly.
– JNevill
Nov 20 at 14:30














How to convert a column to date format?
– Avinash Singh
Nov 20 at 14:39




How to convert a column to date format?
– Avinash Singh
Nov 20 at 14:39




2




2




I don't know how the earlier responders know the data type of your columns. It is not clear from your post. Is SENDDATE a string, or is it actually a DATE in the database? You can find out easily with the DESCRIBE command - DESCRIBE TABLE_NAME will show the names and the data types of each column in a table called TABLE_NAME. Please check the data type of the two columns and add your findings to your post.
– mathguy
Nov 20 at 14:51




I don't know how the earlier responders know the data type of your columns. It is not clear from your post. Is SENDDATE a string, or is it actually a DATE in the database? You can find out easily with the DESCRIBE command - DESCRIBE TABLE_NAME will show the names and the data types of each column in a table called TABLE_NAME. Please check the data type of the two columns and add your findings to your post.
– mathguy
Nov 20 at 14:51




1




1




@mathguy, indeed perhaps REQ_END_TIME is a TIMESTAMP and SENDDATE is a DATE value. Default NLS_DATE_FORMAT and NLS_TIMESTAMP_FORMAT can be completely different.
– Wernfried Domscheit
Nov 20 at 14:55




@mathguy, indeed perhaps REQ_END_TIME is a TIMESTAMP and SENDDATE is a DATE value. Default NLS_DATE_FORMAT and NLS_TIMESTAMP_FORMAT can be completely different.
– Wernfried Domscheit
Nov 20 at 14:55












3 Answers
3






active

oldest

votes


















0














You would convert both to dates or to strings:



where to_date(substr(req_end_time, 9), 'DD-MON-YYYY') = to_date(send_date, 'MM/DD/YYYY')





share|improve this answer























  • to_date('03-APR-18 06.15.30.000000000 AM', 'DD-MON-YYYY') will not work. Better use TO_timestamp('03-APR-18 06.15.30.000000000 AM', 'DD-MON-RR hh.mi.ssxff am') provided they are varchar2
    – Wernfried Domscheit
    Nov 20 at 15:05





















0














You can use CAST and to_date (with the format mask)



select * from mytable
where to_date(CAST(REQ_END_TIME as char(10)), 'DD/MM/YYYY') =
to_date(SENDDATE, 'MM/DD/YYYY')


It'll return:



03.04.18   =   03.04.18


Compiled query






share|improve this answer





























    0














    Assuming SENDDATE is already in date format



    select (to_date(REQ_END_TIME,'M/D/YYYY') - SENDDATE ) COMPARE
    from mytable





    share|improve this answer























      Your Answer






      StackExchange.ifUsing("editor", function () {
      StackExchange.using("externalEditor", function () {
      StackExchange.using("snippets", function () {
      StackExchange.snippets.init();
      });
      });
      }, "code-snippets");

      StackExchange.ready(function() {
      var channelOptions = {
      tags: "".split(" "),
      id: "1"
      };
      initTagRenderer("".split(" "), "".split(" "), channelOptions);

      StackExchange.using("externalEditor", function() {
      // Have to fire editor after snippets, if snippets enabled
      if (StackExchange.settings.snippets.snippetsEnabled) {
      StackExchange.using("snippets", function() {
      createEditor();
      });
      }
      else {
      createEditor();
      }
      });

      function createEditor() {
      StackExchange.prepareEditor({
      heartbeatType: 'answer',
      autoActivateHeartbeat: false,
      convertImagesToLinks: true,
      noModals: true,
      showLowRepImageUploadWarning: true,
      reputationToPostImages: 10,
      bindNavPrevention: true,
      postfix: "",
      imageUploader: {
      brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
      contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
      allowUrls: true
      },
      onDemand: true,
      discardSelector: ".discard-answer"
      ,immediatelyShowMarkdownHelp:true
      });


      }
      });














      draft saved

      draft discarded


















      StackExchange.ready(
      function () {
      StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53395172%2fi-want-to-compare-two-column-which-has-different-date-format%23new-answer', 'question_page');
      }
      );

      Post as a guest















      Required, but never shown

























      3 Answers
      3






      active

      oldest

      votes








      3 Answers
      3






      active

      oldest

      votes









      active

      oldest

      votes






      active

      oldest

      votes









      0














      You would convert both to dates or to strings:



      where to_date(substr(req_end_time, 9), 'DD-MON-YYYY') = to_date(send_date, 'MM/DD/YYYY')





      share|improve this answer























      • to_date('03-APR-18 06.15.30.000000000 AM', 'DD-MON-YYYY') will not work. Better use TO_timestamp('03-APR-18 06.15.30.000000000 AM', 'DD-MON-RR hh.mi.ssxff am') provided they are varchar2
        – Wernfried Domscheit
        Nov 20 at 15:05


















      0














      You would convert both to dates or to strings:



      where to_date(substr(req_end_time, 9), 'DD-MON-YYYY') = to_date(send_date, 'MM/DD/YYYY')





      share|improve this answer























      • to_date('03-APR-18 06.15.30.000000000 AM', 'DD-MON-YYYY') will not work. Better use TO_timestamp('03-APR-18 06.15.30.000000000 AM', 'DD-MON-RR hh.mi.ssxff am') provided they are varchar2
        – Wernfried Domscheit
        Nov 20 at 15:05
















      0












      0








      0






      You would convert both to dates or to strings:



      where to_date(substr(req_end_time, 9), 'DD-MON-YYYY') = to_date(send_date, 'MM/DD/YYYY')





      share|improve this answer














      You would convert both to dates or to strings:



      where to_date(substr(req_end_time, 9), 'DD-MON-YYYY') = to_date(send_date, 'MM/DD/YYYY')






      share|improve this answer














      share|improve this answer



      share|improve this answer








      edited Nov 20 at 18:41

























      answered Nov 20 at 14:56









      Gordon Linoff

      756k35291399




      756k35291399












      • to_date('03-APR-18 06.15.30.000000000 AM', 'DD-MON-YYYY') will not work. Better use TO_timestamp('03-APR-18 06.15.30.000000000 AM', 'DD-MON-RR hh.mi.ssxff am') provided they are varchar2
        – Wernfried Domscheit
        Nov 20 at 15:05




















      • to_date('03-APR-18 06.15.30.000000000 AM', 'DD-MON-YYYY') will not work. Better use TO_timestamp('03-APR-18 06.15.30.000000000 AM', 'DD-MON-RR hh.mi.ssxff am') provided they are varchar2
        – Wernfried Domscheit
        Nov 20 at 15:05


















      to_date('03-APR-18 06.15.30.000000000 AM', 'DD-MON-YYYY') will not work. Better use TO_timestamp('03-APR-18 06.15.30.000000000 AM', 'DD-MON-RR hh.mi.ssxff am') provided they are varchar2
      – Wernfried Domscheit
      Nov 20 at 15:05






      to_date('03-APR-18 06.15.30.000000000 AM', 'DD-MON-YYYY') will not work. Better use TO_timestamp('03-APR-18 06.15.30.000000000 AM', 'DD-MON-RR hh.mi.ssxff am') provided they are varchar2
      – Wernfried Domscheit
      Nov 20 at 15:05















      0














      You can use CAST and to_date (with the format mask)



      select * from mytable
      where to_date(CAST(REQ_END_TIME as char(10)), 'DD/MM/YYYY') =
      to_date(SENDDATE, 'MM/DD/YYYY')


      It'll return:



      03.04.18   =   03.04.18


      Compiled query






      share|improve this answer


























        0














        You can use CAST and to_date (with the format mask)



        select * from mytable
        where to_date(CAST(REQ_END_TIME as char(10)), 'DD/MM/YYYY') =
        to_date(SENDDATE, 'MM/DD/YYYY')


        It'll return:



        03.04.18   =   03.04.18


        Compiled query






        share|improve this answer
























          0












          0








          0






          You can use CAST and to_date (with the format mask)



          select * from mytable
          where to_date(CAST(REQ_END_TIME as char(10)), 'DD/MM/YYYY') =
          to_date(SENDDATE, 'MM/DD/YYYY')


          It'll return:



          03.04.18   =   03.04.18


          Compiled query






          share|improve this answer












          You can use CAST and to_date (with the format mask)



          select * from mytable
          where to_date(CAST(REQ_END_TIME as char(10)), 'DD/MM/YYYY') =
          to_date(SENDDATE, 'MM/DD/YYYY')


          It'll return:



          03.04.18   =   03.04.18


          Compiled query







          share|improve this answer












          share|improve this answer



          share|improve this answer










          answered Nov 20 at 20:54









          Georgy

          667




          667























              0














              Assuming SENDDATE is already in date format



              select (to_date(REQ_END_TIME,'M/D/YYYY') - SENDDATE ) COMPARE
              from mytable





              share|improve this answer




























                0














                Assuming SENDDATE is already in date format



                select (to_date(REQ_END_TIME,'M/D/YYYY') - SENDDATE ) COMPARE
                from mytable





                share|improve this answer


























                  0












                  0








                  0






                  Assuming SENDDATE is already in date format



                  select (to_date(REQ_END_TIME,'M/D/YYYY') - SENDDATE ) COMPARE
                  from mytable





                  share|improve this answer














                  Assuming SENDDATE is already in date format



                  select (to_date(REQ_END_TIME,'M/D/YYYY') - SENDDATE ) COMPARE
                  from mytable






                  share|improve this answer














                  share|improve this answer



                  share|improve this answer








                  edited Nov 21 at 2:59









                  Pang

                  6,8601563101




                  6,8601563101










                  answered Nov 21 at 2:51









                  user3439907

                  112




                  112






























                      draft saved

                      draft discarded




















































                      Thanks for contributing an answer to Stack Overflow!


                      • Please be sure to answer the question. Provide details and share your research!

                      But avoid



                      • Asking for help, clarification, or responding to other answers.

                      • Making statements based on opinion; back them up with references or personal experience.


                      To learn more, see our tips on writing great answers.





                      Some of your past answers have not been well-received, and you're in danger of being blocked from answering.


                      Please pay close attention to the following guidance:


                      • Please be sure to answer the question. Provide details and share your research!

                      But avoid



                      • Asking for help, clarification, or responding to other answers.

                      • Making statements based on opinion; back them up with references or personal experience.


                      To learn more, see our tips on writing great answers.




                      draft saved


                      draft discarded














                      StackExchange.ready(
                      function () {
                      StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53395172%2fi-want-to-compare-two-column-which-has-different-date-format%23new-answer', 'question_page');
                      }
                      );

                      Post as a guest















                      Required, but never shown





















































                      Required, but never shown














                      Required, but never shown












                      Required, but never shown







                      Required, but never shown

































                      Required, but never shown














                      Required, but never shown












                      Required, but never shown







                      Required, but never shown







                      Popular posts from this blog

                      404 Error Contact Form 7 ajax form submitting

                      How to know if a Active Directory user can login interactively

                      TypeError: fit_transform() missing 1 required positional argument: 'X'