I want to query on dates that are equal to today! (SQL)

ingrid

I've been trying for two days to figure this out. All I want to do is query on the job table (from SQL) for all records that the "last modified date" = today. (I'm using the ODBC)

If I type in the date, it works fine, but I need it to automatically compare to the current date. I use:

Select "No_", "Last Modified Date" from job where "Last Modified Date"=GetDate()

It doesn't work I get the message: Expected Lexical Element not found. What can I use to compare for today's date?

RobertMo

SQL's GetDate returns date and time: 2004-05-09 15:03:58.947

In Navision time part for date fields is allways 0: 2004-05-09 00:00:00.000

So you need to "cut" the time part of GetDate. Your SQL should be:

SELECT "No_", "Last Modified Date" FROM job WHERE "Last Modified Date" = CAST(LEFT(CONVERT(varchar,GetDate(),120),10) as datetime)

ingrid

Thank you for the reply.

When I impliment the query, I get "Unexpected extra token: (". So I place the CAST code in the Grid Pane instead then I get "Invalid or Missing Expression"

This is really painful. Do you know what I'm doing wrong?

Thanks!!!
Ingrid

RobertMo

I tested this on SQL Server with QA tool ?

...in the Grid Pane...

What tool are you using ?

hipogrito

Hello:

I'm having exactly the same problem here.

From SQL Server via ODBC I'm trying to read data from a Navision table filtering with the today date (In a SQL Server DTS).

If I write:

SELECT *
FROM MYTABLE
WHERE (MYDATE = { d '2005-06-30' })

It works!

Now I want to change 2005-06-30 for a variable Date... to execute this all days.

I've tried the casting above and it doesn't work... any ideas????

THANK YOU,



Regards
Fran

hipogrito

Hello:

Thanks to Simon Hayes I found that the key was to build dynamicaly the SQL string in SQL Server DTS.

This is one solution for the problem (it would be easier if Navision ODBC would allow something like sysdate.... anyway....):

In SQL Server 2000:

I build a DTS with 2 connections (Origin and Target) and a Data Transformation Task between them with all the transformation changes I want. Until here everything as I had before.

My goal is to read only the data that I want filtering it with a date variable. For example you have some million registers in a table and want to extract each day just the few new records.

Now I add an ActiveX Task with the following code:
(Probably there's an easiest way to do this, I'm not an expert here, but this works...)


'**********************************************************************
' Secuencia de comandos ActiveX Visual Basic
'************************************************************************
Option Explicit

Function Main()


Dim pkg, stp, tsk, cus, sql

'************************************************************************
' Dealing with dates in Navision C/ODBC: They have to be <{d'aaaa-mm-dd'}>
' Being the first d from date... options: t from time and ts from timestamp
' The Month function will give you just one number if you are before October

Dim mes

mes = Month(Date)

If mes <10 Then
mes = "0" & mes
end if

'************************************************************************

Set pkg = DTSGlobalVariables.Parent

' Notice here that DTSStep_DTSDataPumpTask_1 is the name of the Data
' Transformation Task

Set stp = pkg.Steps("DTSStep_DTSDataPumpTask_1")
Set tsk = pkg. Tasks(stp.TaskName)

Set cus = tsk.CustomTask

' Construct the sql statement

sql = "select * from MyTable where (MyDateField > {d'" & Year(Date) & "-" & mes & "-" & Day(Date) & "'})"

'Assign the SourceSQLStatement property of the custom task

cus.SourceSQLStatement = sql

Main = DTSTaskExecResult_Success
End Function




I put a green (Success) line from this Task to the Origin Connection of the Data Transformation Task, so that the ActiveX scritp runs the first, build the SQL sentence, and then the Data Transformation Task Origin Connection runs the SQL Sentence selecting only the proper data. You can see this works just looking at the Origin Connection and in the SQL box you'll see the SQL sentence generated in the last execution of the DTS.

Thanks!

Regards,
Fran