Newbie Question on =Today()

I need to pull some reports from an older ERP system in the US via ODBC
I have the report in play, but want to limit the criteria to the items
shipped today
I have a TODAY_FIELD as a calculated Field with the parameter =TODAY()
The Format of the Oracle view is mm/dd/yyyy hh:mm:ss AM and the
TODAY_FIELD
matches that. (ie 8/30/2006 12:00:00 AM)
So I am looking for the criteria for WHERE (REAL_SHIP_DATE >
TODAY()) which will not generate and ORA-00904 error.
Can anyone please help?getdate() is the system date to use. Or am I understanding what you want
wrong?
"VB" <vodkablokey@.gmail.com> wrote in message
news:1156941597.512536.214380@.b28g2000cwb.googlegroups.com...
>I need to pull some reports from an older ERP system in the US via ODBC
> I have the report in play, but want to limit the criteria to the items
> shipped today
> I have a TODAY_FIELD as a calculated Field with the parameter =TODAY()
> The Format of the Oracle view is mm/dd/yyyy hh:mm:ss AM and the
> TODAY_FIELD
> matches that. (ie 8/30/2006 12:00:00 AM)
> So I am looking for the criteria for WHERE (REAL_SHIP_DATE >
> TODAY()) which will not generate and ORA-00904 error.
> Can anyone please help?
>|||Ben Watts wrote:
> getdate() is the system date to use. Or am I understanding what you want
> wrong?
> "VB" <vodkablokey@.gmail.com> wrote in message
> news:1156941597.512536.214380@.b28g2000cwb.googlegroups.com...
> >I need to pull some reports from an older ERP system in the US via ODBC
> >
> > I have the report in play, but want to limit the criteria to the items
> > shipped today
> >
> > I have a TODAY_FIELD as a calculated Field with the parameter =TODAY()
> >
> > The Format of the Oracle view is mm/dd/yyyy hh:mm:ss AM and the
> > TODAY_FIELD
> > matches that. (ie 8/30/2006 12:00:00 AM)
> >
> > So I am looking for the criteria for WHERE (REAL_SHIP_DATE >
> > TODAY()) which will not generate and ORA-00904 error.
> >
> > Can anyone please help?
> >
I am looking to report all shipping transactions (TRANSACTION_CODE ='OESHIP') which have happened since Midnight.
I know = TODAY() will give you 12:00am today, how do I pass this as a
criteria to a ODBC database call?
I had tried =TODAY() but this is rejected, it seems to suggest I need
to use TO_DATE, but I don't know how to do this either.
Thanks for getting back though :)|||Try:
WHERE REAL_SHIP_DATE > CAST( CONVERT( VARCHAR(8), GETDATE(), 112) AS DATETIME)
This takes the the curent system time, as suggested by Ben, chops of the
time and returns a DateTime value at the very start of the day, similar to
the vb Today() function.
HTH,
Magendo_man
"VB" wrote:
> Ben Watts wrote:
> > getdate() is the system date to use. Or am I understanding what you want
> > wrong?
> > "VB" <vodkablokey@.gmail.com> wrote in message
> > news:1156941597.512536.214380@.b28g2000cwb.googlegroups.com...
> > >I need to pull some reports from an older ERP system in the US via ODBC
> > >
> > > I have the report in play, but want to limit the criteria to the items
> > > shipped today
> > >
> > > I have a TODAY_FIELD as a calculated Field with the parameter =TODAY()
> > >
> > > The Format of the Oracle view is mm/dd/yyyy hh:mm:ss AM and the
> > > TODAY_FIELD
> > > matches that. (ie 8/30/2006 12:00:00 AM)
> > >
> > > So I am looking for the criteria for WHERE (REAL_SHIP_DATE >
> > > TODAY()) which will not generate and ORA-00904 error.
> > >
> > > Can anyone please help?
> > >
> I am looking to report all shipping transactions (TRANSACTION_CODE => 'OESHIP') which have happened since Midnight.
> I know = TODAY() will give you 12:00am today, how do I pass this as a
> criteria to a ODBC database call?
> I had tried =TODAY() but this is rejected, it seems to suggest I need
> to use TO_DATE, but I don't know how to do this either.
> Thanks for getting back though :)
>|||magendo_man wrote:
> Try:
> WHERE REAL_SHIP_DATE > CAST( CONVERT( VARCHAR(8), GETDATE(), 112) AS DATETIME)
> This takes the the curent system time, as suggested by Ben, chops of the
> time and returns a DateTime value at the very start of the day, similar to
> the vb Today() function.
> HTH,
> Magendo_man
> "VB" wrote:
> >
> > Ben Watts wrote:
> > > getdate() is the system date to use. Or am I understanding what you want
> > > wrong?
> > > "VB" <vodkablokey@.gmail.com> wrote in message
> > > news:1156941597.512536.214380@.b28g2000cwb.googlegroups.com...
> > > >I need to pull some reports from an older ERP system in the US via ODBC
> > > >
> > > > I have the report in play, but want to limit the criteria to the items
> > > > shipped today
> > > >
> > > > I have a TODAY_FIELD as a calculated Field with the parameter =TODAY()
> > > >
> > > > The Format of the Oracle view is mm/dd/yyyy hh:mm:ss AM and the
> > > > TODAY_FIELD
> > > > matches that. (ie 8/30/2006 12:00:00 AM)
> > > >
> > > > So I am looking for the criteria for WHERE (REAL_SHIP_DATE >
> > > > TODAY()) which will not generate and ORA-00904 error.
> > > >
> > > > Can anyone please help?
> > > >
> >
> > I am looking to report all shipping transactions (TRANSACTION_CODE => > 'OESHIP') which have happened since Midnight.
> >
> > I know = TODAY() will give you 12:00am today, how do I pass this as a
> > criteria to a ODBC database call?
> >
> > I had tried =TODAY() but this is rejected, it seems to suggest I need
> > to use TO_DATE, but I don't know how to do this either.
> >
> > Thanks for getting back though :)
> >
> >
THanks, that would do the trick.
When I try it though I get a new error, INVALID or MISSING EXPRESSION
Any ideas?|||VB wrote:
> magendo_man wrote:
> > Try:
> >
> > WHERE REAL_SHIP_DATE > CAST( CONVERT( VARCHAR(8), GETDATE(), 112) AS DATETIME)
> >
> > This takes the the curent system time, as suggested by Ben, chops of the
> > time and returns a DateTime value at the very start of the day, similar to
> > the vb Today() function.
> >
> > HTH,
> > Magendo_man
> >
> > "VB" wrote:
> >
> > >
> > > Ben Watts wrote:
> > > > getdate() is the system date to use. Or am I understanding what you want
> > > > wrong?
> > > > "VB" <vodkablokey@.gmail.com> wrote in message
> > > > news:1156941597.512536.214380@.b28g2000cwb.googlegroups.com...
> > > > >I need to pull some reports from an older ERP system in the US via ODBC
> > > > >
> > > > > I have the report in play, but want to limit the criteria to the items
> > > > > shipped today
> > > > >
> > > > > I have a TODAY_FIELD as a calculated Field with the parameter =TODAY()
> > > > >
> > > > > The Format of the Oracle view is mm/dd/yyyy hh:mm:ss AM and the
> > > > > TODAY_FIELD
> > > > > matches that. (ie 8/30/2006 12:00:00 AM)
> > > > >
> > > > > So I am looking for the criteria for WHERE (REAL_SHIP_DATE >
> > > > > TODAY()) which will not generate and ORA-00904 error.
> > > > >
> > > > > Can anyone please help?
> > > > >
> > >
> > > I am looking to report all shipping transactions (TRANSACTION_CODE => > > 'OESHIP') which have happened since Midnight.
> > >
> > > I know = TODAY() will give you 12:00am today, how do I pass this as a
> > > criteria to a ODBC database call?
> > >
> > > I had tried =TODAY() but this is rejected, it seems to suggest I need
> > > to use TO_DATE, but I don't know how to do this either.
> > >
> > > Thanks for getting back though :)
> > >
> > >
> THanks, that would do the trick.
> When I try it though I get a new error, INVALID or MISSING EXPRESSION
> Any ideas?
SOrry Folks, I realised (eventually) instead of trying to re-invent the
wheel I should just create a view in the database with the sysdate -1
call and just report the view.

Newbie Question

I have a table called cr. Within that table I have the colums c_id, c_type, first_name, last_name. I need help creating a query that will pull from an excel file that have the same colum names. This excel file is automaticaly generated from one of our sys
tems and the c_id will never change for the record.
So this is what im wanting to do. I need it to look at the xls file and if there are new records in that xls file it will add those to the table or if the records have changed it will update the records within the table based on the c_id. I think this is
feasable but I can't seem to get it to work. And example would be great.
Thanks
Eric
1) DTS package
2) SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\MyExcel.xls";
User ID=Admin;Password=;Extended properties=Excel 8.0')...Book1$
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:254E3DCB-06A0-4D59-9BAB-120B33A59F0B@.microsoft.com...
> I have a table called cr. Within that table I have the colums c_id,
c_type, first_name, last_name. I need help creating a query that will pull
from an excel file that have the same colum names. This excel file is
automaticaly generated from one of our systems and the c_id will never
change for the record.
> So this is what im wanting to do. I need it to look at the xls file and if
there are new records in that xls file it will add those to the table or if
the records have changed it will update the records within the table based
on the c_id. I think this is feasable but I can't seem to get it to work.
And example would be great.
> Thanks
|||and that will insert or update any records?
THanks
"Uri Dimant" wrote:

> Eric
> 1) DTS package
> 2) SELECT *
> FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
> 'Data Source="c:\MyExcel.xls";
> User ID=Admin;Password=;Extended properties=Excel 8.0')...Book1$
>
> "Eric" <Eric@.discussions.microsoft.com> wrote in message
> news:254E3DCB-06A0-4D59-9BAB-120B33A59F0B@.microsoft.com...
> c_type, first_name, last_name. I need help creating a query that will pull
> from an excel file that have the same colum names. This excel file is
> automaticaly generated from one of our systems and the c_id will never
> change for the record.
> there are new records in that xls file it will add those to the table or if
> the records have changed it will update the records within the table based
> on the c_id. I think this is feasable but I can't seem to get it to work.
> And example would be great.
>
>
sql

Newbie Question

I have a table called cr. Within that table I have the colums c_id, c_type,
first_name, last_name. I need help creating a query that will pull from an e
xcel file that have the same colum names. This excel file is automaticaly ge
nerated from one of our sys
tems and the c_id will never change for the record.
So this is what im wanting to do. I need it to look at the xls file and if t
here are new records in that xls file it will add those to the table or if t
he records have changed it will update the records within the table based on
the c_id. I think this is
feasable but I can't seem to get it to work. And example would be great.
ThanksEric
1) DTS package
2) SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\MyExcel.xls";
User ID=Admin;Password=;Extended properties=Excel 8.0')...Book1$
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:254E3DCB-06A0-4D59-9BAB-120B33A59F0B@.microsoft.com...
> I have a table called cr. Within that table I have the colums c_id,
c_type, first_name, last_name. I need help creating a query that will pull
from an excel file that have the same colum names. This excel file is
automaticaly generated from one of our systems and the c_id will never
change for the record.
> So this is what im wanting to do. I need it to look at the xls file and if
there are new records in that xls file it will add those to the table or if
the records have changed it will update the records within the table based
on the c_id. I think this is feasable but I can't seem to get it to work.
And example would be great.
> Thanks|||and that will insert or update any records?
THanks
"Uri Dimant" wrote:

> Eric
> 1) DTS package
> 2) SELECT *
> FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
> 'Data Source="c:\MyExcel.xls";
> User ID=Admin;Password=;Extended properties=Excel 8.0')...Book1$
>
> "Eric" <Eric@.discussions.microsoft.com> wrote in message
> news:254E3DCB-06A0-4D59-9BAB-120B33A59F0B@.microsoft.com...
> c_type, first_name, last_name. I need help creating a query that will pull
> from an excel file that have the same colum names. This excel file is
> automaticaly generated from one of our systems and the c_id will never
> change for the record.
> there are new records in that xls file it will add those to the table or i
f
> the records have changed it will update the records within the table based
> on the c_id. I think this is feasable but I can't seem to get it to work.
> And example would be great.
>
>

Newbie Question

I have a table called cr. Within that table I have the colums c_id, c_type, first_name, last_name. I need help creating a query that will pull from an excel file that have the same colum names. This excel file is automaticaly generated from one of our systems and the c_id will never change for the record.
So this is what im wanting to do. I need it to look at the xls file and if there are new records in that xls file it will add those to the table or if the records have changed it will update the records within the table based on the c_id. I think this is feasable but I can't seem to get it to work. And example would be great.
ThanksEric
1) DTS package
2) SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\MyExcel.xls";
User ID=Admin;Password=;Extended properties=Excel 8.0')...Book1$
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:254E3DCB-06A0-4D59-9BAB-120B33A59F0B@.microsoft.com...
> I have a table called cr. Within that table I have the colums c_id,
c_type, first_name, last_name. I need help creating a query that will pull
from an excel file that have the same colum names. This excel file is
automaticaly generated from one of our systems and the c_id will never
change for the record.
> So this is what im wanting to do. I need it to look at the xls file and if
there are new records in that xls file it will add those to the table or if
the records have changed it will update the records within the table based
on the c_id. I think this is feasable but I can't seem to get it to work.
And example would be great.
> Thanks

newbie lookup table

How would I run a query, on the server side, on a table that would pull from
a second table (like a lookup table) and write the result to each row in a
record set of the first table?
ThanksHi,
UPDATE TableOne T1
SET T1.ColumnToUpdate = T2.ColumnToRetrieveValueFrom
FROM Table1 as T1
INNER JOIN Table2 as T2
ON (T1.RelatedKey = T2.RelatedKey)
Allright ?
HTH, Jens Smeyer.
http://www.sqlserver2005.de
--
"HollyylloH" <HollyylloH@.discussions.microsoft.com> schrieb im Newsbeitrag
news:64F2B05C-A982-4709-9DB2-2F5B9431EF88@.microsoft.com...
> How would I run a query, on the server side, on a table that would pull
> from
> a second table (like a lookup table) and write the result to each row in a
> record set of the first table?
> Thanks|||UPDATE TableOne
SET T1.ColumnToUpdate
= (SELECT T2.ColumnToRetrieveValueFrom
FROM Table2 AS T2
WHERE TableOne.RelatedKey = T2.RelatedKey) ;