Date Time shows different
-
I have a field set with a type of datetime size 8. When I run an SQL statement like this
SELECT TOP 1 MDC1_DATA_TEXT, MDC1_REV_DATE FROM MDC1 WHERE (MDC1_I_O = 'O')thru the qurey analyzer I get this: 2005-02-25 16:12:07.373 When I run the statement thru enterprise manager by right clicking on the table and choosing open table return all rows then clicking on the SQL button in the toolbar and running my statement, I get this: 2/25/2005 4:12:07 PM This is the same thing that comes across when I run my SQL statement in my program. The problem is that this "2/25/2005 4:12:07 PM" does not contain milliseconds which is part of the primary key and needed to delete the record after reading it in. Does anyon know how to have SQL return back the other date time stamp? Here is my code that I'm using in my program:try { pCommand.CreateInstance(__uuidof(Command)); pCommand->ActiveConnection = m_pConnection; //pCommand->CommandText = _bstr_t("SELECT TOP 1 MDC1_DATA_TEXT, MDC1_REV_DATE FROM MDC1 WHERE (MDC1_I_O = 'O')"); pCommand->CommandText = _bstr_t("SELECT TOP 1 * FROM MDC1 WHERE (MDC1_I_O = 'O')"); pRecordset.CreateInstance(__uuidof(Recordset)); pRecordset->CursorLocation = adUseClient; pRecordset->Open((IDispatch*) pCommand, vtMissing, adOpenStatic, adLockBatchOptimistic, adCmdUnknown); _variant_t MDC1_DATA_TEXT; _variant_t MDC1_REV_DATE; while (!pRecordset->adoEOF) { CString mySQLData = (char*)(_bstr_t)pRecordset->Fields->GetItem("MDC1_DATA_TEXT")->Value; //Get the reverse date time stamp so that we can use it for later. //MDC1_REV_DATE = pRecordset->GetCollect("MDC1_REV_DATE"); CString revDate = (char*) (_bstr_t)pRecordset->Fields->GetItem("MDC1_REV_DATE")->Value;Should I set my pointer for the rev_date to something else? Thanks Tom Wright tawright915@yahoo.com -
I have a field set with a type of datetime size 8. When I run an SQL statement like this
SELECT TOP 1 MDC1_DATA_TEXT, MDC1_REV_DATE FROM MDC1 WHERE (MDC1_I_O = 'O')thru the qurey analyzer I get this: 2005-02-25 16:12:07.373 When I run the statement thru enterprise manager by right clicking on the table and choosing open table return all rows then clicking on the SQL button in the toolbar and running my statement, I get this: 2/25/2005 4:12:07 PM This is the same thing that comes across when I run my SQL statement in my program. The problem is that this "2/25/2005 4:12:07 PM" does not contain milliseconds which is part of the primary key and needed to delete the record after reading it in. Does anyon know how to have SQL return back the other date time stamp? Here is my code that I'm using in my program:try { pCommand.CreateInstance(__uuidof(Command)); pCommand->ActiveConnection = m_pConnection; //pCommand->CommandText = _bstr_t("SELECT TOP 1 MDC1_DATA_TEXT, MDC1_REV_DATE FROM MDC1 WHERE (MDC1_I_O = 'O')"); pCommand->CommandText = _bstr_t("SELECT TOP 1 * FROM MDC1 WHERE (MDC1_I_O = 'O')"); pRecordset.CreateInstance(__uuidof(Recordset)); pRecordset->CursorLocation = adUseClient; pRecordset->Open((IDispatch*) pCommand, vtMissing, adOpenStatic, adLockBatchOptimistic, adCmdUnknown); _variant_t MDC1_DATA_TEXT; _variant_t MDC1_REV_DATE; while (!pRecordset->adoEOF) { CString mySQLData = (char*)(_bstr_t)pRecordset->Fields->GetItem("MDC1_DATA_TEXT")->Value; //Get the reverse date time stamp so that we can use it for later. //MDC1_REV_DATE = pRecordset->GetCollect("MDC1_REV_DATE"); CString revDate = (char*) (_bstr_t)pRecordset->Fields->GetItem("MDC1_REV_DATE")->Value;Should I set my pointer for the rev_date to something else? Thanks Tom Wright tawright915@yahoo.comI don't think your problem is SQL, I think it is with how .NET handles DateTime. You might try reading up on DataTime and see how it handles milliseconds. I did a quick search on www.support.microsoft.com and there seem to be a problem with what you want to do.
-
I have a field set with a type of datetime size 8. When I run an SQL statement like this
SELECT TOP 1 MDC1_DATA_TEXT, MDC1_REV_DATE FROM MDC1 WHERE (MDC1_I_O = 'O')thru the qurey analyzer I get this: 2005-02-25 16:12:07.373 When I run the statement thru enterprise manager by right clicking on the table and choosing open table return all rows then clicking on the SQL button in the toolbar and running my statement, I get this: 2/25/2005 4:12:07 PM This is the same thing that comes across when I run my SQL statement in my program. The problem is that this "2/25/2005 4:12:07 PM" does not contain milliseconds which is part of the primary key and needed to delete the record after reading it in. Does anyon know how to have SQL return back the other date time stamp? Here is my code that I'm using in my program:try { pCommand.CreateInstance(__uuidof(Command)); pCommand->ActiveConnection = m_pConnection; //pCommand->CommandText = _bstr_t("SELECT TOP 1 MDC1_DATA_TEXT, MDC1_REV_DATE FROM MDC1 WHERE (MDC1_I_O = 'O')"); pCommand->CommandText = _bstr_t("SELECT TOP 1 * FROM MDC1 WHERE (MDC1_I_O = 'O')"); pRecordset.CreateInstance(__uuidof(Recordset)); pRecordset->CursorLocation = adUseClient; pRecordset->Open((IDispatch*) pCommand, vtMissing, adOpenStatic, adLockBatchOptimistic, adCmdUnknown); _variant_t MDC1_DATA_TEXT; _variant_t MDC1_REV_DATE; while (!pRecordset->adoEOF) { CString mySQLData = (char*)(_bstr_t)pRecordset->Fields->GetItem("MDC1_DATA_TEXT")->Value; //Get the reverse date time stamp so that we can use it for later. //MDC1_REV_DATE = pRecordset->GetCollect("MDC1_REV_DATE"); CString revDate = (char*) (_bstr_t)pRecordset->Fields->GetItem("MDC1_REV_DATE")->Value;Should I set my pointer for the rev_date to something else? Thanks Tom Wright tawright915@yahoo.comYou are casting it to a string not converting it with a format specifier, so it is going to use default formatting which rounds to seconds. Retain the value as a variant, so that the presison is not lost, use it in a parameterised query when you need to delete. Beware of string conversions from date time, they are locale dependant, and unless you specify the formating the default precison of the conversion is likely seconds. SQL servers Date data type is accurate to 3 ms, but will round up: the next values after .373 will be .377 then .380. Anger is the most impotent of passions. It effects nothing it goes about, and hurts the one who is possessed by it more than the one against whom it is directed. Carl Sandburg