Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Data Type in ADODB.Recordset

Status
Not open for further replies.
Andrzejek

Andrzejek

Programmer
Jan 10, 2006
8,501
US
I grab data from SQL Server in ADODB.Recordset where DateTime data comes as:[tt]
2024-02-07 16:00:00.0000000
2024-04-12 16:00:00.0000000
2023-11-21 16:00:00.0000000
2023-12-18 16:00:00.0000000
2024-01-04 16:00:00.0000000[/tt]

But the Users do not care about time portion. They just want to see the Date.
I know I can do [tt]Cast(MyField As Date) As Whatever[/tt], but I am trying to write something more generic, so I would like to detect DateTime field in my recordset and act upon it.

I did try: [tt]rst.Fields(X).Type[/tt], and I do get Type 202 for DateTime fields, but also I get 202 for nvarchar() fields :-(

My work-around it is:
[tt]If Len(rst.Fields(X).Value) = 27 And IsDate(Left(rst.Fields(X).Value, 10)) Then[/tt]
but I don't like it

Is there a way to detect DateTime field in ADODB recordset?

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson
 
Sort by date Sort by votes
Hmm - you should be getting 135 (adDBTimeStamp) for an SQL DateTime field. And that's certainly what I get here...

 
Upvote 0 Downvote
If I run my code on this connection to DB:
[pre]
"Provider=SQLOLEDB.1;" & _
"Integrated Security=SSPI;Persist Security Info=False;" & _
"Initial Catalog=ABCD;Data Source=XYZ\PDQ1"[/pre]

and I look at one table with this code:
[tt]Debug.Print rst.Fields(intC).Type & " -- " & rst.Fields(intC).Name[/tt]
I get:

DB_er3yd0.png

I get 203 for nvarchar(max) and 202 for varchar(250) and DateTime2(7) field

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson
 
Upvote 0 Downvote
Ah, your OP mentioned DateTime, not DateTime2. Whole different kettle of fish ...

Unfortunately, ADO doesn't support DateTime2 (even if the underlying OLEDB provider does) - it converts it, as you have found, into a wide string (adVarWChar)
 
Upvote 0 Downvote
One method might be to have a calculated field in your recordset using something like TRY_PARSE or TRY_CONVERT, e.g

Select *, try_convert(datetime2,dt2) from Example

Result is NULL if the conversion fails (i.e not a datetrime2), otherwise it returns the datetime2 (which ADO will convert to a wide string). Of course this is in many ways just a variant of your CAST solution, so may not meet the 'generic' ideal you are looking for
 
Upvote 0 Downvote
Unfortunately, {Popwer Pivot will not recognise a string variant of DateTime2 as a date either. You have to do some more work ...
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

IcarusHallsorts
  • Locked
  • Question
I have a column of data in Column A
Replies
3
Views
164
Andrzejek
Andrzejek
sal21
  • Question
scrping in listbox in a web page
Replies
0
Views
102
sal21
sal21
Andrzejek
  • Locked
  • Question
Retain signature in Outlook
Replies
17
Views
445
strongm
strongm
Kymenglish
  • Locked
  • Question
Automated data entry code in MS Excel VB - Edge code how to hit enter to remove popups
Replies
3
Views
168
strongm
strongm
remeng
  • Locked
  • Helpful tip
Calculate Week Number - Week in Month - Holidays
Replies
0
Views
185
remeng
remeng

Part and Inventory Search

Sponsor

Back
Top