Page 1 of 1
Programming with ADO, recordsets
Posted:
Thu Jun 18, 2015 11:29 pm
by 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 ...
Re: Programming with ADO, recordsets
Posted:
Fri Jun 19, 2015 9:33 am
by AHF
Byron,
Try adordd it will do that for you with standard (x)Harbour syntax like any other rdd.
Re: Programming with ADO, recordsets
Posted:
Fri Jun 19, 2015 1:06 pm
by 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
Re: Programming with ADO, recordsets
Posted:
Fri Jun 19, 2015 1:33 pm
by reinaldocrespo
Hey Rick;
If this was Facebook, I'd give a "like" to your response. Simple and to the point.
Re: Programming with ADO, recordsets
Posted:
Fri Jun 19, 2015 1:35 pm
by Rick Lipkin
Reinaldo
Thank you for the Kind words.. appreciate all your advice over the years!
Rick Lipkin
Re: Programming with ADO, recordsets
Posted:
Fri Jun 19, 2015 3:07 pm
by byron.hopp
Thank you Rick, and all.
My frustration level has already diminished.
Re: Programming with ADO, recordsets
Posted:
Sun Jun 21, 2015 2:32 am
by 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.