SQL Query Help

Question

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 030001 to today 030000
• Day before yesterday 0300001 to yesterday 030000

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.

Answer 1

It's hard to sort or filter when your date is in a format like DD-MM-YYYY. In such a format, 01-01-2009 comes before 02-01-2000 !

So, first convert your date with the long years first, and the short seconds last:

YYYY-MM-DD HH:MM:SS

A query like this can do that:

select [id no],
    substring(date,1,2) + '-' + substring(date,4,2) + '-' + 
    substring(date,7,4) + ' ' +
    substring(time,1,2) + ':' + substring(time,3,2) + ':'
    substring(time,5,2) as NormalDate
from YourTable

Now you can easily select all rows for a particular timespan:

select *
from (
    select [id no],
        substring(date,1,5) + '-' + substring(date,7,4) + ' ' +
        substring(time,1,2) + ':' + substring(time,3,2) + ':'
        substring(time,5,2) as NormalDate
    from YourTable
) sub
where '2009-05-01 03:00:00' < NormalDate
and NormalDate < '2009-05-02 03:00:00'