Access date function is Date()

	Home Register Search Help Login

Sponsors

Hosting from $3.99 per month!

Shopping Cart Software
Ecommerce software integrated into Frontpage, Dreamweaver and Golive templates. No monthly fees and available in ASP and PHP versions. dd

Website Templates
We also have a wide selection of Dreamweaver, Expression Web and Frontpage templates as well as webmaster tools and CSS layouts.

Frontpage website templates
Creative Website Templates for FrontPage, Dreamweaver, Flash, SwishMax

Search Forums

Recent Posts

Todays Posts
Most Active posts
Posts since last visit
My Recent Posts
Mark posts read

Access date function is Date()

View related threads: (in this forum | in all forums)

Logged in as: Guest

Users viewing this topic: none

Printable Version

All Forums >> Web Development >> ASP and Database >> Access date function is Date()

Page: [1]


mylu Posts: 128 Joined: 9/11/2006 Status: offline	Access date function is Date() - 2/16/2007 13:18:25 What's SQL's Equivalent? In an access qry this is valid: WHERE tblServiceOrders.DateClosed = date() but is does not fucntion in SQL 2005 Thanks < Message edited by mylu -- 2/16/2007 13:28:05 >


BeTheBall Posts: 6385 Joined: 6/21/2002 From: West Point Utah USA Status: offline	RE: Access date function is Date() - 2/16/2007 14:11:22 I believe it is getDate() instead of Date(). _____________________________ Duane Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs. (in reply to mylu)


mylu Posts: 128 Joined: 9/11/2006 Status: offline	RE: Access date function is Date() - 2/16/2007 15:15:07 Here's the qry SELECT dbo.tblServiceOrders.DateClosed, dbo.tblAccounts.AccountName, dbo.tblAccounts.AccountID, dbo.tblAccounts.Address1, dbo.tblAccounts.Address2, dbo.tblAccounts.City, dbo.tblServiceOrders.SONumber, dbo.tblServiceOrders.BriefDescription, dbo.tblServiceOrders.ContactPhone, dbo.tblServiceOrders.DateOpened, dbo.tblServiceOrders.TimeOpened, dbo.tblServiceOrders.Status, dbo.tblServiceOrders.SOType, dbo.tblServiceOrders.Reference, dbo.tblServiceOrders.GeneralSymptoms, dbo.tblServiceOrders.VerifiedDate, dbo.tblServiceOrders.GeneralResolutions FROM dbo.tblAccounts INNER JOIN dbo.tblServiceOrders ON dbo.tblAccounts.AccountNumber = dbo.tblServiceOrders.AccountNumber WHERE (dbo.tblServiceOrders.DateClosed = GETDATE()) SQL saves it but it returns nothing. I need the qry to return record with the closed date as today. (in reply to BeTheBall)


BeTheBall Posts: 6385 Joined: 6/21/2002 From: West Point Utah USA Status: offline	RE: Access date function is Date() - 2/16/2007 15:59:51 Are you doing this within an asp page or somewhere else? _____________________________ Duane Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs. (in reply to mylu)


mylu Posts: 128 Joined: 9/11/2006 Status: offline	RE: Access date function is Date() - 2/16/2007 16:27:00 It's a SQL view that is called by ASP on a page. If I had the equvilant statement to use in DRW as a criteria value I would us that. But I couldn't figure out how to add the value of "todays date" as a value to filter the quary so I thought I would add a new view to pul only the data from today. (in reply to BeTheBall)


Spooky Posts: 26617 Joined: 11/11/1998 From: Middle Earth Status: offline	RE: Access date function is Date() - 2/16/2007 17:39:40 In the first instance, does it run in the query Analyser? _____________________________ If you arent part of the solution, then there is good money to be made prolonging the problem ��k� Database / DRW help Check out the Tutorials for Frontpage Databases Forum FAQ' s Developers Edition Spooky Login User Registration and Management tool Personalized Frontpage Hosting (in reply to mylu)


mylu Posts: 128 Joined: 9/11/2006 Status: offline	RE: Access date function is Date() - 2/19/2007 10:17:04 Hey spooky, Yes it runs but it does not return any records. If I change GETDATE to todays date 02/19/2007 The qry does return the records. (in reply to Spooky)


BeTheBall Posts: 6385 Joined: 6/21/2002 From: West Point Utah USA Status: offline	RE: Access date function is Date() - 2/19/2007 11:01:04 The problem may be that GetDate() returns the date and time. Maybe this will work: WHERE (dbo.tblServiceOrders.DateClosed = Month(GETDATE()) & "/" & Day(GETDATE() & "/" & Year(GETDATE()) _____________________________ Duane Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs. (in reply to mylu)


BeTheBall Posts: 6385 Joined: 6/21/2002 From: West Point Utah USA Status: offline	RE: Access date function is Date() - 2/19/2007 11:05:03 Have a look here: http://www.sql-server-helper.com/functions/get-date-only.aspx _____________________________ Duane Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs. (in reply to BeTheBall)


mylu Posts: 128 Joined: 9/11/2006 Status: offline	RE: Access date function is Date() - 2/19/2007 12:12:18 Tried that looks like the syntax may not be correct. I get this error: Error in list of function arguments: ')' not recognized. Unable to parse query text. Tried correcting but I haven't got it right yet. I'll take a look at the sorcre page you sent also. Thanks! (in reply to BeTheBall)


BeTheBall Posts: 6385 Joined: 6/21/2002 From: West Point Utah USA Status: offline	RE: Access date function is Date() - 2/19/2007 13:07:34 Yes, there was a missing closing ). WHERE (dbo.tblServiceOrders.DateClosed = Month(GETDATE()) + "/" + Day(GETDATE()) + "/" + Year(GETDATE()) _____________________________ Duane Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs. (in reply to mylu)


mylu Posts: 128 Joined: 9/11/2006 Status: offline	RE: Access date function is Date() - 2/20/2007 9:43:08 Added a closing bracket at the end of the statement WHERE (dbo.tblServiceOrders.DateClosed = Month(GETDATE()) + "/" + Day(GETDATE()) + "/" + Year(GETDATE())) Now I get: Invalid column name '/' Removed the / WHERE (dbo.tblServiceOrders.DateClosed = MONTH(GETDATE()) + DAY(GETDATE()) + YEAR(GETDATE())) Qry executes but still no results. < Message edited by mylu -- 2/20/2007 10:03:06 > (in reply to BeTheBall)


BeTheBall Posts: 6385 Joined: 6/21/2002 From: West Point Utah USA Status: offline	RE: Access date function is Date() - 2/20/2007 10:00:25 You're right. Missed the closing parenthesis for the WHERE clause: WHERE (dbo.tblServiceOrders.DateClosed = Month(GETDATE()) + "/" + Day(GETDATE()) + "/" + Year(GETDATE())) I am not convinced the above will work, but the methods in the link I posted should. _____________________________ Duane Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs. (in reply to mylu)


mylu Posts: 128 Joined: 9/11/2006 Status: offline	RE: Access date function is Date() - 2/20/2007 12:12:24 THanks for all the help. Here's the correct statement. WHERE (dbo.tblServiceOrders.DateClosed = CONVERT(VARCHAR(8), GETDATE(), 1)) Works like a charm! (in reply to BeTheBall)