Results 1 to 9 of 9

Thread: SQL Query Help

  1. #1
    Join Date
    Jun 2009
    Posts
    4

    Unanswered: SQL Query Help

    Hai,

    Table Structure

    Id No Date Time

    0001, 01-05-2009, 040000
    0001, 02-05-2009, 020000
    0002, 01-05-2009, 060000
    0002, 01-05-2009, 180000

    Time and Date is nvarchar


    I want to get the data between

    Yesterday 03:00:01 to today 03:00:00 (hh:mm:ss)
    Day before yesterday 03:00:01 to yesterday 03:00:00 (hh:mm:ss)
    ……………

    I tried the below mentioned query

    Select idno, min (time), max (time) from table where time between 030001 and 030000
    Nothing displayed in the result because it is taking today time from 03.00 am to 03.01am
    Exactly I need today 03.00 am to yesterday 03.01 am

    I need the sql query for the above condition

    Can any one help me?


    Jash.

  2. #2
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by jash
    Can any one help me?
    Use the datetime data type to hold datetime fields!
    If you're going to store times as a varchar then don't compare it with an int!
    Name your tables something better than table.
    Name your fields something better than time.
    Why are you using min and max in your query?
    Are dates stored as DD-MM-YYYY or MM-DD-YYYY?

    If you change the table to use a datetime data type then this becomes quite easy.

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    100% agree with Mike - we have the datetime data type for a reason
    George
    Home | Blog

  4. #4
    Join Date
    Jun 2009
    Posts
    4

    SQL Query Help

    Hai,

    Am Using SQL SERVER - 2000

    Table Structure

    CARDEVENTDATE CARDEVENTTIME CARDNO
    20090224 92007 485
    20090224 92345 321
    20090225 163932 168
    20090225 164630 471
    20090225 165027 488
    20090225 165137 247
    20090225 165147 519
    20090225 165715 518
    20090225 165749 331
    20090303 162059 240
    20090303 162723 518
    20090303 155029 386
    20090303 155707 441
    20090303 162824 331

    Cardeventdate and Cardeventtime - nvarchar data type
    Date and Time is separate column

    I want to get a data between

    Yesterday 03:00:01 AM to today 03:00:00 AM
    Day before yesterday 03:00:01 AM to yesterday 03:00:00 AM
    So On……..

    I tried the below mentioned query’s

     Select Cardno, cardeventdate, min(cardeventtime), max(cardeventtime) from table where cardeventtime between 030001 to 030000

     Select Cardno, Cardeventdate, Min(cardeventtime), max(cardeventtime) from table where Cardeventtime >030001 and Cardeventtime < 030000

    Nothing displayed in the result because it is taking today time from 03.00 am to 03.01am

    Select Cardno, Cardeventdate, min (cardeventtime), max (cardeventtime) from table where cardeventtime < 030000 and cardeventtime > previous day time – query help

    Exactly I need yesterday 03.00.01 am to today 03.00.00 am data’s, Day before yesterday 03.00.01 am to yesterday 03.00.00 am data’s …………………. So on

    I need the sql query for the above condition. Can any one help me?

    Jash.
    I need a data from yesterday 03.01 am to 23.59

  5. #5
    Join Date
    Jun 2009
    Posts
    4

    SQL Query Help

    Hai,

    Am Using SQL SERVER - 2000

    Table Structure

    CARDEVENTDATE CARDEVENTTIME CARDNO
    20090224 92007 485
    20090224 92345 321
    20090225 163932 168
    20090225 164630 471
    20090225 165027 488
    20090225 165137 247
    20090225 165147 519
    20090225 165715 518
    20090225 165749 331
    20090303 162059 240
    20090303 162723 518
    20090303 155029 386
    20090303 155707 441
    20090303 162824 331

    Cardeventdate and Cardeventtime - nvarchar data type
    Date and Time is separate column

    I want to get a data between

    Yesterday 03:00:01 AM to today 03:00:00 AM
    Day before yesterday 03:00:01 AM to yesterday 03:00:00 AM
    So On……..

    I tried the below mentioned query’s

     Select Cardno, cardeventdate, min(cardeventtime), max(cardeventtime) from table where cardeventtime between 030001 to 030000

     Select Cardno, Cardeventdate, Min(cardeventtime), max(cardeventtime) from table where Cardeventtime >030001 and Cardeventtime < 030000

    Nothing displayed in the result because it is taking today time from 03.00 am to 03.01am

    Select Cardno, Cardeventdate, min (cardeventtime), max (cardeventtime) from table where cardeventtime < 030000 and cardeventtime > previous day time – query help

    Exactly I need yesterday 03.00.01 am to today 03.00.00 am data’s, Day before yesterday 03.00.01 am to yesterday 03.00.00 am data’s …………………. So on

    I need the sql query for the above condition. Can any one help me?

    Jash.

  6. #6
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by jash
    Cardeventdate and Cardeventtime - nvarchar data type
    Date and Time is separate column
    You need to join the date and time fields into one datetime field.
    You could do this via a view if you want.
    When you have done this the query is quite simple.

    Quote Originally Posted by jash
    Can any one help me?
    Please see post 2.

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if you are using SQL server 2000 then do you want this question moving to the SQL server forum, as opposed to the MySQL forum it is in now?
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Jun 2009
    Posts
    4

    SQL Query Help

    Two Tables



    T_Person – Table 1



    CARDNO



    168

    471

    488

    247

    519

    518

    331

    240

    518

    386

    441

    331



    T_Cardevent – Table 2



    CARDEVENTDATE CARDEVENTTIME



    20090225 163932
    20090225 164630
    20090225 165027
    20090225 165137
    20090225 165147
    20090225 165715
    20090225 165749
    20090303 162059
    20090303 162723
    20090303 155029
    20090303 155707
    20090303 162824



    CARDEVENTTIME VALUE IS 6 NUMBERS NOT A 5 NUMBERS, SO NO NEED TO ADD ANYTHING.

    I WANT A DATA FROM PARTICULAR DATE TO PARTICULAR DATE FOR THAT CARDNO’s





    Query

    SELECT T_PERSON.CARDNO, T_CARDEVENT.CARDEVENTDATE, MIN(T_CARDEVENT.CARDEVENTTIME), T_CARDEVENT.CARDEVENTDATE, CASE WHEN MIN (cardeventtime) = MAX(cardeventtime) THEN 'Nodata' ELSE MAX(cardeventtime) END AS OUTTIME
    FROM T_PERSON LEFT OUTER JOIN T_CARDEVENT ON T_PERSON.CARDNO = T_CARDEVENT.CARDNO WHERE T_CARDEVENT.CARDEVENTDATE BETWEEN '" & sdate & "' AND '" & edate & "' AND T_CARDEVENT.CARDEVENTTIME BETWEEN 030001 AND 030000 GROUP BY T_PERSON.CARDNO, T_CARDEVENT.CARDEVENTDATE ORDER BY CARDNO, CARDEVENTDATE



    '" & sdate & "' – From date '" & edate & "' – To date



    T_CARDEVENT.CARDEVENTTIME BETWEEN 030001 AND 030000 – This is problem to me because it is taking today 03:00:01 to 03:00:00



    I need in time and out time of the particular cardno from this date to this date.



    But this time should take 03:00:01 to 03:00:00 means (yesterday 03 am to today 03 am) The Time should change 03:00:01 to 03:00:00 instead of 00:00:01 to 23:59:59



    I need the sql query for the above condition.

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    first thing you need to do is change your table so that you use a single DATETIME column for your dates and times

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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