SQL Error 2207 on TO_TIMESTAMP() using datetime format










0















In Postgres, I'm trying to do a date/time based query in my WHERE predicate.



When I try to select with this date/time format SQL error says the value needs to be an integer. I'm not sure why it does not think my minute of 17 is not an integer or why it only see it as a 1 and not a 17?



SELECT *
FROM history
WHERE create_time > TO_TIMESTAMP('2018-10-08T23:17:44.728','yyyy-MM-dd''T''HH:mm:ss.SSS');


ERROR: invalid value ":1" for "HH"
DETAIL: Value must be an integer.
SQL state: 22007










share|improve this question


























    0















    In Postgres, I'm trying to do a date/time based query in my WHERE predicate.



    When I try to select with this date/time format SQL error says the value needs to be an integer. I'm not sure why it does not think my minute of 17 is not an integer or why it only see it as a 1 and not a 17?



    SELECT *
    FROM history
    WHERE create_time > TO_TIMESTAMP('2018-10-08T23:17:44.728','yyyy-MM-dd''T''HH:mm:ss.SSS');


    ERROR: invalid value ":1" for "HH"
    DETAIL: Value must be an integer.
    SQL state: 22007










    share|improve this question
























      0












      0








      0








      In Postgres, I'm trying to do a date/time based query in my WHERE predicate.



      When I try to select with this date/time format SQL error says the value needs to be an integer. I'm not sure why it does not think my minute of 17 is not an integer or why it only see it as a 1 and not a 17?



      SELECT *
      FROM history
      WHERE create_time > TO_TIMESTAMP('2018-10-08T23:17:44.728','yyyy-MM-dd''T''HH:mm:ss.SSS');


      ERROR: invalid value ":1" for "HH"
      DETAIL: Value must be an integer.
      SQL state: 22007










      share|improve this question














      In Postgres, I'm trying to do a date/time based query in my WHERE predicate.



      When I try to select with this date/time format SQL error says the value needs to be an integer. I'm not sure why it does not think my minute of 17 is not an integer or why it only see it as a 1 and not a 17?



      SELECT *
      FROM history
      WHERE create_time > TO_TIMESTAMP('2018-10-08T23:17:44.728','yyyy-MM-dd''T''HH:mm:ss.SSS');


      ERROR: invalid value ":1" for "HH"
      DETAIL: Value must be an integer.
      SQL state: 22007







      sql postgresql datetime-format






      share|improve this question













      share|improve this question











      share|improve this question




      share|improve this question










      asked Nov 14 '18 at 18:19









      JasonJason

      71311023




      71311023






















          2 Answers
          2






          active

          oldest

          votes


















          0














          You're trying to consume a date value that contains a T, and it looks like you're trying to declare to TO_TIMESTAMP that the T is a literal value to be ignored. Problem is you're doing this by putting 'T' (apostrophe-T-apostrophe, escaped) which is bumping the parser on by 3 characters and it is then encountering ':1' from 23:17 when it is expecting HH:



          --your date, and underneath it, the format you gave
          2018-10-08T23:17:44.728
          yyyy-MM-dd'T'HH:mm:ss.SSS


          Can you see how the HH aligns (vertically) with :1? Postgres is complaining that it was expecting an integer that it could parse to 23, but it encountered the string :1 which isn't an integer.



          This question:



          Postgres- have to_timestamp() ignore/not read a specific character in middle of date/time string



          Implies you can put a space in the format where the T is, or just cast the string you have to a Timestamp - postgres can apparently parse that string as a Timestamp without you having to literally lay the format out for it explicitly



          Try:



          SELECT *
          FROM history
          WHERE create_time > TIMESTAMP '2018-10-08T23:17:44.728'

          SELECT *
          FROM history
          WHERE create_time > cast('2018-10-08T23:17:44.728' as timestamp)

          SELECT *
          FROM history
          WHERE create_time > TO_TIMESTAMP('2018-10-08T23:17:44.728','yyyy-MM-dd HH:mm:ss.SSS');


          You might even find this works:



          SELECT *
          FROM history
          WHERE create_time > TO_TIMESTAMP('2018-10-08T23:17:44.728', 'yyyy MM dd HH mm ss SSS')


          The numbers align with the format fields and space is used for everything else you want to ignore (hyphens, colons, dots etc)






          share|improve this answer

























          • The first one with TIMESTAMP '2018-10-08T23:17:44.728' worked.

            – Jason
            Nov 14 '18 at 22:06


















          0














          The problem is due to using ''T'' which's before HH, and DB signals that, you might use



          TO_TIMESTAMP('2018-10-08 23:17:44.728','yyyy-mm-dd HH24:MI:SS.MS')



          instead.






          share|improve this answer

























          • I don't think it's a typo, I think it is an erroneous expectation that that is how the T in the date value should be consumed

            – Caius Jard
            Nov 14 '18 at 18:44











          • @CaiusJard ok, thanks.

            – Barbaros Özhan
            Nov 14 '18 at 18:52











          • This worked too, but required me to remove the 'T' from my date and came slightly after Caius Jard's answer

            – Jason
            Nov 14 '18 at 22:05











          • @Jason ok man, nice to hear. By the way I was the first replier, but not problem, the important thing to have a better answer( that's his one ).

            – Barbaros Özhan
            Nov 14 '18 at 22:08











          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%2f53306510%2fsql-error-2207-on-to-timestamp-using-datetime-format%23new-answer', 'question_page');

          );

          Post as a guest















          Required, but never shown

























          2 Answers
          2






          active

          oldest

          votes








          2 Answers
          2






          active

          oldest

          votes









          active

          oldest

          votes






          active

          oldest

          votes









          0














          You're trying to consume a date value that contains a T, and it looks like you're trying to declare to TO_TIMESTAMP that the T is a literal value to be ignored. Problem is you're doing this by putting 'T' (apostrophe-T-apostrophe, escaped) which is bumping the parser on by 3 characters and it is then encountering ':1' from 23:17 when it is expecting HH:



          --your date, and underneath it, the format you gave
          2018-10-08T23:17:44.728
          yyyy-MM-dd'T'HH:mm:ss.SSS


          Can you see how the HH aligns (vertically) with :1? Postgres is complaining that it was expecting an integer that it could parse to 23, but it encountered the string :1 which isn't an integer.



          This question:



          Postgres- have to_timestamp() ignore/not read a specific character in middle of date/time string



          Implies you can put a space in the format where the T is, or just cast the string you have to a Timestamp - postgres can apparently parse that string as a Timestamp without you having to literally lay the format out for it explicitly



          Try:



          SELECT *
          FROM history
          WHERE create_time > TIMESTAMP '2018-10-08T23:17:44.728'

          SELECT *
          FROM history
          WHERE create_time > cast('2018-10-08T23:17:44.728' as timestamp)

          SELECT *
          FROM history
          WHERE create_time > TO_TIMESTAMP('2018-10-08T23:17:44.728','yyyy-MM-dd HH:mm:ss.SSS');


          You might even find this works:



          SELECT *
          FROM history
          WHERE create_time > TO_TIMESTAMP('2018-10-08T23:17:44.728', 'yyyy MM dd HH mm ss SSS')


          The numbers align with the format fields and space is used for everything else you want to ignore (hyphens, colons, dots etc)






          share|improve this answer

























          • The first one with TIMESTAMP '2018-10-08T23:17:44.728' worked.

            – Jason
            Nov 14 '18 at 22:06















          0














          You're trying to consume a date value that contains a T, and it looks like you're trying to declare to TO_TIMESTAMP that the T is a literal value to be ignored. Problem is you're doing this by putting 'T' (apostrophe-T-apostrophe, escaped) which is bumping the parser on by 3 characters and it is then encountering ':1' from 23:17 when it is expecting HH:



          --your date, and underneath it, the format you gave
          2018-10-08T23:17:44.728
          yyyy-MM-dd'T'HH:mm:ss.SSS


          Can you see how the HH aligns (vertically) with :1? Postgres is complaining that it was expecting an integer that it could parse to 23, but it encountered the string :1 which isn't an integer.



          This question:



          Postgres- have to_timestamp() ignore/not read a specific character in middle of date/time string



          Implies you can put a space in the format where the T is, or just cast the string you have to a Timestamp - postgres can apparently parse that string as a Timestamp without you having to literally lay the format out for it explicitly



          Try:



          SELECT *
          FROM history
          WHERE create_time > TIMESTAMP '2018-10-08T23:17:44.728'

          SELECT *
          FROM history
          WHERE create_time > cast('2018-10-08T23:17:44.728' as timestamp)

          SELECT *
          FROM history
          WHERE create_time > TO_TIMESTAMP('2018-10-08T23:17:44.728','yyyy-MM-dd HH:mm:ss.SSS');


          You might even find this works:



          SELECT *
          FROM history
          WHERE create_time > TO_TIMESTAMP('2018-10-08T23:17:44.728', 'yyyy MM dd HH mm ss SSS')


          The numbers align with the format fields and space is used for everything else you want to ignore (hyphens, colons, dots etc)






          share|improve this answer

























          • The first one with TIMESTAMP '2018-10-08T23:17:44.728' worked.

            – Jason
            Nov 14 '18 at 22:06













          0












          0








          0







          You're trying to consume a date value that contains a T, and it looks like you're trying to declare to TO_TIMESTAMP that the T is a literal value to be ignored. Problem is you're doing this by putting 'T' (apostrophe-T-apostrophe, escaped) which is bumping the parser on by 3 characters and it is then encountering ':1' from 23:17 when it is expecting HH:



          --your date, and underneath it, the format you gave
          2018-10-08T23:17:44.728
          yyyy-MM-dd'T'HH:mm:ss.SSS


          Can you see how the HH aligns (vertically) with :1? Postgres is complaining that it was expecting an integer that it could parse to 23, but it encountered the string :1 which isn't an integer.



          This question:



          Postgres- have to_timestamp() ignore/not read a specific character in middle of date/time string



          Implies you can put a space in the format where the T is, or just cast the string you have to a Timestamp - postgres can apparently parse that string as a Timestamp without you having to literally lay the format out for it explicitly



          Try:



          SELECT *
          FROM history
          WHERE create_time > TIMESTAMP '2018-10-08T23:17:44.728'

          SELECT *
          FROM history
          WHERE create_time > cast('2018-10-08T23:17:44.728' as timestamp)

          SELECT *
          FROM history
          WHERE create_time > TO_TIMESTAMP('2018-10-08T23:17:44.728','yyyy-MM-dd HH:mm:ss.SSS');


          You might even find this works:



          SELECT *
          FROM history
          WHERE create_time > TO_TIMESTAMP('2018-10-08T23:17:44.728', 'yyyy MM dd HH mm ss SSS')


          The numbers align with the format fields and space is used for everything else you want to ignore (hyphens, colons, dots etc)






          share|improve this answer















          You're trying to consume a date value that contains a T, and it looks like you're trying to declare to TO_TIMESTAMP that the T is a literal value to be ignored. Problem is you're doing this by putting 'T' (apostrophe-T-apostrophe, escaped) which is bumping the parser on by 3 characters and it is then encountering ':1' from 23:17 when it is expecting HH:



          --your date, and underneath it, the format you gave
          2018-10-08T23:17:44.728
          yyyy-MM-dd'T'HH:mm:ss.SSS


          Can you see how the HH aligns (vertically) with :1? Postgres is complaining that it was expecting an integer that it could parse to 23, but it encountered the string :1 which isn't an integer.



          This question:



          Postgres- have to_timestamp() ignore/not read a specific character in middle of date/time string



          Implies you can put a space in the format where the T is, or just cast the string you have to a Timestamp - postgres can apparently parse that string as a Timestamp without you having to literally lay the format out for it explicitly



          Try:



          SELECT *
          FROM history
          WHERE create_time > TIMESTAMP '2018-10-08T23:17:44.728'

          SELECT *
          FROM history
          WHERE create_time > cast('2018-10-08T23:17:44.728' as timestamp)

          SELECT *
          FROM history
          WHERE create_time > TO_TIMESTAMP('2018-10-08T23:17:44.728','yyyy-MM-dd HH:mm:ss.SSS');


          You might even find this works:



          SELECT *
          FROM history
          WHERE create_time > TO_TIMESTAMP('2018-10-08T23:17:44.728', 'yyyy MM dd HH mm ss SSS')


          The numbers align with the format fields and space is used for everything else you want to ignore (hyphens, colons, dots etc)







          share|improve this answer














          share|improve this answer



          share|improve this answer








          edited Nov 14 '18 at 19:00

























          answered Nov 14 '18 at 18:48









          Caius JardCaius Jard

          11.9k21240




          11.9k21240












          • The first one with TIMESTAMP '2018-10-08T23:17:44.728' worked.

            – Jason
            Nov 14 '18 at 22:06

















          • The first one with TIMESTAMP '2018-10-08T23:17:44.728' worked.

            – Jason
            Nov 14 '18 at 22:06
















          The first one with TIMESTAMP '2018-10-08T23:17:44.728' worked.

          – Jason
          Nov 14 '18 at 22:06





          The first one with TIMESTAMP '2018-10-08T23:17:44.728' worked.

          – Jason
          Nov 14 '18 at 22:06













          0














          The problem is due to using ''T'' which's before HH, and DB signals that, you might use



          TO_TIMESTAMP('2018-10-08 23:17:44.728','yyyy-mm-dd HH24:MI:SS.MS')



          instead.






          share|improve this answer

























          • I don't think it's a typo, I think it is an erroneous expectation that that is how the T in the date value should be consumed

            – Caius Jard
            Nov 14 '18 at 18:44











          • @CaiusJard ok, thanks.

            – Barbaros Özhan
            Nov 14 '18 at 18:52











          • This worked too, but required me to remove the 'T' from my date and came slightly after Caius Jard's answer

            – Jason
            Nov 14 '18 at 22:05











          • @Jason ok man, nice to hear. By the way I was the first replier, but not problem, the important thing to have a better answer( that's his one ).

            – Barbaros Özhan
            Nov 14 '18 at 22:08
















          0














          The problem is due to using ''T'' which's before HH, and DB signals that, you might use



          TO_TIMESTAMP('2018-10-08 23:17:44.728','yyyy-mm-dd HH24:MI:SS.MS')



          instead.






          share|improve this answer

























          • I don't think it's a typo, I think it is an erroneous expectation that that is how the T in the date value should be consumed

            – Caius Jard
            Nov 14 '18 at 18:44











          • @CaiusJard ok, thanks.

            – Barbaros Özhan
            Nov 14 '18 at 18:52











          • This worked too, but required me to remove the 'T' from my date and came slightly after Caius Jard's answer

            – Jason
            Nov 14 '18 at 22:05











          • @Jason ok man, nice to hear. By the way I was the first replier, but not problem, the important thing to have a better answer( that's his one ).

            – Barbaros Özhan
            Nov 14 '18 at 22:08














          0












          0








          0







          The problem is due to using ''T'' which's before HH, and DB signals that, you might use



          TO_TIMESTAMP('2018-10-08 23:17:44.728','yyyy-mm-dd HH24:MI:SS.MS')



          instead.






          share|improve this answer















          The problem is due to using ''T'' which's before HH, and DB signals that, you might use



          TO_TIMESTAMP('2018-10-08 23:17:44.728','yyyy-mm-dd HH24:MI:SS.MS')



          instead.







          share|improve this answer














          share|improve this answer



          share|improve this answer








          edited Nov 14 '18 at 18:51

























          answered Nov 14 '18 at 18:34









          Barbaros ÖzhanBarbaros Özhan

          13.6k71633




          13.6k71633












          • I don't think it's a typo, I think it is an erroneous expectation that that is how the T in the date value should be consumed

            – Caius Jard
            Nov 14 '18 at 18:44











          • @CaiusJard ok, thanks.

            – Barbaros Özhan
            Nov 14 '18 at 18:52











          • This worked too, but required me to remove the 'T' from my date and came slightly after Caius Jard's answer

            – Jason
            Nov 14 '18 at 22:05











          • @Jason ok man, nice to hear. By the way I was the first replier, but not problem, the important thing to have a better answer( that's his one ).

            – Barbaros Özhan
            Nov 14 '18 at 22:08


















          • I don't think it's a typo, I think it is an erroneous expectation that that is how the T in the date value should be consumed

            – Caius Jard
            Nov 14 '18 at 18:44











          • @CaiusJard ok, thanks.

            – Barbaros Özhan
            Nov 14 '18 at 18:52











          • This worked too, but required me to remove the 'T' from my date and came slightly after Caius Jard's answer

            – Jason
            Nov 14 '18 at 22:05











          • @Jason ok man, nice to hear. By the way I was the first replier, but not problem, the important thing to have a better answer( that's his one ).

            – Barbaros Özhan
            Nov 14 '18 at 22:08

















          I don't think it's a typo, I think it is an erroneous expectation that that is how the T in the date value should be consumed

          – Caius Jard
          Nov 14 '18 at 18:44





          I don't think it's a typo, I think it is an erroneous expectation that that is how the T in the date value should be consumed

          – Caius Jard
          Nov 14 '18 at 18:44













          @CaiusJard ok, thanks.

          – Barbaros Özhan
          Nov 14 '18 at 18:52





          @CaiusJard ok, thanks.

          – Barbaros Özhan
          Nov 14 '18 at 18:52













          This worked too, but required me to remove the 'T' from my date and came slightly after Caius Jard's answer

          – Jason
          Nov 14 '18 at 22:05





          This worked too, but required me to remove the 'T' from my date and came slightly after Caius Jard's answer

          – Jason
          Nov 14 '18 at 22:05













          @Jason ok man, nice to hear. By the way I was the first replier, but not problem, the important thing to have a better answer( that's his one ).

          – Barbaros Özhan
          Nov 14 '18 at 22:08






          @Jason ok man, nice to hear. By the way I was the first replier, but not problem, the important thing to have a better answer( that's his one ).

          – Barbaros Özhan
          Nov 14 '18 at 22:08


















          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.




          draft saved


          draft discarded














          StackExchange.ready(
          function ()
          StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53306510%2fsql-error-2207-on-to-timestamp-using-datetime-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







          這個網誌中的熱門文章

          How to read a connectionString WITH PROVIDER in .NET Core?

          In R, how to develop a multiplot heatmap.2 figure showing key labels successfully

          Museum of Modern and Contemporary Art of Trento and Rovereto