Regional date settings on Access PC different than on SQL server

[Luzbel]

I have an Access database with the backend in SQL Server 2005. I am using a couple of DateTimePicker ActiveX Controls on my forms. I have noticed that some users have their regional settings option for the date in format dd/MM/yy. My SQL database saves the information as MM/dd/yyyy. When running a form I noticed that I was getting incorrect data. I have been using Format(DateValue(Me.DTPickerFromDate.Value), "MM/dd/yyyy") on my VBA Dlookups, and DAO recordsets. I noticed that when I saved a field with the format function it still reverts and saves the date in the field with the month first. How can I fix this without having to change the Regional Settings options?

BTW I even tried this statement: Format$(DateValue(Me.DTPickerFechaMenu.Value), "mm\/dd\/yyyy\")

 

[RoyVidar]

Both SQL server and Jet stores date information in pretty much the same way. It's a numeric, where the integer part represents the number of days since sometimes in the past, and the decimal part, represents the number of seconds since midnight.

On a side note, that start date is different between SQL server and Jet by two days, I think, see for instance the difference between

SELECT CAST(0 AS DATETIME) -> 1/1/1900

And

SELECT Format(0, "mm\/dd\/yyyy") -> 12/30/1899

So what happens at your setup, is most likely a consequence of implicit conversion between datatypes, or a "misunderstanding" in the user interface. Allen Browne has a good article on such here

http://allenbrowne.com/ser-36.html

BTW - if the date picker returns an actual date, the perhaps more proper way of formatting it when passing to a SQL string for evaluation and execution on SQL server, would be:

Format$(Me.DTPickerFechaMenu.Value, "yyyy-mm-dd")

That is ISO 8601 date format, and is supposed to work on any platform.

(or "yyyy\-mm\-dd" for safety), or, when Jet is involved, the original format could be used

Format$(Me.DTPickerFechaMenu.Value, "mm\/dd\/yyyy")

Roy-Vidar

 

[Luzbel]

Thanks for the reply.
I had used the following format statement and worked.

Format(Me.DTPickerFechaMenu.Value, "yyyy\/mm\/dd\")
Aperently this way no matter in what order the date, month or year is and instead of using the regular date seperator "/" like a "." or "-".
So I ended up using "yyyy\/mm\/dd\") everywhere I lookup a date in the SQL Server database (tables and queries) and when inserting a field date value. If it does a calculation in VBA I did NOT use the Format function.

Thanks again!