Tidy up some SQL Select...

[ClimoC]

Howdy How

Just getting into more SQL heavy orientated stuff to work things a bit smarter and faster when I then start vba/excel'ing with my recordset

The datatype on the server (PL/SQL Oracle), like a lot of them, is in text format

so after reading up on the CAST function, I can successfully bring in the recordset, with another column appended, which is the date column, but with a datatype 135 (adDBTimeStamp)

Just wondering, I think there's a CONVERT function, which doesn't look exactly what I'm after... so I sort of have two small questions...

1) Can what is 'CAST' be returned as/in place of the actual column? That is, instead of having a redundant text formatted date field with an appended date field, I can just swap the former for the latter?

and

2) Does anyone know the datatype to cast it as so that it returns as a datatype 7? (adDate, rather than a 135 adDBTimeStamp)

var1 = "SELECT *, CAST(schedule_date_raw AS DATE)" & vbCrLf & "FROM schedule_export_chris" & vbCrLf & " WHERE schedule_date_raw = '20121201'"


    With cnImportConn
                    .CursorLocation = adUseClient
                    .Open strConn
                    .CommandTimeout = 0
                    Set RecSet = .Execute(var1, adOpenForwardOnly, adLockReadOnly)
    End With

Thanks
C

 

[Kyle123]

does this answer your question? Oracle Dates and Times

I don't really understand question one, but you can return anything you like from the table, using the * operator will return everything from the table as you've noticed. Can't you do something like:

SELECT field1,
field2,
Cast(schedule_date_raw AS DATE),
field7
FROM schedule_export_chris
WHERE schedule_date_raw = '20121201'

 

[xenou]

Just out of curiousity, why do you think the datatype is adDBTimestamp? I'd expect it would be a date (as here, also: ADO Data Types). Well, technically I think that would be either - but Access has only one Date type and they should work either way too.

 

[ClimoC]

Just out of curiousity, why do you think the datatype is adDBTimestamp? I'd expect it would be a date (as here, also: ADO Data Types). Well, technically I think that would be either - but Access has only one Date type and they should work either way too.

xenou - it's Oracle source. Oracle doesn't have adDate equivalent datatype. And if you query the fields's ".datatype", it returns what the values have returned as from the source. In this case, from Oracle, as adDBTimestamp. Further reading showed there is no equivalent in Oracle to adDate. Just adTimestamp.

And yeah, Kyle you're probably right. Might just have to select all 50 columns, and do the CAST on the 2/3 date columns when I come to them. I just want the data to return in the same format, and as the same type, to minimise any potential tiny misgivings when I start comparing recordsets (specifically, I'm drawing from Oracle, and comparing to access.)

 

[xenou]

I guess I don't see why it matters if the datatype in the ADO recordset is adDBTimeStamp or adDate. Surely these are both date data types and would work correctly either way (my quess is that timestamp has greater precision, but I don't really know).

 

[ClimoC]

I guess I don't see why it matters if the datatype in the ADO recordset is adDBTimeStamp or adDate. Surely these are both date data types and would work correctly either way (my quess is that timestamp has greater precision, but I don't really know).

yeah, I don't think it'd matter, but I just want to avoid the situation where it maybe tries to compare "01/01/2013" with "01/01/2013 00:00:00" because if I either get something slightly wrong, or am forced to use it in a different way (say, if I needed to ever convert it to a string to pass it somewhere) - then I want them to be identical. Might not even have to be a string. Might pass it to an array and then I don't know WHAT would happen!

 

[xenou]

I see. I think you're right. It may be that adDBTimeStamp is a string. Found some details here finally: DataTypeEnum

For interoperability I personally like to use ISO date/times, which are strings in yyyymmdd format (or yyyy-mm-dd) format. If there is a time elements then: yyyymmddThhnnss (more precision I've not had to deal with yet).

These strings are easily parsed and in fact also work with greater than/less than comparisons (I picked this up from SQLite, which stores dates as strings (as might Oracle?).