Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2003
    Posts
    21

    Unhappy Unanswered: Date / time aso ...

    another question from the noobpowaaa

    hum before anything else ...
    DB2 7.2.3
    win 2000

    I try to find a type which is like the date-time mysql type. I find a time type, a date type and a timestamp type, but no date-time ? Am I blink ?

    What is the DB2 date syntax ? (and time please )
    I try the MySQL syntax : "yyyy-mm-dd" with no result

    sorry for all this question ...

  2. #2
    Join Date
    Sep 2002
    Posts
    456

    Re: Date / time aso ...

    Good work with the info.

    You can use the TRANSLATE function of DB2 to format the date.

    select TRANSLATE( '*format*' , char(your-date,ISO) , 'ABCD-EF-GH' )

    you can choose any *format* where ABCD is year , EF is month and GH is day.

    e.g.
    TRANSLATE ('GH.EF.ABCD',char(your-date,ISO),'ABCD-EF-GH') returns dd.mm.yyyy
    TRANSLATE ('EF/GH/ABCD',char(your-date,ISO),'ABCD-EF-GH') returns mm/dd/yyyy
    TRANSLATE ('CD+GH+EF',char(your-date,ISO),'ABCD-EF-GH') returns yy+dd+mm
    and so on.

    Paul

    Originally posted by arfman
    another question from the noobpowaaa

    hum before anything else ...
    DB2 7.2.3
    win 2000

    I try to find a type which is like the date-time mysql type. I find a time type, a date type and a timestamp type, but no date-time ? Am I blink ?

    What is the DB2 date syntax ? (and time please )
    I try the MySQL syntax : "yyyy-mm-dd" with no result

    sorry for all this question ...

  3. #3
    Join Date
    Sep 2002
    Posts
    456

    Re: Date / time aso ...

    Good work with the info.

    You can use the TRANSLATE function of DB2 to format the date.

    select TRANSLATE( '*format*' , char(your-date,ISO) , 'ABCD-EF-GH' )

    you can choose any *format* where ABCD is year , EF is month and GH is day.

    e.g.
    TRANSLATE ('GH.EF.ABCD',char(your-date,ISO),'ABCD-EF-GH') returns dd.mm.yyyy
    TRANSLATE ('EF/GH/ABCD',char(your-date,ISO),'ABCD-EF-GH') returns mm/dd/yyyy
    TRANSLATE ('CD+GH+EF',char(your-date,ISO),'ABCD-EF-GH') returns yy+dd+mm
    and so on.

    Paul

    Originally posted by arfman
    another question from the noobpowaaa

    hum before anything else ...
    DB2 7.2.3
    win 2000

    I try to find a type which is like the date-time mysql type. I find a time type, a date type and a timestamp type, but no date-time ? Am I blink ?

    What is the DB2 date syntax ? (and time please )
    I try the MySQL syntax : "yyyy-mm-dd" with no result

    sorry for all this question ...

  4. #4
    Join Date
    Jan 2003
    Posts
    1,605

    Talking Re: Date / time aso ...

    Have u try to write your syntax in single quotation marks:
    'yyyy-mm-dd'. NOT in double quotation marks: "yyyy-mm-dd'

    Originally posted by arfman
    another question from the noobpowaaa

    hum before anything else ...
    DB2 7.2.3
    win 2000

    I try to find a type which is like the date-time mysql type. I find a time type, a date type and a timestamp type, but no date-time ? Am I blink ?

    What is the DB2 date syntax ? (and time please )
    I try the MySQL syntax : "yyyy-mm-dd" with no result

    sorry for all this question ...

  5. #5
    Join Date
    Jan 2003
    Posts
    21
    all your answer are great but ... yes in my answer there is always a but , hum sorry .. but, if i want to had a date in a insert ? in which format must it be ?

    insert into test (DATETEST) values ('20.12.2002') ...

  6. #6
    Join Date
    Jan 2003
    Posts
    1,605
    It depends in witch country you live. The options are: ISO, USA, EUR, JIS and LOCAL.

    Your SQL is fine if you live in EUR.

    The ISO format works no mather where you live.
    So SQL below must work:
    insert into test (DATETEST) values ('2002-12-20')

Posting Permissions

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