Programming with ADO, recordsets

[byron.hopp]

I seem to spend way too much time messing with variable types when programming with Sql Server. Anybody have a secret on how to do this better. In sql with Date/Time fields are there functions to help with the saving of the data, extracting the time from the field.

Byron ...

[AHF]

Byron,

Try adordd it will do that for you with standard (x)Harbour syntax like any other rdd.

[Rick Lipkin]

Bryon

In xHarbour ( not sure about harbour ) date\time is a new ValType "T" which ( as you probably know ) looks something like this :

mm:dd:yyyy H:M:S ampm ( generally speaking )

To save a Date variable to SQl Date\Time you can simply do it like this :

Code: Select all  Expand view

dDate := Date()

oRs:Fields("YourDateField"):Value := dDate

or

oRs:Fields("YourDateField"):Value := dtoc(dDate)+" "+Time()

 

To Decode-Convert the Date\time the code below to the standard ValType "D" was inspired from one of our friends in this forum :

Code: Select all  Expand view

//--------------------------
Function TtoDate( tDate )

If empty( tDate)
   Return( ctod("00/00/00"))
Endif

If ValType( tDate ) = "D"
   Return(tDate )
Endif

Return( stod( substr( ttos( tDate ), 1, 8 ) ))

//----------------------------------
dDate := TtoDate(oRs:Fields("YourDateTime"):Value) // converted back to ValType "D"
 

Rick Lipkin

[reinaldocrespo]

Hey Rick;

If this was Facebook, I'd give a "like" to your response. Simple and to the point.

[Rick Lipkin]

Reinaldo

Thank you for the Kind words.. appreciate all your advice over the years!

Rick Lipkin

[byron.hopp]

Thank you Rick, and all.

My frustration level has already diminished.

[nageswaragunupudi]

Both Harbour and xHarbour have the data type 'T', i.e., DateTime value.

Optimized functions for conversion of Date type to DateTime type and vice-versa are:

FW_TTOD( tDateTime/dDate ) --> dDate ( valtype 'D' )
and
FW_DTOT( dDate ) --> tDateTime( valtype 'T' ) // time is 0 seconds.

To get the timepart of DateTime variable in seconds
FW_TIMEPART( tDateTime ) --> nSeconds
or
SecToTime( FW_TIMEPART( tDateTime ) ) --> cTimePart ( eg. '10:11:12' )

Add ( or Subtract ) Time to Date or DateTime variable --> returns DateTime value
FW_ADDTIME( dDate_or_tDateTime, cTime ) --> tDateTime
Eg: ? FW_ADDTIME( {^ 2010/10/20 }, "2d 10:12:32" ) --> '22-10-2010 10:12:32'
? FW_ADDTIME( {^ 2010/10/20 }, "-2d 10:12:32" ) --> '17-10-2010 13:47:28'

Though the dates are stored in MSSQL table as DateTime Values, we can assign either dDate value of type 'D' or tDateTime value of type 'T'.

oRs:Fields( "DTFIELD" ):Value := Date() // or DateTime()

tValue := oRs:Fields( "DTFIELD" ):Value // returns a tDateTime value of type 'T'
To extract only the truncated date
dValue := FW_TTOD( oRs:Fields( "DTFIELD" ):Value )

When you want to assign empty date it is desirable to assign AdoNull()

eg:
oRs:Fields( "DTFIELD" ):Value := AdoNull() // instead of CtoD( "" )

Note:
Recent versions of SQL Server provides a field type "Date" which stores only a pure date value, without time part. From my experiences, I advise not to use this type, but to depend on DateTime field or SmallDateTime field type
If required I can elaborate the reasons.