| Author |
 Topic  |
|
giambona
New Member
USA
84 Posts |
 Posted - 26 June 2001 : 15:25:18
|
I am trying to create a query on SQL Server that only shows records with today's date. In MS Access all I had to put for criteria in the query was =Date(). This isn't working for me in SQL Server. Please help.
Thanks!
|
|
|
HuwR
Forum Admin
United Kingdom
20595 Posts |
Posted - 26 June 2001 : 15:40:26
|
in SQL the equivalent function is GetDate()
|
 |
|
|
giambona
New Member
USA
84 Posts |
Posted - 26 June 2001 : 16:02:47
|
Just tried that and i'm still getting 0 records in my query.
|
|
|
|
HuwR
Forum Admin
United Kingdom
20595 Posts |
Posted - 26 June 2001 : 16:49:32
|
how are you comparing the result of GetDate() with your field ?
Post an example of your SQL.
|
|
|
|
giambona
New Member
USA
84 Posts |
Posted - 26 June 2001 : 17:14:02
|
What I'm doing is creating a "View" and putting "= GETDATE()" for criteria.
|
|
|
|
giambona
New Member
USA
84 Posts |
Posted - 26 June 2001 : 17:16:38
|
here's the sql code for the "view" if it helps:
quote:
SELECT Deals.DealDate, Deals.Title, Deals.Description, Deals.Link,
Posters.Poster, Merchants.Name
FROM pinnacledea.Deals INNER JOIN
pinnacledea.Merchants ON
pinnacledea.Deals.MerchantID = pinnacledea.Merchants.MerchantID
INNER JOIN
pinnacledea.Posters ON
pinnacledea.Deals.Poster = pinnacledea.Posters.PosterID
WHERE (pinnacledea.Deals.DealDate = GETDATE())
|
|
|
|
HuwR
Forum Admin
United Kingdom
20595 Posts |
Posted - 26 June 2001 : 17:26:00
|
Ok, getDate returns a dateTime value, and therfore the time is present, so doing an = comparison will only give you results where the times are the same.
|
|
|
|
giambona
New Member
USA
84 Posts |
Posted - 26 June 2001 : 17:33:13
|
all right, is there a way to get only the date and not both the date and time?
|
|
|
|
mafifi
Junior Member
USA
308 Posts |
Posted - 26 June 2001 : 18:54:35
|
I used the code below to trim a string. Try to use it to trim the time from the GetDate()
============================================
<% istring = "GetDate()"
istring = Right(istring, Len(istring)-Instr(istring, "\"))
response.write istring%>
============================================
Hope this helps.
Thanks,
Mo
Edited by - mafifi on 26 June 2001 18:55:32 |
|
|
|
giambona
New Member
USA
84 Posts |
Posted - 26 June 2001 : 20:08:00
|
i did a little more searching online and i think i found a function that might work. i just put this in criteria for the date field and it seems to be working: quote:
= { fn CURDATE() }
.
Mafifi, thanks for the tip. I'll try it later.
Thanks!
|
|
|
|
Dan Martin
Average Member
USA
528 Posts |
Posted - 27 June 2001 : 01:09:15
|
I could be missing something here, cause I always have to play around with dates myself. But wouldn't this work:
WHERE (pinnacledea.Deals.DealDate = FormatDateTime(GETDATE(), vbShortDate))
Or maybe vbLongDate. I dont really know for sure, but I'm thinking that should work.
|
|
|
|
aspdesigner
Junior Member
165 Posts |
Posted - 27 June 2001 : 01:27:28
|
You're mixing SQL and VBScript commands together there, Dan. But you are on the right track. Assuming your web server and SQL server are in the same time zone, why not just get the current date from ASP and then pass it as a literal in the SQL? Maybe something like -
SQLStmt = SQLStmt & "WHERE thedatefield = '" & CStr(Date()) & "'"
or something similar. Just remember that you should surround a date literal with single quotes (') in SQL Server, and with pound signs (#) in Access. This is an incompatibility between the way Access and SQL Server interpret SQL date literals.
|
|
|
|
giambona
New Member
USA
84 Posts |
Posted - 27 June 2001 : 16:52:47
|
I really wanted to do this as a "view" on the SQL server for simplicity since I'm going to have many pages pulling the same data. I was successful using quote:
= { fn CURDATE() }
to get the current date but i can't change that to get all of the records for yesterday.
|
|
|
|
Dan Martin
Average Member
USA
528 Posts |
Posted - 27 June 2001 : 17:20:23
|
quote:
You're mixing SQL and VBScript commands together there, Dan. But you are on the right track.
DOH! It was late at night. 
How about this from MSDN:
quote:
Shortening a Datetime Field to Date
I can't find a function that will take a datetime field value such as 11/19/98 8:16:00 PM and shorten it to 11/19/98. Do you have a solution?
Answer
Try the following:
select convert (char(20),getdate(),101)
This results in 01/08/1999.
To get the two-digit year, use 1 instead of 101.
Code Sample
Set nocount on
Drop table t1
go
Create table T1 (c1 datetime)
go
Insert T1 values (getdate())
select * from t1
select convert (char(20),c1,101) from t1
select convert (char(20),c1,1) from t1
Edited by - Dan Martin on 27 June 2001 17:21:24 |
|
|
|
giambona
New Member
USA
84 Posts |
Posted - 27 June 2001 : 17:38:24
|
Yep, that works!
Thanks!
|
|
|
|
giambona
New Member
USA
84 Posts |
Posted - 27 June 2001 : 17:42:53
|
oops i spoke too soon
when i change it from "CONVERT (char(20), GETDATE(), 101)" to "CONVERT (char(20), GETDATE() - 1, 101)" to get all records with yesterday's date, it returns one less record than it should.
This is not a coding problem as it is being executed from an SQL server "view"
|
|
|
|
Topic |
|
Topic Locked
