Data Type in ADODB.Recordset

[Andrzejek]

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

 

[strongm]

Hmm - you should be getting 135 (adDBTimeStamp) for an SQL DateTime field. And that's certainly what I get here...

 

[Andrzejek]

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:

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

 

[strongm]

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)

 

[strongm]

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

 

[remeng]

If you have Manage Data Model under the Data tab, you might be able to change the data source type there.

Here's a general article on how to make a change. It might be a good starting point.

How to create a date table in PowerPivot with one single value

 

[strongm]

Unfortunately, {Popwer Pivot will not recognise a string variant of DateTime2 as a date either. You have to do some more work ...