September 13, 2007 at 8:08 am
September 13, 2007 at 8:53 am
The servers settings can be different from that of the user account. Check your user account that you login with.
September 14, 2007 at 12:53 am
I am not much of a Windows engineer (I can hardly distinguish between Windows domains and workgroups). The server I am referring to is installed on my development PC. My PC is on the office network, just part of a workgroup, but not part of the domain.
However, this should have no effect because I do all my programming (VB6) and SQL work on my own PC and do not use any network resource.
My PC has only the default "USER" account to log in to Windows, and its password is set to blank. My regional settings are UK. I don't know whether the SQL Server collation (mine is US English) will affect dates.
I have always assumed that dates are stored and processed unambiguously by Windows. However, if I hold "5th September 2007" to a variable in VB6, it will read 05/09/2007 on first formatting (FORMAT(DATE, "DD/MM/YYYY")), and flip to 09/05/2007 on second formatting.
If I write the date variable back to the DB I will get 05/09/2007 with the first one and 09/05/2007 with the second one. If the date was originally 5th September 2007 and no processing has been done on the variable holding it, shouldn't it be saved back as 5th September 2007 irrespective of how many times it is formatted?
Similarly, if dates are to be ported without ambiguity, shouldn't the regional settings on a workstation querying the database be immaterial to how the dates are read and written back and forth? The regional settings should only serve teh function of formatting the dates/times according to the user's preferences.
I am surprised at the havoc this issue is causing me and am wondering how all the other programmers have solved it. For the life of me, I cannot solve it without resorting to changing user's settings, which is very unethical.
All help will be appreciated.
September 14, 2007 at 6:19 am
goodguy,
the column you are speaking about is DATETIME datatype, right?
Datetime values are not stored in any format, they are stored absolutely unambiguously, as amount of time that has passed since certain point 0. What can be different is how they are displayed on the client. When inserting the date string ('05/09/2007' or whatever format you use) into a database column of datatype DATETIME, conversion from string to date occurs.
That means, if you are passing a string from some application to be stored in database as date, you need to be sure in which format it was created and say so, by using the appropriate CONVERT style. The value you are passing is a string, not a date - and various settings can influence how this is interpreted if you rely on automatic conversion. CONVERT with specified style says unambiguously where in the string is day and where is month.
If you are able to format the string when passed to SQL as 'YYYYMMDD', then you can be sure that it will always be converted correctly to datetime, no matter what settings are active (using CONVERT is not necessary in this case).
Read "Using Date and Time Data" and "CAST and CONVERT" in BOL to get an idea how to work with dates in SQL.
I know very little about VB and how the values are passed from VB to database, so I may have misunderstood something in your question. In that case, please wait for some other person who knows better :-).
September 14, 2007 at 7:02 am
Thanks, Vladan, for your explanation. And yes, I am talking of the datetime datatype.
I suspected as much. Unlike most of the members here, I am not a SQL Server professional or admin. I am a VB6 developer with responsibility for designing (and interfacing with) SQL Server 2000 databases.
VB6 has a function called Format(expression, style) which formats many datatypes including datetime. In VB6, we also have a datatype called 'Date' which is supposed to hold date and time variables (as unambiguously as in SQL, I should suppose). But wonder of wonders, the datatype just holds the formatted date in string format. So a little mishap can cause havoc.
Any VB vets out there who'd care to contribute?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply