thanks it worked

There seems to be something wrong i here i replied thrice already and nothing went through.

I seemed to have solved the problem halfway.

I removed the SELECT from the while...

Thanks all, its seems that i had to use a little discretion from everyone who pitched in something, and come up with the best solution.

My final query looks like...

It still doesnt match anything even though i try 

JOIN changed:

SELECT dbo.DEC_TXN.*

  FROM dbo.DEC_TXN

 INNER JOIN dbo.CALENDAR

    ON Convert(varchar(8), dbo.DEC_TXN.DATE_LOAD,112) = Convert(varchar(8),dbo.CALENDAR.dt,112)

 WHERE Convert(varchar(8),dbo.DEC_TXN.DATE_LOAD,112) = Convert(varchar(8),getdate(),112)

Yes my...

I have created the Calendar table so as to display all public holidays

Snip

dt                                                     isWeekday isHoliday Y      FY     Q    M    D    DW   monthname dayname   W    HolidayDescription              

------------------------------------------------------ --------- --------- ------ ------...

SELECT dbo.DEC_TXN.*

FROM dbo.DEC_TXN

INNER JOIN dbo.CALENDAR ON dbo.DEC_TXN.DATE_LOAD = dbo.CALENDAR.dt

where dbo.DEC_TXN.DATE_LOAD between convert(datetime, convert(varchar(10),dateadd(dd,-1,getdate(),101) + '07:00:00')) and getdate()

Server: Msg 174, Level 15, State 1, Line 4

The dateadd function requires 3...

I actually need to return data everything including from yest.. 07:00am till when ever the query is run. but i cant even get the getdate to work without the date...