|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Problem with DateAdd & GetDate()Hi,
I've been struggling trying to figure out this code to no avail. I need to query a date time field on an external database to give me data if the Closed_Time has changed within the last day. The Closed_Time field looks like this 9/1/2005 11:59:00 AM. The code I've been attempting to use is (Closed_Time > DATEADD(day, - 7, GETDATE()) and when that is executed a Lexical Element error message is returned. I've searched on the boards for other variations of this code and none have worked so far. I'm hoping someone can shed a little light on it for me. Thanks. What error message?
Also look up DATEDIFF in Books Online, if you haven't already. ML --- http://milambda.blogspot.com/ ML wrote:
> What error message? Why would DATEDIFF be relevant?> > Also look up DATEDIFF in Books Online, if you haven't already. > -- Microsoft MVP -- ASP/ASP.NET Please reply to the newsgroup. The email account listed in my From header is my spam trap, so I don't check it very often. You will get a quicker response by posting to the newsgroup. As an alternative. Only after I've sent the message have I realised that
DATEADD enables the use of indexes. ML --- http://milambda.blogspot.com/ > I've been struggling trying to figure out this code to no avail. I need Assume the current date/time is 20060126 11:07:13.003. What does the > to > query a date time field on an external database to give me data if the > Closed_Time has changed within the last day. The Closed_Time field looks > like this 9/1/2005 11:59:00 AM. The code I've been attempting to use is > (Closed_Time > DATEADD(day, - 7, GETDATE()) and when that is executed a > Lexical Element error message is returned. I've searched on the boards > for > other variations of this code and none have worked so far. evaluation of the expression "DATEADD(day, - 7, GETDATE()" yield? It is likely not the value you desire. However, your description doesn't match this query - you said "... has changed within the last day" yet your expression subtracts 7 days from the current date/time. At a minimum, the following should help you understand datetime values and how they should be used. http://www.karaszi.com/sqlserver/info_datetime.asp And as everyone else suggested, post the error message - exactly as it appears. Perhaps it would help to define/identify what "external database" means - I've never seen sql server return an error message with the text "lexical element error" in any form. Jason wrote:
> Hi, Using OPENQUERY?> > I've been struggling trying to figure out this code to no avail. I > need to query a date time field on an external database to give me > data if the Closed_Time has changed within the last day. The I suggest asking for help in a group devoted to the type of external> Closed_Time field looks like this 9/1/2005 11:59:00 AM. The code > I've been attempting to use is (Closed_Time > DATEADD(day, - 7, > GETDATE()) and when that is executed a Lexical Element error message > is returned. I've searched on the boards for other variations of > this code and none have worked so far. > > I'm hoping someone can shed a little light on it for me. > database you are using. It sounds as if that rdbms requires a different syntax for the DATEADD function than that required by Transact-SQL. Bob Barrows -- Microsoft MVP -- ASP/ASP.NET Please reply to the newsgroup. The email account listed in my From header is my spam trap, so I don't check it very often. You will get a quicker response by posting to the newsgroup. The exact error message is
ADO Error Driver) Expected lexical element not found :) I was informed that it could be an ODBC issue. I get data from a company and I use their own ODBC connection to pull it. We're under the assumption that we are limited becasue of the connection. With that in mind I just attempted this Closed_Time > DATEADD(day, -1, 1/26/2006 10:02:25 AM)) and the same error was given. If I'm limited by the ODBC connection, what other way can I get records that were just updated in the last day. Thanks for all the help, I appreciate it. Jason Show quote "Bob Barrows [MVP]" wrote: > Jason wrote: > > Hi, > > > > I've been struggling trying to figure out this code to no avail. I > > need to query a date time field on an external database to give me > > Using OPENQUERY? > > > data if the Closed_Time has changed within the last day. The > > Closed_Time field looks like this 9/1/2005 11:59:00 AM. The code > > I've been attempting to use is (Closed_Time > DATEADD(day, - 7, > > GETDATE()) and when that is executed a Lexical Element error message > > is returned. I've searched on the boards for other variations of > > this code and none have worked so far. > > > > I'm hoping someone can shed a little light on it for me. > > > I suggest asking for help in a group devoted to the type of external > database you are using. It sounds as if that rdbms requires a different > syntax for the DATEADD function than that required by Transact-SQL. > > Bob Barrows > -- > Microsoft MVP -- ASP/ASP.NET > Please reply to the newsgroup. The email account listed in my From > header is my spam trap, so I don't check it very often. You will get a > quicker response by posting to the newsgroup. > > > "ODBC" tells us nothing. Is it an Oracle database? Foxpro? MySQL? DB2? (I've
never seen a Jet error message containing the word "Lexical" so I think Access can be ruled out) You have to use syntax that works in the external database. We cannot help you with that without knowing the type of database involved. And even then, the chances of finding somebody who is knowledgeable about that database in a SQL Server group are not great. Could you ask a developer in that "company" to write a query that does what is needed and send it to you? Bob Barrows Jason wrote: Show quote > The exact error message is > > ADO Error Driver) Expected lexical element not found :) > > I was informed that it could be an ODBC issue. I get data from a > company and I use their own ODBC connection to pull it. We're under > the assumption that we are limited becasue of the connection. With > that in mind I just attempted this Closed_Time > DATEADD(day, -1, > 1/26/2006 10:02:25 AM)) and the same error was given. If I'm limited > by the ODBC connection, what other way can I get records that were > just updated in the last day. > > Thanks for all the help, I appreciate it. > > Jason > > "Bob Barrows [MVP]" wrote: > >> Jason wrote: >>> Hi, >>> >>> I've been struggling trying to figure out this code to no avail. I >>> need to query a date time field on an external database to give me >> >> Using OPENQUERY? >> >>> data if the Closed_Time has changed within the last day. The >>> Closed_Time field looks like this 9/1/2005 11:59:00 AM. The code >>> I've been attempting to use is (Closed_Time > DATEADD(day, - 7, >>> GETDATE()) and when that is executed a Lexical Element error message >>> is returned. I've searched on the boards for other variations of >>> this code and none have worked so far. >>> >>> I'm hoping someone can shed a little light on it for me. >>> >> I suggest asking for help in a group devoted to the type of external >> database you are using. It sounds as if that rdbms requires a >> different syntax for the DATEADD function than that required by >> Transact-SQL. >> >> Bob Barrows >> -- >> Microsoft MVP -- ASP/ASP.NET >> Please reply to the newsgroup. The email account listed in my From >> header is my spam trap, so I don't check it very often. You will get >> a quicker response by posting to the newsgroup. -- Microsoft MVP -- ASP/ASP.NET Please reply to the newsgroup. The email account listed in my From header is my spam trap, so I don't check it very often. You will get a quicker response by posting to the newsgroup. Ok it looks like in order to query that database I need to use this syntax.
(Closed_Time > { ts '2006-01-25 00:00:00' }) Is there then a way for me to store the current date in this format in another table perhaps and then pass it along to this query or would that fail on me as well? I'm just looking to automate it as much as possible. Thanks. Show quote "Bob Barrows [MVP]" wrote: > "ODBC" tells us nothing. Is it an Oracle database? Foxpro? MySQL? DB2? (I've > never seen a Jet error message containing the word "Lexical" so I think > Access can be ruled out) > > You have to use syntax that works in the external database. We cannot help > you with that without knowing the type of database involved. And even then, > the chances of finding somebody who is knowledgeable about that database in > a SQL Server group are not great. > > Could you ask a developer in that "company" to write a query that does what > is needed and send it to you? > > Bob Barrows > > Jason wrote: > > The exact error message is > > > > ADO Error Driver) Expected lexical element not found :) > > > > I was informed that it could be an ODBC issue. I get data from a > > company and I use their own ODBC connection to pull it. We're under > > the assumption that we are limited becasue of the connection. With > > that in mind I just attempted this Closed_Time > DATEADD(day, -1, > > 1/26/2006 10:02:25 AM)) and the same error was given. If I'm limited > > by the ODBC connection, what other way can I get records that were > > just updated in the last day. > > > > Thanks for all the help, I appreciate it. > > > > Jason > > > > "Bob Barrows [MVP]" wrote: > > > >> Jason wrote: > >>> Hi, > >>> > >>> I've been struggling trying to figure out this code to no avail. I > >>> need to query a date time field on an external database to give me > >> > >> Using OPENQUERY? > >> > >>> data if the Closed_Time has changed within the last day. The > >>> Closed_Time field looks like this 9/1/2005 11:59:00 AM. The code > >>> I've been attempting to use is (Closed_Time > DATEADD(day, - 7, > >>> GETDATE()) and when that is executed a Lexical Element error message > >>> is returned. I've searched on the boards for other variations of > >>> this code and none have worked so far. > >>> > >>> I'm hoping someone can shed a little light on it for me. > >>> > >> I suggest asking for help in a group devoted to the type of external > >> database you are using. It sounds as if that rdbms requires a > >> different syntax for the DATEADD function than that required by > >> Transact-SQL. > >> > >> Bob Barrows > >> -- > >> Microsoft MVP -- ASP/ASP.NET > >> Please reply to the newsgroup. The email account listed in my From > >> header is my spam trap, so I don't check it very often. You will get > >> a quicker response by posting to the newsgroup. > > -- > Microsoft MVP -- ASP/ASP.NET > Please reply to the newsgroup. The email account listed in my From > header is my spam trap, so I don't check it very often. You will get a > quicker response by posting to the newsgroup. > > > But
Jason wrote: > Ok it looks like in order to query that database I need to use this Only if you store it as a string in a varchar column.> syntax. > > (Closed_Time > { ts '2006-01-25 00:00:00' }) > > Is there then a way for me to store the current date in this format in > another table perhaps > and then pass it along to this query or would I don't think you have to do that. There are ODBC functions you might be> that fail on me as well? I'm just looking to automate it as much as > possible. > able to use: http://msdn.microsoft.com/library/en-us/odbc/htm/odbcscalar_functions.asp -- Microsoft MVP -- ASP/ASP.NET Please reply to the newsgroup. The email account listed in my From header is my spam trap, so I don't check it very often. You will get a quicker response by posting to the newsgroup. The query is taking place via a SQL task in a DTS package.
Show quote "Bob Barrows [MVP]" wrote: > Jason wrote: > > Hi, > > > > I've been struggling trying to figure out this code to no avail. I > > need to query a date time field on an external database to give me > > Using OPENQUERY? > > > data if the Closed_Time has changed within the last day. The > > Closed_Time field looks like this 9/1/2005 11:59:00 AM. The code > > I've been attempting to use is (Closed_Time > DATEADD(day, - 7, > > GETDATE()) and when that is executed a Lexical Element error message > > is returned. I've searched on the boards for other variations of > > this code and none have worked so far. > > > > I'm hoping someone can shed a little light on it for me. > > > I suggest asking for help in a group devoted to the type of external > database you are using. It sounds as if that rdbms requires a different > syntax for the DATEADD function than that required by Transact-SQL. > > Bob Barrows > -- > Microsoft MVP -- ASP/ASP.NET > Please reply to the newsgroup. The email account listed in my From > header is my spam trap, so I don't check it very often. You will get a > quicker response by posting to the newsgroup. > > >  |
|||||||||||||||||||||||
Other interesting topics