Welcome to WebmasterWorld Guest from 54.234.63.187

Forum Moderators: ocean10000

Message Too Old, No Replies

to compare two dates from sql server

to compare two dates from sql server

     

justify

2:43 pm on Apr 23, 2003 (gmt 0)

10+ Year Member



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

3:03 pm on Apr 23, 2003 (gmt 0)

WebmasterWorld Senior Member txbakers is a WebmasterWorld Top Contributor of All Time 10+ Year Member



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

3:04 pm on Apr 23, 2003 (gmt 0)

10+ Year Member



try

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

duckhunter

3:11 pm on Apr 23, 2003 (gmt 0)

10+ Year Member



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

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

aspdaddy

4:07 pm on Apr 23, 2003 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



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

markusf

6:51 pm on Apr 23, 2003 (gmt 0)

10+ Year Member



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

10:15 am on Apr 24, 2003 (gmt 0)

10+ Year Member



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

10:26 am on Apr 24, 2003 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



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

1:00 pm on Apr 24, 2003 (gmt 0)

10+ Year Member



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

 

Featured Threads

Hot Threads This Week

Hot Threads This Month