Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2009
    Location
    Chennai, India
    Posts
    7

    Unanswered: Converting character varying to integer

    SELECT (to_date(?, 'DD-MM-YYYY') + integer ? )as checkout

    This is my query i want add a date and a integer. If i used without integer it automatically took as character varying. If i used integer it gave syntax error. how will i change this query?. Can anyone tell me?
    Last edited by manikandan.c; 12-07-09 at 12:39.

  2. #2
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    Not sure what you are using to connect/bind but here is what works and what doesn't on 8.4.

    Works
    Code:
    SELECT to_date('12-07-2009', 'MM-DD-YYYY') + 2;
    SELECT to_date('12-07-2009', 'MM-DD-YYYY') + int '2';
    SELECT to_date('12-07-2009', 'MM-DD-YYYY') + '2'::int;
    SELECT to_date('12-07-2009', 'MM-DD-YYYY') + 2::int
    Doesn't work
    Code:
    SELECT to_date('12-07-2009', 'MM-DD-YYYY') + '2';
    SELECT to_date('12-07-2009', 'MM-DD-YYYY') + int 2;
    Given that, I would use ?::int since it behaves correctly for both int and varchar inputs.

  3. #3
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by manikandan.c View Post
    SELECT (to_date(?, 'DD-MM-YYYY') + integer ? )as checkout

    This is my query i want add a date and a integer.
    And what does the integer stand for? days? hours? minutes? seconds? years? weeks?

    You should use an interval for that:

    Code:
    SELECT to_date(?, 'DD-MM-YYYY') + interval '2' day
    Although I don't know if that will work with a PreparedStatement...

  4. #4
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    And what does the integer stand for? days? hours? minutes? seconds? years? weeks?
    Adding ints to dates is always interpreted as days. Adding an interval will cast the date to timestamp and return a timestamp.

  5. #5
    Join Date
    Dec 2009
    Location
    Chennai, India
    Posts
    7
    Quote Originally Posted by shammat View Post
    And what does the integer stand for? days? hours? minutes? seconds? years? weeks?

    You should use an interval for that:

    Code:
    SELECT to_date(?, 'DD-MM-YYYY') + interval '2' day
    Although I don't know if that will work with a PreparedStatement...

    hi shammat thank you

  6. #6
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by artacus72 View Post
    Adding an interval will cast the date to timestamp and return a timestamp.
    I don't see a problem with that?

  7. #7
    Join Date
    Dec 2009
    Location
    Chennai, India
    Posts
    7
    how can i change the default date format
    Last edited by manikandan.c; 12-08-09 at 05:27.

  8. #8
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •