Write Date-only part to database

Classic Servoy
1

Hi,

What is the best way to write a Date-only part into the database?

I tried to use format dd/MM/yyyy in the dataprovider format settings, but I see that in my database (SQL Server 2005) that the field is still written as 10/09/2009 09:42:00
But I want it to be written as 10/09/2009 00:00:00

I tried to set the timepart to all zeroes

	_date.setHours(0)
	_date.setMinutes(0)
	_date.setSeconds(0)
	_date.setMilliseconds(0)

but I see that when I use calculations on my date, that I see a difference of 1 hour. I guess that has something to do with summertime.

I even tried to use a dataprovider global method converter (see below)

	if (arguments[0] && arguments[1] && arguments[1] == 'DATETIME')
	{
		var _new_date = new Date(arguments[0].getFullYear(), arguments[0].getMonth(), arguments[0].getDate(), 0, 0, 0)
		return _new_date
	}
	else
		return arguments[0];

but that doesn’t solve the problem either.

So I would like to know, what is the best way to write a date-only field in the database?

The problem is dat when the time part is in the database, that 10/09/2009 09:42 and 10/09/2009 09:50 are not recognized when I want to select records from 10/09/2009 (CALENDER FIELD)

Thanks for your reply

Martin

2

does your database not have a date only column?
that would be the most easy way.

else try something like this:

utils.dateFormat("2009/09/10", "yyy/MM/dd")
3

No SQL Server uses (small)datetime

If I use in the dataprovider settings in Servoy the format dd/MM/yyyy, could it be an option that Servoy doesn’t write time-part to the database (so 10/09/2009 00:00:00) automatically?
Or does that give problems with the JDBC drivers?

4

dont think that will work.
because setting that format on a column makes sure that if you dont set a format on a field that one is used.
but a field format for dates have this unique feature that they dont loose the time format if you just have a date format string.
This is so that you can have 1 dataprovider that you display over 2 fields where 1 handles the date and 1 handles the time.

5

Hi,
This may be of some help, I recently had to do the exact same thing, it may not be the most elegant of solutions but it seems to work …

vRecord.invoice_date is the date to be modified

var vDateString = utils.dateFormat(vRecord.invoice_date,'yyyy/MM/dd 00:00:00'); //get date then set time to 00:00:00
var vFormDate = new Date(vDateString); //convert back to date object with time zero

Hope this helps ?