homepage Welcome to WebmasterWorld Guest from 54.197.111.87
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / Microsoft / Microsoft IIS Web Server and ASP.NET
Forum Library, Charter, Moderators: ocean10000

Microsoft IIS Web Server and ASP.NET Forum

    
to compare two dates from sql server
to compare two dates from sql server
justify




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

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




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

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




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

try

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

duckhunter




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

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

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

aspdaddy




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

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

markusf




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

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




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

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




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

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




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

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

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Microsoft / Microsoft IIS Web Server and ASP.NET
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved