ADO doesn't read datetime recordset in ODBC canonical form

>>>>The datetime is saved as yyyy-mm-dd hh:mm:sec (ODBC canonical form) format .<<
>>Datetime values are not stored in any regional format whatsoever (they are actually stored as two >>integers)

Thanks for your comment, I was unaware of this...

>>>>Is there anyway i can get the valu in ODBC canonical form?<<
>>You willl have to convert (using the T-SQL CONVERT() function) to a character data as in:
>>CONVERT(varchar(20), YourDate, 120)

Actually my issue is that if i run the query "select * from table_name" the o/p is in the format  yyyy-mm-dd hh:mm:sec but when i run the same query from cpp application using ADO the following line of code

adboCmd->Parameters->Item[COLUMN_NAME]->Value

gives me the value in the format mm/dd/yyyy hh:mm:sec

Any idea why this is the behavior ?

Is there anything i could modify in the ADO API to get the ODBC cannonical format of timestamp?

>>Any idea why this is the behavior ?<<
ADO is interpreting the date using your regional settings in much the same way that SSMS does the same.  The only way that I know how you can do it is as I mentioned before and that is using CONVERT to output character data.

I don't know why you would want to do this, but try changing your O/S regional settings and ADO may use that format.

>> I don't know why you would want to do this, but try changing your O/S regional settings and ADO may use that format.

In a browser based ui, the client could access the data anywhere from across the world. If we change the OS regional settings in the database server that could solve the issue for those who are accessing the data through browser from the same region.

Example: If the database server is set to U.S.[English], then client tries to access the value in U.K region the date format that client recieves would be U.S.[English]... Instead i would like to make the format independent i.e. UTC format so that client browser can change the required date format depending on the region from where the data is accessed

So i need a way that can give me the datetime format in UTC(odbc cannonical form) which is indepedent of regional time settings

Okay... date time in database are not saved in any regional format. I understood that. I believe you are referring the CONVERT in TSQL. Yes, CONVERT function converts the datetime into odbc cannonical or any specified format.

CONVERT function gives me the odbc cannonical form. But my actual issue when I exceute this CONVERT function with 120 as output string from a stored procedure through ADO util it gives me the result in U.S. specific form.

I have tried changing the regional settings in Control Panel, nothing helps.

>>I believe you are referring the CONVERT in TSQL. Yes, CONVERT function converts the datetime into odbc cannonical or any specified format.<<
That is correct.

>>But my actual issue when I exceute this CONVERT function with 120 as output string from a stored procedure through ADO util it gives me the result in U.S. specific form.<<
Huh? I am confused.  You first say "CONVERT function converts the datetime into odbc cannonical or any specified format" and now "it gives me the result in U.S. specific form".  If that is the case, there must be something wrong in your C++ code.  Perhaps you are trying to interpret the resulting character representation of the date as a date.

> Huh? I am confused.  You first say "CONVERT function converts the datetime into odbc cannonical or any specified format" and now "it gives me the result in U.S. specific form".  

Sorry for the confusion. I wanna make one thing clear here... executing the datetime query in SQL Management Studio - Query Window UI provided by SQL Server itself and executing the same query using ADO util function. In Query Windows UI everything is ok, the output is in odbc cannonical form. When I execute the same query without any changes in the ADO util (irrespective of with/without CONVERT function) the output date is in U.S. Specific form. I have asked this in my first question column itself though i may not be very specific to the actual issue.


> If that is the case, there must be something wrong in your C++ code.  Perhaps you are trying to interpret the resulting character representation of the date as a date.

No, I am getting the value as variant. Attached is the code snippet.

_CommandPtr adboCmd;
_variant_t  vntValue;
 
adboCmd->Execute(NULL, NULL, noRecords);
vntValue = adboCmd->Parameters->Item[COLUMN_NAME]->Value;
                                              

1:
2:
3:
4:
5:

I am sorry I give up.  I don't know how else to explain it.  Hopefully someone else will step up to the plate.

Good luck.