Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 12 of 12

Thread: SQL Query Help

  1. #1
    New to the CF scene
    Join Date
    Jun 2009
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Location
    Denver, Colorado USA
    Posts
    3,151
    Thanks
    2
    Thanked 335 Times in 327 Posts
    If you use a DATETIME data type, you can do this with a simple BETWEEN statement in the WHERE clause. Your queries will be faster, simpler, and the amount of storage taken by the data will be minimum.
    If you are learning PHP, developing PHP code, or debugging PHP code, do yourself a favor and check your web server log for errors and/or turn on full PHP error reporting in php.ini or in a .htaccess file to get PHP to help you.

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,700
    Thanks
    80
    Thanked 4,658 Times in 4,620 Posts
    You should *ALSO* put BOTH the date and time into a single field.

    Since you said the data is NVARCHAR, I would guess that your database is SQL Server, yes?

    So you are also posting in the wrong forum.

    Your data *could* be converted to DATETIME in SQL Server, but it won't be easy. Your DATE format is not bad, but your TIME format is terrible. Converting it to DATETIME would be tough.

    If you can't change the database to a *GOOD* design, then post again (but maybe not in this forum, if this is SQL Server DB) and I'll tackle the conversion.

    Be sure to say what DB you are using.

  • #4
    New to the CF scene
    Join Date
    Jun 2009
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SQL Date and Time 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.

  • #5
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    Since you are using SQL SERVER - 2000 your question belongs on the general database forum and not the one specific to the MySQL database tool. Thread moved.

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,700
    Thanks
    80
    Thanked 4,658 Times in 4,620 Posts
    As we said, you REALLY REALLY REALLY should *CHANGE* your database.

    You should use a STANDARD DATETIME field to hold *both* the cardeventdate and cardeventtime.

    You have ENORMOUSLY complicated your life, coding it this way.

    That's the bad news. The good news is that AT LEAST you chose to store the date as YYYYMMDD. That means that it will automatically "ORDER BY" correctly, even in this bad format.

    So...

    Code:
    SELECT * FROM yourtable
    WHERE cardeventdate + RIGHT('000000'+cardeventtime,6)
    BETWEEN '20090601030001' AND '20090602030000'
    You will need to build up those two strings ('20090601030001', etc.) in code *outside* of SQL Server. E.g., in ASP/ASP.NET code or whatever you are using.

    If this query *must* be done all in SQL Server, then you'd probably be best off using a Stored Procedure to create those values.

    Note that you *MUST* consider the date *AND* time as a SINGLE VALUE to make any comparison like this work, which is why I said that you should change your DB table design!

    Yes, there are other ways to do this.

    One thing you might do is create a VIEW of that table that converts your bad data to good data.

    Let me see if I can get this right...
    Code:
    CREATE VIEW goodCardData
    AS
    SELECT cardno, 
           other, 
           fields,
           DATEADD( second
                    , 3600*CONVERT(INT,SUBSTRING(RIGHT('00000'+cardeventtime,6),1,2))
                      +60*CONVERT(INT,SUBSTRING(RIGHT('00000'+cardeventtime,6),3,2))
                      +CONVERT(INT,SUBSTRING(RIGHT('00000'+cardeventtime,6),5,2)
                    , CONVERT( DATETIME, cardeventdate, 112 ) 
                  )
    FROM yourtable

  • #7
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,700
    Thanks
    80
    Thanked 4,658 Times in 4,620 Posts
    By the way, you showed examples of your CARDDATETIME values with only 5 digits. And since this is an NVARCHAR field, I assumed that you did not pad the strings to 6 characters.

    If you did, then my code needs some minor mods.

    But, again, can't you FIX the DB??

  • #8
    New to the CF scene
    Join Date
    Jun 2009
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query Help

    Still am not get the proper query. I need exactly yesterday 03:00:01 am to today 03:00:00 data's.

  • #9
    New to the CF scene
    Join Date
    Jun 2009
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query Help

    Hai,

    Am Using SQL SERVER - 2000

    Table Structure

    CARDEVENTDATE CARDEVENTTIME CARDNO
    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.

  • #10
    New to the CF scene
    Join Date
    Jun 2009
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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.

  • #11
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,700
    Thanks
    80
    Thanked 4,658 Times in 4,620 Posts
    DAMN! I wrote a LONG answer to this one and this idiotic VBulletin form swallowed it. AGAIN!

    AARRGGHH!

    Again, what you are *COMPLETELY* missing is that you CAN NOT work on the cardeventdate and the cardeventtime as SEPARATE ENTITIES! You must construct a COMBINATION of the two.

    *THINK* about your code!

    There are only 4 seconds in two days that will meet the requirements of your query!
    Code:
        sdate at 03:00:00
        sdate at 03:00:01
        edate at 03:00:00
        edate at 03:00:01
    NO OTHER date or time will satisfy your UGLY condition:
    Code:
    WHERE T_CARDEVENT.CARDEVENTDATE BETWEEN '6/4/2009' AND '6/5/2009'
      AND T_CARDEVENT.CARDEVENTTIME BETWEEN '030001' AND '030000'
    Since you say your cardeventtime field is *ALWAYS* 6 characters long (even at 000003? 3 seconds after midnight??), there are two ways to approach this:

    The preferred way, convering your ugly VARCHAR fields to DATETIME (and this is just the *BASIC* part of the query):
    Code:
    SELECT * FROM T_CARDEVENT
    WHERE DATEADD( second, 
                   (   CONVERT(INT,SUBSTRING(cardeventtime,1,2))*3600
                     + CONVERT(INT,SUBSTRING(cardeventtime,3,2))*60
                     + CONVERT(INT,SUBSTRING(cardeventtime,5,2))      ),
                   CONVERT(DATETIME, cardeventdate, 112)
                 ) BETWEEN '6/4/2009 03:00:01' AND '6/5/2009 03:00:00'
    Or you can do it with string concatenation. Since you are using & to string together your strings, I assume you are using VBScript (ASP) or VB.NET (ASP.NET).

    SO:
    Code:
    isosdate = Year(sdate) * 10000 + Month(sdate) * 100 + Day(sdate)
    isoedate = Year(edate) * 10000 + Month(edate) * 100 + Day(edate)
    
    SQL = "SELECT * FROM T_CARDEVENT " _
        & " WHERE cardeventdate + cardeventtime " _
        & " BETWEEN '" & isosdate & "030001' AND '" & isoedate & "030000' "
    Hopefully, starting from that basis, you can see how to JOIN to your other table and get the right answer.

    If not, ask again.

  • #12
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,700
    Thanks
    80
    Thanked 4,658 Times in 4,620 Posts
    As an ASP solution, you might do something like this:
    Code:
    
    isosdate = Year(sdate) * 10000 + Month(sdate) * 100 + Day(sdate)
    isoedate = Year(edate) * 10000 + Month(edate) * 100 + Day(edate)
    
    SQL = "SELECT P.cardno, MIN(X.eventdatetime) AS indatetime, MAX(X.eventdatetime) AS outdatetime " _
        & " FROM t_person AS P " _
        & " LEFT JOIN ( SELECT cardno, cardeventdate+cardeventtime AS eventdatetime " _
        &             " FROM t_cardevent " _
        &             " WHERE cardeventdate + cardeventtime " _
        &             " BETWEEN '" & isosdate & "030001' AND '" & isoedate & "030000' " _
        & " ) AS X " _
        & " ON P.cardno = X.cardno " _
        & " GROUP BY P.cardno " _
        & " ORDER BY P.cardno "
    
    Set RS = conn.Execute( SQL )
    Do Until RS.EOF
        cardno = RS("cardno")
        indatetime = RS("indatetime")
        outdatetime = RS("outdatetime")
        indate = Left(indatetime,8)
        intime = Righ(indatetime,6)
        outdate = Left(outdatetime,8)
        outtime = Righ(outdatetime,6)
        ' now those are still your VERY UGLY strings...20090605 and 175011...
        ' so you get to figure out how to make them human presentable!
        If indatetime = outdatetime Then problem = "Only one date/time in range!" Else problem = ""
    
        ... present the information ...
     
        RS.MoveNext
    Loop
    RS.Close
    
    ...
    Isn't there ANY way we can convince you to FIX THAT BADLY DESIGNED DATABASE!!!

    ????


  •  

    Posting Permissions

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