to compare two dates from sql server

[justify]

Hi Everbody
I am using SQL Server 2000. I am trying to get date from the server and to compare computer date. In other words I want to show last 24 hours records. I assign date type in SQL Server as a “smalldatetime” and i think date format is dd/mm/yyyy i dont know exactly. I tried some sql query codes but i got erros. How can i create sql query sentencens and assign data type.

Here is my codes? where is the mistake?

<% comdate = Date %>
<% sqltemp = "select*from news where recorddate = "& comdate &" order by haber_id desc " %>
<% sqltemp = "select*from news where recorddate = #" & comdate &"# order by haber_id desc" %>

comdate ="#"& date & "#"
sqltemp = "Select * from news where recorddate = " & comdate
sqltemp = sqltemp& "order by haber_id desc"

Error Types
Microsoft OLE DB Provider for SQL Server (0x80040E14)
Line 1: Incorrect syntax near .04. or ‘#’

[txbakers]

Hi. I'm not a SQL Server expert, but I don't think you need the # to delimit the dates. I think that's only needed in Access, but I could be wrong.

[jimmykav]

try

select * from news where recorddate = #" & Format(comdate, "dd/mmm/yyyy") & " # order by haber_id desc

[duckhunter]

Use the DataAdd function to subtract 1 Day ("d") from getdate()

select*from news where recorddate > dateadd("d",-1, getdate())

[aspdaddy]

> I don't think you need the #
Thats right - in SQL dates are treated like strings, use single quotes not #

[markusf]

select * from mytable where date between 'datea' and 'dateb'

make sure dateb is one day greater then datea. If you don't do this expect wierd results.

[justify]

Hi,
I tried Jimmykav code but i get this error;

Error Type:
Microsoft VBScript runtime (0x800A00D)
Type mismatch:’Format’

And i tried another code examples in the form but i get error. I solved the problem. I assign date type in SQL Server as a not “smalldatetime” or “datettime” i assigned “int”
and then i use this blow the function and then solved the problem, but i couldn’t portray this solve
Still i dont understand why i dont use as a date type? Thanks for helping.

Here is my code.

Function TarihCevir(Tarih,CevirmeTipi)
Dim Gun, Ay, Yil
Select Case CevirmeTipi
Case "Tarih"
Gun=Mid(Tarih,7,2)
Ay=Mid(Tarih,5,2)
Yil=Mid(Tarih,1,4)
TarihCevir=Gun&"."&Ay&"."&Yil
Case "Rakam"
Gun=Mid(Tarih,1,2)
Ay=Mid(Tarih,4,2)
Yil=Mid(Tarih,7,4)
TarihCevir=Gun&Ay&Yil
CASE "TR"
Gun=Mid(Tarih,4,2)
Ay=Mid(Tarih,1,2)
Yil=Mid(Tarih,7,4)
TarihCevir=Gun&"."&Ay&"."&Yil
CASE "USA"
Gun=Mid(Tarih,1,2)
Ay=Mid(Tarih,4,2)
Yil=Mid(Tarih,7,4)
TarihCevir=Ay&"/"&Gun&"/"&Yil
Case Else
TarihCevir=Tarih
End Select
End Function

Tarihim=Date()
Bugun=TarihCevir(Tarihim,"Rakam")
SQL = "SELECT * FROM news WHERE tarih = "&Bugun&""
SQL = SQL& "ORDER BY haber_id desc"

[aspdaddy]

Dates can be so much trouble, probably coz there are different ways to do the same thing,.

Whenever I get stuck I refer to this guide, very good info:
[aspfaq.com...]

[duckhunter]

I want to show last 24 hours records

This doesn't work? Now minus 1 day:

select * from news where recorddate > dateadd("d",-1, getdate())