#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2015
    Posts
    9
    Rep Power
    0

    Exclamation Query executes but returns no data


    Below query executes without where clause but I need to extract data between those two dates. Can someone help me solve this issue? Many thanks



    Code:
    select   wo.num, sysuser.username, woitem.qtytarget/wo.qtytarget as TotalQuantity, wo.DateCreated, wo.datefinished
    from woitem
    join wo
    ON wo.id = woitem.woid
    JOIN part ON woitem.partid = part.id
    Join moitem
    on moitem.id = woitem.moitemid
    Join mo
    ON mo.id = moitem.moid
    LEFT JOIN SYSUSER ON mo.userid = sysuser.id
    where  current_timestamp between '01-Jun-2014' AND timestamp '12-Jun-2015'
    group by sysuser.username,wo.DateCreated, wo.datefinished, wo.num, woitem.qtytarget/wo.qtytarget,  current_timestamp 
    ORDER BY CAST( REPLACE( wo.num, ':', '.') as decimal(12, 3))
  2. #2
  3. Lord of the Dance
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Oct 2003
    Posts
    4,113
    Rep Power
    2010
    How and where have you tried to execute that query? I would expect you to get some kind of a syntax error.

    The syntax for between is 'where fieldName between 'date1' and 'date2'.
    E.g:
    Code:
    where  current_timestamp between '01-Jun-2014' AND '12-Jun-2015'
    Can you explain more about what you want to do?

    Maybe you also can have a look at this function:
    DATEDIFF()
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2008
    Posts
    74
    Rep Power
    9
    current time stamp = date space time

    us format
    mm/dd/yyyy hh:mm:ss:nnn

    you need CASTing before to Date

    (cast(fieldx as date) between date1 and date2)

    look faq about casting date data in firebirdsql.org
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2008
    Posts
    74
    Rep Power
    9
    you can use variables to date as

    :date1
    :date2

    thus stay easy change the values to date


    to TIME you can use '00:00:00' if you dont use it

    then

    '12/31/2015 00:00:00'

    dont forget

    fieldNULL result always Null resulted
    Last edited by emailx45; September 25th, 2015 at 10:01 AM.
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2008
    Posts
    74
    Rep Power
    9
    you can use variables to date as

    :date1
    :date2

    thus stay easy change the values to date
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2006
    Location
    Plovdiv. Bulgaria
    Posts
    238
    Rep Power
    16
    current_timestamp gives you exactly that - the current date and time on the server at the time when this query is executed.
    So, your statement look like this

    WHERE '07.10.2015 15:30:25' between '01-Jun-2014' AND timestamp '12-Jun-2015'. It's just not true that's why the query doesn't return anything.
    You need something like this: WHERE :date1 between cast('01-Jun-2014' as timestamp) AND cast('12-Jun-2015' as timestamp). CURRENT_DATE, CURRENT_TIME and CURRENT_TIMESTAMPS always give the date,time, date and time on the server at the time when the statement is executed. Try this to see what I mean, run it every 5-10 seconds -
    Code:
    SELECT CURRENT_TIMESTAMP, CURRENT_TIME, CURRENT_DATE FROM RDB$DAABASE

IMN logo majestic logo threadwatch logo seochat tools logo