Date Query Help!

[PQTIII]

I'm trying to query(SQL) data on a char field mm/dd/yyyy (record date). How do I query the data to show all records that are from yesterday?

Thanks pt

 

[mercwrought]

I’m not sure but possibly something like this

select * from mytable where dateadd(dd, 0, datediff(dd, 0,cast([red]date[/red] as datetime))) = dateadd(day,-1,dateadd(dd, 0, datediff(dd, 0,getdate())))

Just replace the mytable with your table and the red date with you char field
You might even be able to get away with out the cast.

 

[iBe]

SELECT *
FROM TABLE
WHERE DATEFIELD = GETDATE()-1

If the column were a datetime data type, use the below for mm/dd/yyyy output...

SELECT Date = CONVERT(VARCHAR(10),DATEFIELD, 101)
FROM TABLE
WHERE DATEFIELD = GETDATE()-1

 

[PQTIII]

The field I'm using is a smalldatetime type. What coversion code is need to match the getdate() type?

Thanks pt

 

[mercwrought]

Ok, I’m back now. Sorry for my absence I had a 400 problem at 2 in the morning and had to go in, I was on call.
Ibe I love your code but the biggest problem with it is that not only are you comparing dates but you are also comparing times. So it would only work if everything matched up.

What I have done is strip the time off both of the sides so that I only get a date comparison.

Now PQTIII you say.

I'm trying to query(SQL) data on a [RED]char field[/RED] mm/dd/yyyy (record date)

Then you say

The field I'm using is a [red]smalldatetime[/red] type

So is it a char field or is it a small date time. If it is a small date time just remove the cast and it should work.

As for the difference with small date time and date time it is difference of date holding capability. Look in the BOL and read on it.

 

[SQLBill]

Check out these two FAQs.

FAQ183-5834
FAQ183-5842

-SQLBill

Posting advice: FAQ481-4875