mssql advice (datetime errors again)

Hi,
I've made an application (WD12) using MSSQL. But whenever it comes to datetime fields I'm struggling for hours. Ending up using HExecuteSqlQuery(...,hQueryWithoutCorrection,...) or even using stored procedures in mssql, to be able to get values in the proper format using CAST/CONVERT.

All I want is to use WD and the WD-syntax (H-functions). I expect WD to do all necessary conversion for me and make my live easy. Just define query with the query editor and assigning parameters in WD-format, like DateSys() + TimeSys().

What am I doing wrong? Can anyone give me some hints/tips or point to some pittfalls?

WD can handle date-fields, whereas mssql does not (until mssql2005 - I believe mssql 2008 can do). Should I define these fields in WD as datetime too?


Arie

Hi Arie,

i use datetime fields in every table at least once. And as my app is time-management, i use calculations on datetime intensively. In SQL they are all defined as datetime, in WD they are all defined as datetime, too, except at one place, where I have just a date type. But this doesn't make problems so far.

Mostly I am using tables filled by queries with modify-option and standard h-commands with no problems.

Maybe you can post a few codelines which are not working.

Erik

Erik, I'm trying to update records for an employee on a given date (or date range) - (time-management :-) )

This is the query-text (mostly in Dutch...)
UPDATE
Tbl_Werktijden
SET
Geaccordeerd_door = {ParamGeaccordeerdDoor},
Geaccordeerd_op = {ParamGeaccordeerdOp}
WHERE
Tbl_Werktijden.WerknemersID = {ParamWerknemerID}
AND Tbl_Werktijden.Datum >= {ParamDatumVan}
AND Tbl_Werktijden.Datum < {ParamDatumTm}

Geaccordeerd_door is a string-50 (wd) / varchar2(50) (sql)
Geaccordeerd_op is a date (wd) / datetime (sql)
WerknemersID is an int (wd) / int (sql)
Datum is a date (wd) / datetime (sql)

This is my code:
qry_Accorderen.ParamDatumVan = dtDatum
qry_Accorderen.ParamDatumTm = IntegerToDate(DateToInteger(dtDatum)+1)
qry_Accorderen.ParamWerknemerID = nWerknemer
qry_Accorderen.ParamGeaccordeerd = nGeaccordeerd
qry_Accorderen.ParamGeaccordeerdDoor = glb_nUserId
qry_Accorderen.ParamGeaccordeerdOp = DateSys()+TimeSys()
IF NOT HExecuteQuery(qry_Accorderen) THEN
ToClipboard(ErrorInfo(errFullDetails))
Error("Error updating records!",ErrorInfo(errFullDetails))
END

(dtDatum comes from a date-edit-field)

And this is the error:
"Conversion failed when converting datetime from character string." (translated from dutch...)

When I leave out the line with DateSys()+TimeSys() it's working, so that's is the line causing the error.

Arie

Hi Arie,

You cannot assume that MSsql uses the same DateTime format as Windev, so check the documentation for MSsql.

For MySQL I use the follwing function:

FUNCTION MySQLdateTime(sDate,sTime)
//parameters sDate and sTime are date and time type.
IF DateValid(sDate) AND DateValid(sTime) THEN
	RESULT DateToString(sDate,"YYYY-MM-DD")+" "+TimeToString(sTime,"HH:MMS")
END
RESULT ""

Best regards,
Piet

Hi Arie,

maybe the problem is when assigning the date edit field to your dtDatum variable. I had have such issues like this:

date_edit = "20090201"

dtDatum = date_edit (dtDatum is then "20090201",too)
means the timepart is missing.

I solved with

dtdatum = date_edit + "000000000" (hhmmssccc)

Just out of my head, give it a try.

Erik