lucerias

Question one
May i know is there any time picker in VB.NET 2005 which allows user to enter a time The DateTimePicker can only retrieve current time and i think of using few combo boxes to form the format like hour,sec and AM or PM. Apart from that, is there any other way of doing it If i use the combo boxes method, how to parse the concatenation string as time and store in database
 
Question two
I allowed user to select a date from DateTimePicker and format the date as the below and store it in database.
 
FormatDateTime(join_date.Text, DateFormat.ShortDate)
 
The datatype of that column i set it to string in order to prevent any format error. One problem is that the time format (DD/MM/YY) appears in DateTimePicker in my office is different from home. And i realized that this format is as same as the time format of the taskbar. When i have done the code and bring it back to home, it cannot execute unless i manually change the time format in database from (MM/DD/YY) to (DD/MM/YY). May i know is there any way of solving it by specifying the date format in DateTimePicker so then it won't cause the conflict according to the system taskbar time format. Thank you.


Re: Visual Basic Language Date and Time Format Problem

Dick Donny

Hi

Perhaps you could look at doing something along the following lines.

DateTimePicker.Format = DateTimePickerFormat.Custom

DateTimePicker.CustomFormat = Threading.Thread.CurrentThread.CurrentCulture.DateTimeFormat.FullDateTimePattern

This code set the custom format of the control to display the date and time in the user's preferred pattern. The user can adjust the time from within the control as well as the date.

When you come to store the date time in the database, make sure you store it as a UTC time (ToUniversalTime). This represents a none localised point in time that can then be correctly localised to the users windows time zone (ToString) when they view it again

Hope this helps

Richard





Re: Visual Basic Language Date and Time Format Problem

John Lieurance

Lucerias,

I would suggest you work around the date time format by formatting it before storing it in the database. Different regions and user preferences may result in an unexpected date time format. To account for this you can alter the format when you convert it to a string.

'formate the date and display it in the text box
TextBox1.Text = DateTimePicker1.Value.ToString(
"dd/MM/yy")

Johnny





Re: Visual Basic Language Date and Time Format Problem

lucerias

There is one datetimepicker in my form to choose the date and store it in database. This goes to time as well. I want the user to specify them in separate field. By the way, i want to know how datetimepicker can allow user to select time as the format of HH:MM:AM / PM.
I have set the properties of datetimepicker to short in order to display only short date. However, the format of the date looks as same as my taskbar time format even i make use ToUniversalTime as the following.
Dim Plan_Request_Date As Date
Plan_Request_Date = Date.Now
Plan_Request_Date.ToUniversalTime.ToString()
I suppose the time conflict will still happen because my office time format will still be the usual one. What i want is to specify a format like MM/DD/YY and store it into database.




Re: Visual Basic Language Date and Time Format Problem

lucerias

I have successfully standardize the format of date and store into the database and now another problem is error occur when i retrieve the date and display it in DataTimePicker as the following code.
 
        Dim sDate As String = Format(Today, "dd/MM/yy")
        DateTimePicker1.Text = Convert.ToDateTime(sDate)
 
The reason is because of the DateTimePicker format is set according to user system. I tried to change the format of date from dd/MM/YY to MM/dd/yy and this solved the problem. May i know how to display the date from the database in DateTimePicker regardless of the user system time format Thank you.




Re: Visual Basic Language Date and Time Format Problem

John Lieurance

lucerias wrote:
Dim sDate As String = Format(Today, "dd/MM/yy")
DateTimePicker1.Text = Convert.ToDateTime(sDate)

You're real close. Just set the value of the date time picker and not the text.

DateTimePicker1.Value = Convert.ToDateTime(sDate)

Johnny





Re: Visual Basic Language Date and Time Format Problem

Dick Donny

For what it is worth .....

I believe you have misunderstood what the DateTime type represents, and I would recommend that you take a little time out to read the help files and fully understand what you are working with.

In short, you are having problems purely because you are working with strings and fighting user preferences. Surely that doesn't sound like a good approach

Here is a little bit of code that shows a good way to work with dates ... hopefully it will help send you down the right path

'' Appropriately setting up the date time picker control to accept date and time in the format chosen by the user

DateTimePicker1.Format = DateTimePickerFormat.Custom

DateTimePicker1.CustomFormat = Threading.Thread.CurrentThread.CurrentCulture.DateTimeFormat.FullDateTimePattern

'' Extract the time from the picker. This is a localised date and time

Dim dt As DateTime = DateTimePicker1.Value

'' Persisting the date. Always store dates in UTC. UTC Dates are a point in time and easily localised.

Dim dTbl As New DataTable("MyTable")

dTbl.Columns.Add("DateUTC", GetType(DateTime))

Dim dr As DataRow = dTbl.NewRow : dr.Item("DateUTC") = dt.ToUniversalTime

'' Now write away to the database .......

'' ...... Getting the data back out of the database .. slap it into a data table and read it out as follows

dt = DateTime.SpecifyKind(CType(dr.Item("DateUTC"), DateTime), DateTimeKind.Utc).ToLocalTime

Note that there is no use of the string class, no messing about with user preferences and your application is pretty much globalized out of the box. As an added bonus, all the times in your database represent exact points in time and can be compared across source time zones accurately.

Richard





Re: Visual Basic Language Date and Time Format Problem

John Lieurance

Dick Donny wrote:

'' Persisting the date. Always store dates in UTC. UTC Dates are a point in time and easily localised.

Dim dTbl As New DataTable("MyTable")

dTbl.Columns.Add("DateUTC", GetType(DateTime))

Dim dr As DataRow = dTbl.NewRow : dr.Item("DateUTC") = dt.ToUniversalTime

'' Now write away to the database .......

'' ...... Getting the data back out of the database .. slap it into a data table and read it out as follows

dt = DateTime.SpecifyKind(CType(dr.Item("DateUTC"), DateTime), DateTimeKind.Utc).ToLocalTime

Note that there is no use of the string class, no messing about with user preferences and your application is pretty much globalized out of the box. As an added bonus, all the times in your database represent exact points in time and can be compared across source time zones accurately.

Richard

Richard is correct as long as your database supports a .NET datetime datatype. Since the datetime object format changed in .NET 2.0, I don't think SQL 2000 supports it. Even before the change I seem to remember the two data types being different, meaning a conversion is required between the two. I believe the value changed from a 64bit with 2 of the bits being reserved to a full 64 bit value.

SQL 2000 server accepts dates between 1 Jan 1753 and 31 Dec 9999; while a .Net datatype accepts dates between 1 Jan 0001 and 31 Dec 9999. Here's a quick reference, but I'm sure there's more information out there on the subject.
ms-help://MS.VSCC.v80/MS.MSDN.v80/MS.NETDEVFX.v20.en/cpref2/html/T_System_DateTime.htm
http://www.sqlservercentral.com/columnists/rmarda/manipulatingandusingdatetimedata.asp

This kind of talk is way beyond my knowledge and since I work with many database's that use thier own datetime format, I find that converting the value to a string before storing it in the database is the best approach for me. That's not to say that I don't store a UTC, its just a UTC that's been converted to a string :)

Richard can you give an example of UTC with MS SQL 2000 or 2005 It may be simpiler then I would expect

Johnny





Re: Visual Basic Language Date and Time Format Problem

Dick Donny

Hi John

With regard to converting dates etc to database specific formats and back ..... that is the job of the provider and isn't something that a developer should need to concern himself with. SQL Server 2000/5 will work just fine with .NET 2.0

I'm not sure what sort of example you are looking for other than what is in the code in my previous post. If you can be a little more specific as to what you need to know, I'd be happy to help.

Richard





Re: Visual Basic Language Date and Time Format Problem

MS Johan Stenberg

If you need to store the date time as a string in the database, http://msdn2.microsoft.com/en-us/library/az4se3k1.aspx lists a bunch of format strings that you can use. If you want the string to be independend of your current settings, look for "The pattern for this specifier is a defined standard. Therefore, it is always the same, regardless of the culture used or the format provider supplied" in the description of the format.

Best regards,
Johan Stenberg






Re: Visual Basic Language Date and Time Format Problem

John Lieurance

Dick Donny wrote:

Hi John

With regard to converting dates etc to database specific formats and back ..... that is the job of the provider and isn't something that a developer should need to concern himself with. SQL Server 2000/5 will work just fine with .NET 2.0

I'm not sure what sort of example you are looking for other than what is in the code in my previous post. If you can be a little more specific as to what you need to know, I'd be happy to help.

Richard

Well its still nice to learn something new every day :) Thank you for your feedback Richard. Some investigating indicates you're correct; the provider does a pretty good job at converting the native datetime object. I still have some difficulty with IBM's DB2, but hey that's always the case when I work with the OS/390.

Just in case anyone is looking for an example, I put together 2 subroutines. Create a form and add 2 buttons two it. Cut and paste the code below and point the connection strings to a database that has a table named "TableDate" with a datetime field named "created" and a varchar(50) field named "Info".

Johnny

Public Class Form1

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

'setup a native sql connection to MS SQL 2000

Dim sqlConn As Data.SqlClient.SqlConnection

Dim sqlCmd As New Data.SqlClient.SqlCommand

Dim sqlReader As Data.SqlClient.SqlDataReader

Dim connectionString As String = _

"Data Source=fhnt103;Initial Catalog=forumPlayground;User ID=johnny;pwd=fhnt103"

Dim createdDate As DateTime

Dim lastRecord As String = ""

Try

'set our date to an invalid sql date

createdDate = New DateTime(1, 1, 31)

sqlConn = New Data.SqlClient.SqlConnection(connectionString)

sqlConn.Open()

sqlCmd.Connection = sqlConn

sqlCmd.CommandText = "Insert into TableDate (created, info) values (@created, @info)"

sqlCmd.Parameters.Add("@created", SqlDbType.DateTime)

sqlCmd.Parameters.Add("@info", SqlDbType.VarChar, 50)

sqlCmd.Parameters.Item("@created").Value = createdDate.ToUniversalTime

sqlCmd.Parameters.Item("@info").Value = "A test record"

'insert the data

If sqlCmd.ExecuteScalar() Then

Err.Raise(vbObjectError + 1024, Me, "Insert failed to update any records.")

End If

sqlCmd.CommandText = "select created, info from TableDate"

sqlReader = sqlCmd.ExecuteReader

'read all records

While sqlReader.Read

'store the last records value

lastRecord = sqlReader.Item("created").ToString + " -- " + sqlReader.Item("info").ToString

End While

'display the value

MsgBox(lastRecord)

'close the reader

sqlReader.Close()

Catch ex As Exception

MsgBox(ex.Message)

End Try

End Sub

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click

'setup an ODBC sql connection to MS SQL 2000

Dim sqlConn As Data.Odbc.OdbcConnection

Dim sqlCmd As New Data.Odbc.OdbcCommand

Dim sqlReader As Data.Odbc.OdbcDataReader

Dim connectionString As String = _

"dsn=odbcDB;UID=johnny;pwd=fhnt103"

Dim createdDate As DateTime

Dim lastRecord As String = ""

Try

'set our date to an invalid sql date

createdDate = New DateTime(1, 1, 31)

sqlConn = New Data.Odbc.OdbcConnection(connectionString)

sqlConn.Open()

sqlCmd.Connection = sqlConn

'NOTE! .NET 2.0 no longer supports this syntax; named parameter fields.

'sqlCmd.CommandText = "Insert into TableDate (created, info) values (@created, @info)"

sqlCmd.CommandText = "Insert into TableDate (created, info) values ( , )"

sqlCmd.Parameters.Add(" ", Odbc.OdbcType.DateTime)

sqlCmd.Parameters.Add(" ", Odbc.OdbcType.VarChar, 50)

sqlCmd.Parameters.Item(0).Value = createdDate.ToUniversalTime

sqlCmd.Parameters.Item(1).Value = "A test record"

'insert the data

If sqlCmd.ExecuteScalar() Then

Err.Raise(vbObjectError + 1024, Me, "Insert failed to update any records.")

End If

sqlCmd.CommandText = "select created, info from TableDate"

sqlReader = sqlCmd.ExecuteReader

'read all records

While sqlReader.Read

'store the last records value

lastRecord = sqlReader.Item("created").ToString + " -- " + sqlReader.Item("info").ToString

End While

'display the value

MsgBox(lastRecord)

'close the reader

sqlReader.Close()

Catch ex As Exception

MsgBox(ex.Message)

End Try

End Sub

End Class





Re: Visual Basic Language Date and Time Format Problem

lucerias

I have tried the code dt.ToUniversalTime and however the data stored into the database are date and time. I dont really need time to be stored but only date. May i know any solution for this
 
Another problem is after dt.ToUniversalTime is stored and i changed the shorttimeformat under control panel from m/d/YYYY to d/m/YYYY, i can't display it using DateTimePicker.
 
I realized that the problem is lying on the display format of DateTimePicker, is there any way to specify the Date format of the DateTimePicker so then it can store and retrieve the same format I want to make the format to be consistent regardless what the user system time format is.
 
I specified the format of DateTimePicker as the following
 
DateTimePicker1.CustomFormat = "dd/MM/yy"
DateTimePicker1.Format = DateTimePickerFormat.Custom
 
Now i am able to save the date in consistent format in database and however when i retrieve and display in DataTimePicker, it still has conflict with the user system date format.




Re: Visual Basic Language Date and Time Format Problem

MS Johan Stenberg

If you want to persist date time values as strings, and you also want to display them to the user, you usually:

Use a standard, culture and user formatting independent, format in the database/file/wherever. The user is not expected to see this value directly.

When your application reads the value, it knows what format it uses to store the value and can convert the string into a DateTime object.

When your application shows the value to the user (i.e. in the DateTime picker), you use the user's current settings in order to display the DateTime object as a string, and to convert strings that the user may type in to DateTime values.

This means that the format stored in the database should be totally independent of what you show the user in the UI.

After all, the reason that the user can select what date time format they like is so that applications actually show them date time values in that format. It would drive me nuts if I had an application where the developer had decided that dates are specified as "dd/MM/yy" when I in fact wanted to use "MM/dd/yy"...

Best regards,
Johan Stenberg






Re: Visual Basic Language Date and Time Format Problem

lucerias

I understand what you meant, but the most critical part is still how to make it to be formatting independent and enable the application know about the format and convert to datetime accordingly Is there any way to check about user system time format Thank you.



Re: Visual Basic Language Date and Time Format Problem

Dick Donny

Hi Lucerias

There really should be no need whatsoever to handle formatting yourself. The minute you start to do this, you'll create all sorts of totally unncessary problems. Please review your approach considering that databases should store Points In Time and GUI's should show Dates and Times.

Maybe a workflow along these lines would help ...

1 Choose the SmallDate data type for the database (check the dates you need to store are within its bounds first).

2. When saving to the database call the ... ToUniversalTime.Date method to strip the time portion off.

3. When reading from the database, create a new DateTime structure using the static SpecifyKing method. Pass in the database value and kind type of universal to convert back.

If you see yourself declaring a string variable ... stop and reconsider.

Voila ... format independent date time management, a database where points in time (excluding daylight savings) can be compared as opposed to dates which are tied to a timezone, and users with GUI's showing dates in their preferred format.

I hope you have the opportunity to review and consider the above.

Richard