|
Don't know, except you are in the wrong forum
Bob
Ashfield Consultants Ltd
|
|
|
|
|
hi. I'm trying to locate the date/time of a certain data. I've tried the typical "SELECT * FROM this_table WHERE column_name = '<some date>'. This does not return anything. Can someone help me with this? I want to search quickly for the specified date and time. Thanks! </some>
|
|
|
|
|
Hi,
If the data type of column_name is datetime, 'some date' should be convertible to datetime implicitely or you should convert it explicitely. For conversion use CONVERT function. For example:
column_name = CONVERT(datetime, '2008-09-07 12:02:00', 120)
Also notice that this is exact match so minutes, seconds and so on must match.
Mika
|
|
|
|
|
Wow thanks. However, What if I would want to search all records under 2008-09-07?
|
|
|
|
|
If you know for sure that the time is always at midnight, then you can use equality like:
column_name = CONVERT(datetime, '2008-09-07', 105)
If time portion can vary, use ranges like:
... column_name >= CONVERT(datetime, '2008-09-07', 105)
AND column_name < CONVERT(datetime, '2008-09-08', 105)
Hope this helps,
Mika
|
|
|
|
|
I tried to use "select @@identity" in my ADO/ACCESS/C++ application to retrieved
the primary key of the last inserted record but failed
the table only contain two column
1.cNo ---> is a primary key (autonumber)
2.cText ----> just dummy field (char 255)
this is the code
CString strConnect = _T("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\\test\\test.mdb;");
ADODB::_ConnectionPtr connectionPtr;
hr = connectionPtr.CreateInstance( __uuidof( ADODB::Connection ) );
connectionPtr->Open((const TCHAR*)strConnect,_T(""),_T(""),NULL);
CString queryString = _T("insert into test_table (cText) values ('dummy')");
connectionPtr->Execute((LPCTSTR)queryString,NULL,ADODB::adCmdText);
CString getIDQueryString = _T("select @@identity");
ADODB::_RecordSetPtr rPtr = connectionPtr->Execute((LPCTSTR)getIDQueryString,NULL,ADODB::adCmdText);
int lastInserted = rPtr->Fields->GetItem(_T("cNo"))->GetValue().lVal;
The execution of getIDQueryString is fine.
but I got an error when try to get the value of 'cNo' , saying that it cannot be found (cNo is the table primary key with autonumber).
Hope someone can give me some solution.
Thank in advance.
|
|
|
|
|
I could be wrong (it's been so long since i used Access) but I don't think it supports this kind of operation - all you can do is runa "select max(id)" or "select top 1 id from.. order by id desc" type query... but note that these are NOT specific to your connection. If someone else happens to insert a record between your insert and you calling this, you will get their last insert id, not yours... one reason why Access should not be used in multi-user environments.
|
|
|
|
|
I get it working now(after losing few of my hair) by modified the query to "SELECT @@IDENTITY AS temp"
and then calling GetData on "temp" field which is work fine now.
but as you said since ACCESS doesn't even support multi query i guess this is still as dangerous as calling MAX(id).
but my application is Single user I guess it ok for now.
Thank for your answer.
|
|
|
|
|
Hi
I am not sure of the right terminology but here goes. (I am fairly new to this any help will be appreciated)
I am trying to take data and assign it a new string name each time a new row has been through the loop. This is in asp classic.
rs1.Open "Select * from email where deleted = 0;", connStr1, 3 , 4
startbody = rs1("body")
rs1.MoveNext
rs1.Close
Set rs1 = Nothing
for each record that comes out I need a new sting name
example
startbody1 = rs1("body") (first record returned)
startbody2 = rs1("body") (second record returned) etc.
modified on Sunday, September 7, 2008 4:53 PM
|
|
|
|
|
First, try Web Development forum.
Second, arrays could be useful for you
Mika
|
|
|
|
|
Thanks will repost it in the web dev area.
|
|
|
|
|
Hello friends I want to run a store procedure in a job schedule each time the server starts. How can I do this?
|
|
|
|
|
|
Thank u Mika. I hope that will work but as I am using Sql Server 2005, And the Sql Server Agent is disabled here by default so I could not do u told me to do. Can u plz tell me how can I activate Sql Server Agent of SQL Server 2005 ?
Thanks in Advance
Johnny
|
|
|
|
|
SQL Server 2005 Standard and Enterprise editions ship with an agent, but if you are using Express edition, there is no way you can use SQL Server's own agent (Express doesn't include that).
However, there are several alternatives, for example SQL Agent: A Job Scheduler Framework[^]
I don't know if that's capable of running a job when the agent starts (that was your original question), but you can take a look.
Mika
|
|
|
|
|
Hi,
I am using datetime variable in one table.I want to convert the date in the following format "yyyy-mm-dd hh:mm:ss". Now I am using conevrt(datetime,getdate(),120).But its returns "yyyy-mm-dd hh:mm:ss.mmm".
Regards,
Subbu
|
|
|
|
|
try this
select convert(varchar(10), getdate(), 23)+' '+convert(varchar(12),getdate(),14)<br />
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
A DateTime value is stored in no particular format, a format is only applied when the value is displayed.
|
|
|
|
|
Im returning an XML from a stored procedure using "FOR XML, XMLDATA". Is there any way to specify the name space to be returned? I want to return eg (xmlns="http://App.myapp") and not the default(xmlns="x-schema:#Schema1")
Thanks
|
|
|
|
|
|
Hello!
The uniqueidentifier produces an automated value and it is not acting likely PK in Access database. Therefore I decided to create a uniqueidentifier function which produces a unique value. I defined the first column as an uniqueidentifier column in my table and assigned char(30) instead of uniqueidentifier newID() where I assign produced value.
My question is: is it still mandatory to define this column as uniqueidentifier NEWID() column and then later on assign the produced value to this column as shown example below or can I simply define a column which will be used as an uniqueidentifier column with a definition of Char(30) and then I assign the produces value to this column ?
What would be the prons and cons?
Regards,
<br />
<br />
DECLARE @GUID uniqueidentifier<br />
SET @GUID=NEWID()<br />
INSERT Item VALUES(@GUID,ProducedIdentifier)<br />
<br />
<br />
What a curious mind needs to discover knowledge is noting else than a pin-hole.
|
|
|
|
|
I'm not sure I follow you.
To use a Guid, use the uniqueidentifier datatype.
To have it created automatically, set RowGuid to Yes.
To create the Guid separately, set RowGuid to No.
If you don't want to use Guid, don't use the uniqueidentifier datatype.
Don't use character data as a primary key (or at least very rarely).
What problem are you having?
|
|
|
|
|
Whenever I run a particular test, i don't get all of the data. But when I run the test in debug, I get all of the data. The test is independent of other tests. The only thing in common is that they all talk to the database (ms access database). Also, I get all of the data when I run all of the tests. Any idea on why this is happening and how I can resolve this problem?
Thank you,
Prateek
|
|
|
|
|
Can you describe more technically your problem?
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
After the data gets added to the database, I read and print out the data in nUnit's console from the database. There are times when I can't read all of the data from the table. e.g. if the table has 5 rows, it will sometimes show me only 2 rows. And then there are times, when it would print out incorrect values from the data e.g. if a column has an integer value of '2', it will print out '0'. My guess for this would be that it wasn't able to read the data from that particular column.
What other type of information would you like to know?
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
