Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2002
    Posts
    11

    Unanswered: URGNET HELP: SQL/Oracle get weekly record

    Hello -

    I have a question on forming the query using SQL. I have a table that has a column named START_TIME which has strings in the format
    YYYY/MM/DD HH:MMS (19 characters)

    I want to get the current date and time from the system and then form a query that will pull records from the table that has the current date to 7 days back.

    i.e., I need a query that will pull record for a week from today (having in mind the date format specified)

    Any help?

    Thanks.
    edwk
    Last edited by edwk2002; 12-30-02 at 15:54.

  2. #2
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    By converting the string of chars to a date you will be able to use standard date arithemetic. This should get you started....

    select to_date('2002/12/30 21:03:19','YYYY/MM/DD HH24:MIS')-7 from dual

    HTH

  3. #3
    Join Date
    Oct 2002
    Location
    greenwich.ct.us
    Posts
    279
    Personally, I think you'd be better off storing your START_TIME as a date instead of a string.

  4. #4
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Whilst I (and probably everyone on here) agrees that storing dates/times as dates is the better way to go, it doesn't answer the original question.

    However, if I were to make such a sugestion and if the original poster was willing to change the table and/or the numerous systems relying on it then this might also help -

    alter table [tablename] modify [columnname] date

    maybe an ....

    alter session set nls_date_format = 'YYYY/MM/DD HH24:MIS'

    would also be useful before the modify column? I don't know I haven't tried it? But then I didn't suggest it either.

    Comments with usefull suggestions welcome. Comments on their own... nah you can keep them, they don't really help anyone - they just alienate them.

    http://dbforums.com/t643138.html
    http://dbforums.com/t640487.html

    Marist, clearly you are knowledgable in the Oracle arena but your posting style can give people the wrong impression. Me included. I don't believe it is your intention and I don't want to be flamed to hell and back over it either.

    I'm sorry - I wanted to send this personally rather than on the forum - but I'm a programmer, not a forum user:-( Maybe AndrewSt will delete this once you've got it.

    Kindest Regards
    Bill

Posting Permissions

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