SQL Query Help

[jash]

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.

[CFMaBiSmAd]

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.

[Old Pedant]

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.

[jash]

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.

[guelphdad]

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.

[Old Pedant]

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...


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...

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

[Old Pedant]

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??

[jash]

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

[jash]

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.

[jash]

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.

[Old Pedant]

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!

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:

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):

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:

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.

[Old Pedant]

As an ASP solution, you might do something like this:



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!!!

????