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!

  • Congratulations strongm 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

Programmer
Jan 10, 2006
8,489
5
38
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
 
Hmm - you should be getting 135 (adDBTimeStamp) for an SQL DateTime field. And that's certainly what I get here...

 
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
 
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)
 
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
 
Unfortunately, {Popwer Pivot will not recognise a string variant of DateTime2 as a date either. You have to do some more work ...
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top