Date and Time Format Problem

Date and Time Format Problem

• Henrik Dahl

  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

  
  

• Chalagif

  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

  
  
  

• K.Kong

  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

  
  

• Mike**

  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

  
  

• cgraus

  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.

  
  

• Eduardo Couto Parreiras

  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.

  
  

• Ilan D

  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

  
  

• ReneeC

  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

  
  

• Ludo-R

  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

  
  

• koby198

  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.
  

• Marlun

  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

  
  

• John Sudds - MSFT

  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.

  
  

• Dinesh Patel

  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

  
  

• JerryCic

  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

  
  
  
• Date and Time Format Problem