[2005] query problem

[GARAKWAZVO]

Guys i am getting an err msg "D1 invallied identifier". I want to select data from a table where the date value is today such that i declared

Dim d1 As System.DateTime = System.DateTime.Today

and used d1 in my query as
select * from STTM_CUST_AC where DATE_MODIFIED = D1

any suggestions and corrections to the above code

[Ecniv]

strsql= "select * from STTM_CUST_AC where DATE_MODIFIED = " & D1

Try something like that... D1 isn't declared on Sql server/the database, so that is why it errors.

[LinXG]

Hi GARAKWAZVO;

It doesn't look like you've included the date limiters in your query. If you are using MS Access the delimiter is #, so that you query should look like...

strsql= "select * from STTM_CUST_AC where DATE_MODIFIED = #" & D1 & "#"

D1 should be converted to a string before including it in your query. It may work the way you've written but if you get errors do the conversion.

Other databases use ' as the delimiter so you query would look like this...

strsql= "select * from STTM_CUST_AC where DATE_MODIFIED = '" & D1 & "'"

Again check to see if you have to do the string conversion.

If your database uses a different delimiter for dates then use that.

[GARAKWAZVO]

This is much better and is giving me the date in the fommat #04/12/07# which is conflicting with the database value which include time and im recieving an err "not a valid month". i don't want to change my date format to include time cause i want to query all accounts modified today. any suggestions

[Ecniv]

you have two choices.

1) convert the dates in the db so they don't have times
2) use a between statement and add time into the query



example (MS Access)
1)
WHERE format([table].[field],'dd mmm yyyy')='" & format(cdate(D1),'dd mmm yyyy') & "'"

2)
WHERE [table].[field] between #" & d1 & " 00:00:00# and #" & d1 & " 23:59:59"

[LinXG]

Hi GARAKWAZVO;

Nice to see that you're making progress on this.

I am assuming at this point that

- you are using MS Access
- DATE_MODIFIED field is formatted as Date/Time


To deal with the "not a valid month" error you will want to open the Windows
control panel and go to the Regional and language setting application and look at
how the short date is formatted. Click the Customize..., then the Date tab
and look at the Short date format box. This will show you how the date is formatted.


At this point you can go back and look at Ecniv's post to see how formatting is handled.

If for example your short date is formatted to look like 12/04/2007 (DD/mm/yyyy) then your
code and query might look like this...

d1 = format(d1,"DD/mm/yyyy")

strsql= "select * from STTM_CUST_AC where format([DATE_MODIFIED],"DD/mm/yyyy") = #" & D1 & "#"

Your query should now return all records matching the d1 date.

[GARAKWAZVO]

Thanks so much LinXG for your respond

Firstly im using Oracle Database.

the major concern now is that i want to transfare data from the oracle dbase to SQL database and if you can help with any suggestions how i can go around this. im using VS2005 and i will be gratefull if you include the code

[Ecniv]

Have a look at the help files in oracle on the Trunc() function... for dates.

As to the transfer - depends on whether it is a copy of the oracle data or you are moving to sql server.


Probably you need the same/similar table stucture and you can loop in code.
Or you extract to a text file and reimport.

Possibly you can link direct from sql to oracle.. I don't know how fast that might be tho.

[GARAKWAZVO]

I have tried the following and Im getting an err "convestion from string "13/04/2007 12:00:00" to type 'Date' is not valid

d1 = Format(d1, "dd/MM/yyyy 12:00:00")
b = "SELECT STTM_CUSTOMER.CUSTOMER_NAME1, STTM_CUST_ACCOUNT.CUST_AC_NO, STTM_CUST_ACCOUNT.ADDRESS1, STTM_CUST_ACCOUNT.ADDRESS2, STTM_CUST_ACCOUNT.ADDRESS3, STTM_CUST_ACCOUNT.ADDRESS4, STTM_CUST_PERSONAL.TELEPHONE, STTM_CUST_PERSONAL.FAX, STTM_CUSTOMER.COUNTRY, STTM_CUST_PERSONAL.E_MAIL FROM STTM_CUST_ACCOUNT, STTM_CUSTOMER, STTM_CUST_PERSONAL WHERE STTM_CUST_ACCOUNT.CUST_NO = STTM_CUSTOMER.CUSTOMER_NO AND STTM_CUSTOMER.CUSTOMER_NO = STTM_CUST_PERSONAL.CUSTOMER_NO AND STTM_CUSTOMER.CHECKER_DT_STAMP = '" & d1 & "'"

How do i go around this?

[LinXG]

Hi GARAKWAZVO;

Oops, wrong assumptions. I am not an Oracle person so read through Ecniv's suggestion.

So a quick question, what language are you using VB6 or VB.Net?

[Ecniv]

d1 = Format(d1,"dd/mm/yyyy")
b = "SELECT STTM_CUSTOMER.CUSTOMER_NAME1, STTM_CUST_ACCOUNT.CUST_AC_NO, STTM_CUST_ACCOUNT.ADDRESS1, STTM_CUST_ACCOUNT.ADDRESS2, STTM_CUST_ACCOUNT.ADDRESS3, STTM_CUST_ACCOUNT.ADDRESS4, STTM_CUST_PERSONAL.TELEPHONE, STTM_CUST_PERSONAL.FAX, STTM_CUSTOMER.COUNTRY, STTM_CUST_PERSONAL.E_MAIL FROM STTM_CUST_ACCOUNT, STTM_CUSTOMER, STTM_CUST_PERSONAL WHERE STTM_CUST_ACCOUNT.CUST_NO = STTM_CUSTOMER.CUSTOMER_NO AND STTM_CUSTOMER.CUSTOMER_NO = STTM_CUST_PERSONAL.CUSTOMER_NO AND trunc(STTM_CUSTOMER.CHECKER_DT_STAMP) = '" & d1 & "'"

Might work...

[LinXG]

I think I spotted the problem and also discovered you are using VB.Net.

d1 is dimensioned as System.DateTime and VB.Net doesn't like this statement.

d1 = Format(d1,"dd/mm/yyyy")

Try this change

Dim d1 As System.DateTime = System.DateTime.Today
Dim d2 As String
d2 = Format(d1, "dd/MM/yyyy 12:00:00")

b = "....... your SQL statement ... = '" & d2 & "'"

[TINEIZVO]

thanks guys for your comments so far but
1. the error is still popping up but im working flat out to clear it

2. in reply to the following

Have a look at the help files in oracle on the Trunc() function... for dates.

As to the transfer - depends on whether it is a copy of the oracle data or you are moving to sql server.


Probably you need the same/similar table stucture and you can loop in code.
Or you extract to a text file and reimport.

Possibly you can link direct from sql to oracle.. I don't know how fast that might be tho.

the intergration involves two applications with different datatable structures but have similar fields that are the ones which i want to export to SQL from ORACLE so you can help with the best possible way i can do this