Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

ADO doesn't read datetime recordset in ODBC canonical form

Posted on 2009-04-24
11
Medium Priority
?
1,314 Views
Last Modified: 2012-05-11
The datetime is saved as yyyy-mm-dd hh:mm:sec (ODBC canonical form) format .
When I query the database in SQL Management Studio, it gives the same format as that of in database
When I use ADO member in my code to get the recordset, it gives the value in regional specific formate. i.e. since my machine is configured for English(U.S.), the output from ADO is mm/dd/yyyy hh:mm:sec

Is there anyway i can get the valu in ODBC canonical form?
_CommandPtr adboCmd
adboCmd->Execute(NULL, NULL, noRecords)
adboCmd->Parameters->Item[COLUMN_NAME]->Value

Open in new window

0
Comment
Question by:Kanchipuramdeena
  • 6
  • 5
11 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24229860
>>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)

>>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)
0
 
LVL 1

Author Comment

by:Kanchipuramdeena
ID: 24230925
>>>>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?

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24232386
>>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.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:Kanchipuramdeena
ID: 24258908
>> 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
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 100 total points
ID: 24262597
And that is exactly why I said "I don't know why you would want to do this "...  In other words, you should not change the O/S regional settings.

>>So i need a way that can give me the datetime format in UTC(odbc cannonical form) which is indepedent of regional time settings<<
So again you will have to use the CONVERT function with a style of 120 to output as a string.

I am not sure how else to explain that to you.  Other then to repeat that datetime values are NOT stored in any regional format and in order to display in a particular format you have to use CONVERT.
0
 
LVL 1

Author Comment

by:Kanchipuramdeena
ID: 24324894
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.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24325271
>>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.
0
 
LVL 1

Author Comment

by:Kanchipuramdeena
ID: 24333546
> 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;

Open in new window

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24335342
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.
0
 
LVL 1

Accepted Solution

by:
Kanchipuramdeena earned 0 total points
ID: 24351687
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24353929
I suggest you request this question be deleted and restate it in a new thread.  It does not look like anyone is interested.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
The viewer will be introduced to the technique of using vectors in C++. The video will cover how to define a vector, store values in the vector and retrieve data from the values stored in the vector.

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question