|
select * from mytable where datecreated > (CONVERT(varchar(30),DATEPART(year, GETDATE())) + '-' + CONVERT(varchar(30),DATEPART(month, GETDATE())) + '-' + CONVERT(varchar(30),DATEPART(day, GETDATE())-1)) + ' 12:07:00'
|
|
|
|
|
Change the type of the column, datecreated, to be of date type instead of string or varchar type. The query would then be
select *
from mytable
where datecreated > to_date(to_char(trunc(sysdate-1),'YYYY-MM-DD')|' 12:07:00'),'YYYY-MM-DD HH:MI:SS');
Chris Meech
We're more like a hobbiest in a Home Depot drooling at all the shiny power tools, rather than a craftsman that makes the chair to an exacting level of comfort by measuring the customer's butt. Marc Clifton
VB is like a toolbox, in the hands of a craftsman, you can end up with some amazing stuff, but without the skills to use it right you end up with Homer Simpson's attempt at building a barbeque or his attempt at a Spice rack. Michael P. Butler
|
|
|
|
|
I would agree Chris. Make sure the DateCreated column is a dateTime datatype and the parameter being passed is the same DateTime datatype. Also, you can put an index on the DateCreated column.
Create Index idx1 on myTable (DateCreated)
Michael
I firmly believe that any man's finest hour, the greatest fulfillment of all that he holds dear, is that moment when he has worked his heart out in a good cause and lies exhausted on the field of battle - victorious.
Vince Lombardi (1913-1970)
|
|
|
|
|
Chris Meech wrote:
select * from mytable where datecreated > to_date(to_char(trunc(sysdate-1),'YYYY-MM-DD')|' 12:07:00'),'YYYY-MM-DD HH:MI:SS');
following not recognised by query analyzer/ T-Sql
trunc
to_date
to_char
|
|
|
|
|
Anonymous wrote:
query analyzer/ T-Sql
Sorry. That suggests you are running this on SQL Server. My example was from an Oracle database. TRUNC is just a truncation routine. In this case, the sysdate call returns a current datetime value from which the time value is truncated. TO_DATE, and TO_CHAR are just conversions routines to convert strings to dates and vice versa. There should be similar functions available on SQL Server, I would assume.
Chris Meech
We're more like a hobbiest in a Home Depot drooling at all the shiny power tools, rather than a craftsman that makes the chair to an exacting level of comfort by measuring the customer's butt. Marc Clifton
VB is like a toolbox, in the hands of a craftsman, you can end up with some amazing stuff, but without the skills to use it right you end up with Homer Simpson's attempt at building a barbeque or his attempt at a Spice rack. Michael P. Butler
|
|
|
|
|
How to get 'IRowsetFind' interface in oledb sdk !
dear all:
i will use following code in my program(C++) :
discover 'pICommandProperties->SetProperties(1,rgInitPropSet);' return failed code 'DB_S_ERRORSOCCURRED'
so 'pIRowset->QueryInterface(IID_IRowsetFind,(void **)&pIRowsetFind);' return failed!
dear all, please help me! how to get 'IRowsetFind' interface in oledb sdk , and how to use 'IRowsetFind->FindNextRow()'
InitProperties[0].dwPropertyID=DBPROP_IRowsetScroll;
InitProperties[0].dwOptions=DBPROPOPTIONS_REQUIRED;
InitProperties[0].dwStatus=DBPROPSTATUS_OK;
InitProperties[0].colid=DB_NULLID;
InitProperties[0].vValue.vt=VT_BOOL;
InitProperties[0].vValue.lVal=VARIANT_TRUE;
InitProperties[1].dwPropertyID=DBPROP_IRowsetFind;
InitProperties[1].dwOptions=DBPROPOPTIONS_REQUIRED;
InitProperties[1].dwStatus=DBPROPSTATUS_OK;
InitProperties[1].colid=DB_NULLID;
InitProperties[1].vValue.vt=VT_BOOL;
InitProperties[1].vValue.lVal=VARIANT_TRUE;
rgInitPropSet[0].cProperties=2;
rgInitPropSet[0].guidPropertySet=DBPROPSET_ROWSET;
rgInitPropSet[0].rgProperties=InitProperties;
hr=pICommandText->QueryInterface(IID_ICommandProperties,(void **)&pICommandProperties);
hr=pICommandProperties->SetProperties(1,rgInitPropSet);
if (DB_S_ERRORSOCCURRED==pICommandProperties->SetProperties(1,rgInitPropSet))
cout<< "test";
if(FAILED(hr))
{
cout<< "OLEDB Error !\r\nExecute Failed To Set Rowset Properties";
}
hr = pIRowset->QueryInterface(IID_IRowsetFind,(void **)&pIRowsetFind);
if(FAILED(hr))
{
cout << "Failed to get IID_IRowsetFind interface.\n";
cout << endl;
} //end if
laker
|
|
|
|
|
I am using the Information Schema to retrive a list of tables and their columns:
SELECT TABLE_NAME, COLUMN_NAME,
DATA_TYPE, CHARACTER_MAXIMUM_LENGTH,
ORDINAL_POSITION
FROM INFORMATION_SCHEMA.columns
ORDER BY TABLE_NAME, ORDINAL_POSITION
This works fine but I get all the tables when I just want the user tables. This their any way to limit the results to just these tables. Also is their a way to determine what columns are primary keys. What other information schema would be useful?
Thanks for your help in advance.
Brian
|
|
|
|
|
Can I use ATL and MFC in an extended stored procedure (ESP)? In Santash Rao's sample "Tutorial on Extended Stored Procedures for MS SQL Server v7.0", he says that SQL Server uses LoadLibrary to load ESP DLL's. MSDN doc says AfxLoadLibrary (rather than LoadLibrary) must be used to load DLLs using MFC. His sample uses MFC. Is there a bug in his sample (the use of MFC) or is he wrong about SQL Server using LoadLibrary?
Jim Cutler
|
|
|
|
|
Yes, I believe you can use these libraries.
AfxLoadLibrary is intended for use within an MFC application or library to load another MFC library which extends MFC classes - what the documentation refers to as an 'MFC Extension DLL'. It simply takes a critical section object before calling LoadLibrary to ensure that certain MFC global structures are not corrupted in a multithreaded program by the code that runs in the extension DLL (basically adding itself to the list of loaded modules).
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
Thanks, Mike. You're right. I had missed MSDN article 249235 which explains that the AfxLoadLibrary doc is incorrect in that AfxLoadLibrary only applies to extension DLLs not all MFC DLLs.  Thanks, again. -- Jim
Jim Cutler
|
|
|
|
|
Hello ,
I just finished developing a program using Microsoft Desktop Engine with C# as the front end. Everything is okay on my system. But, on trying to deploy it on a different system it gives an error message that it can open the database.
This is my first time in using the Microsoft Desktop engine. All this while I have been using Access as my database engine and normally what I do after developing a program is to transfer the Database file in Access after installation to the client’s system and everything will be okay. But with the Desktop Engine what I did was to
1) Install it on the client computer
2) Transfer the Database file to the client’s system.
On running it was giving me message that it can’t open it.
I would be glad if anybody can show me how Microsoft Desktop Engine is deployed together with a program developed using it as the database engine.
Thanks in anticipation.
|
|
|
|
|
I don't think you can just up and transfer the database file. Try doing this instead:
osql -U[username] -P[upass] -S[database] -Q "BACKUP DATABASE [database] to DISK='c:\blah.backup'"
Move blah.backup to the new system, then run the same command with statement "RESTORE DATABASE [database] from DISK='c:\blah.backup'"
This should move the data + schema. You'll probably want to run it as sa, so a sample command is:
osql -Usa -Ppassword -SCOMPNAME\DBNAME -Q "BACKUP test01 TO DISK='C:\backup'"
HTH
-earl-
|
|
|
|
|
I'm guessing the user/login you're using on the production system lacks the access you have on your development system. Check user priviledges in the production database.
|
|
|
|
|
|
Hi Gurus:
i m new to Database programing in visual C++ . i know ADO in Visual C++ with MS Acces but getting confused to do with SQL Server, can any one help me .
Thanks in Advance .....
"Winner's don't do different things , they do things differently "
|
|
|
|
|
what is the problem exactly ?
www.connectionstrings.com[^] has the connection string you need.
after you open the sql db querying it is similar to access.
|
|
|
|
|
Great site! Thanks for posting it.
Some people think of it as a six-pack; I consider it more of a support group.
|
|
|
|
|
Folks,
I mostly do systems programming but got suckered into writing an app for a business run by my brother. This is a POS system which is backed by SQL Server. I'm using WTL on the client and ODBC (because it seemed simple / straightforward) to communicate with the db.
The question: there are a series of queries / reports that are run against the server. Exactly what is included in any individual report is variable -- eg a net sales report may or may not include gift certificates, employee discounts, etc.
My question: what is the best strategy for passing query results around? They can be (1) displayed on screen, (2) saved to CSV, or (3) printed. I expect there may be future options in the future, such as blowing them directly into QuickBooks.
Do people typically pass around the SQL statement handle and let the various consuming functions directly pull data via SQLFetch? That seems like a really bad idea. I currently have the SQL functions isolated and pull query results into an internal data structure -- vectors of structs right now. Unfortunately, I have to create structs for each query and pass around vectors. How do you deal with the fact that there are many optional fields -- just create an entry in the struct for each potential field? Also, what is the most convenient manner to deal with the fact that certain fields may be null?
Are there better ways to do this?
Thanks,
Earl
|
|
|
|
|
Hi every body!
Please tell how can i get data type of a field in table of DB!
Thanks!
xyz
|
|
|
|
|
Assume you are useing SQLServer 2000 , look for INFORMATION_SCHEMA.COLUMNS in SQLSerever online book. You have to query from it.
Mazy
"I think that only daring speculation can lead us further and not accumulation of facts." - Albert Einstein
|
|
|
|
|
Hello,
My name is Jon Ingram and I am 16 years old. I am not sure whether this message should be posted in this forum but i'll put it here anyway.
You might know the OJB tutorial at http://ojb-net.sourceforge.net/[^]. I thought that to fully understand Ojb.Net I would try and create a similar application. My problem is with referencing: after I have added a reference to ojb.Net.Framework, ojb.Net.Common, ojb.Net.Core and ojb.Net.Facade I get three Build Errors:
1.Error: The dependency 'nunit.framework, Version=2.0.6.0, Culture=neutral, PublicKeyToken=96d09a1eb7f44a77' in project 'Car.Ojb.Net' cannot be copied to the run directory because it would conflict with dependency 'nunit.framework, Version=2.1.4.0, Culture=neutral'.
2.Error: The dependency 'nunit.framework, Version=2.1.4.0, Culture=neutral' in project 'Car.Ojb.Net' cannot be copied to the run directory because it would conflict with dependency 'nunit.framework, Version=2.0.6.0, Culture=neutral, PublicKeyToken=96d09a1eb7f44a77'.
3.D:\Documents and Settings\Administrator\Desktop\Programming\Car.Ojb.Net\Car.cs(12): The type or namespace name 'Facade' does not exist in the class or namespace 'Car.Ojb.Net' (are you missing an assembly reference?)
With errors 1 and 2 I added three references: nunit.framework.dll, nunit.uikit.dll and nunit.util.dll but then I get another build error:
Warning: The dependency 'nunit.framework, Version=2.0.6.0, Culture=neutral, PublicKeyToken=96d09a1eb7f44a77' in project 'Car.Ojb.Net' cannot be copied to the run directory because it would overwrite the reference 'nunit.framework, Version=2.1.4.0, Culture=neutral'.
The third error is very stange because I have already made a reference to Ojb.Net as shown below:
using System;
using System.Collections;
using Ojb.Net.Facade.Persist;
using Ojb.Net.Facade.Po;
namespace Car.Ojb.Net
{
///
/// Summary description for Car.Ojb.Net.
///
[Serializable]
class Car : Ojb.Net.Facade.Po.EditableObject
{
..........
Can someone who knows something about Ojb.Net help me?
Kind thanks to anyone who helps me!
Jon Ingram
|
|
|
|
|
ie, i have got a running live sql server.
Now i have installed sql server on one more system in my LAN. I want this new server to be a realtime backup of my live server. so that i can switch when ever there s a problem.
wots the easiest way.
and would be great if only datas(records are backed not the entire settings of the live server).
|
|
|
|
|
sanomni wrote:
wots the easiest way
Replication - though it certainly ain't easy, it will give you the ability to have real-time two-way synchronisation....
Can be a bit of a beast to set up though...have a document lying around if you're interested.
"Now I guess I'll sit back and watch people misinterpret what I just said......"
Christian Graus At The Soapbox
|
|
|
|
|
Is it possible to insert an unserparated string value for a date/time into a SQL datetime field? I'm trying to insert a string of "20040130101532", but SQL keeps telling me it can't convert the type. In the SQL Books Online that I have it's only showing an unserparated string format that contains a date. I can't believe that SQL wouldn't be able to accept a date/time value and only a date value. Is there anyway that anyone know to insert a string like above? Thanks.
- Aaron
|
|
|
|
|
I found a way to do it by inserting the string as "2004-01-30 10:15:32" it seems to work just as well as if it was unseparated.
- Aaron
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
