Date formate in VB .net.
-
jeshra279 wrote:
In my sql database table, date is stored as "09/15/2009" i.e. in "MM/dd/YYYY" format.
No, its simply stored as a number. The display format is based on your SQL Server settings. You do not need to convert it to another format to work with it, only when you want to display it. Then you do a tostring with the required format - just look in the help for date format, it gives loads of examples.
Bob Ashfield Consultants Ltd Proud to be a 2009 Code Project MVP
-
It is stored as Date format as in my sql database table column datatype is Date. I need to convert this to dd/MM/YYYY format but with date datatype, not in string.
As Bob said previously a Date datatype does not have a format, it merely holds a number representing a date. Only when you need to display it on an output device does it get formatted, and this is done by converting it to a string using a standard or custom format.
-
As Bob said previously a Date datatype does not have a format, it merely holds a number representing a date. Only when you need to display it on an output device does it get formatted, and this is done by converting it to a string using a standard or custom format.
I know it has to be converted to string, but if i convert this to a string instead of date, I am not able to get correct result using the command: cmd = New SqlCommand("SELECT [Client ID] FROM client_details WHERE [Date of Joining] <= '" + dateofj + "'GROUP BY [Client ID]", con) The above results in incorrect data if i use this as an string, thats why i want to convert this into Date format. pls let me know how to convert this into date format.
-
I know it has to be converted to string, but if i convert this to a string instead of date, I am not able to get correct result using the command: cmd = New SqlCommand("SELECT [Client ID] FROM client_details WHERE [Date of Joining] <= '" + dateofj + "'GROUP BY [Client ID]", con) The above results in incorrect data if i use this as an string, thats why i want to convert this into Date format. pls let me know how to convert this into date format.
-
I know it has to be converted to string, but if i convert this to a string instead of date, I am not able to get correct result using the command: cmd = New SqlCommand("SELECT [Client ID] FROM client_details WHERE [Date of Joining] <= '" + dateofj + "'GROUP BY [Client ID]", con) The above results in incorrect data if i use this as an string, thats why i want to convert this into Date format. pls let me know how to convert this into date format.
jeshra279 wrote:
+ dateofj +
Is where your problem lies I suspect. I expect it is in a format something like 10/10/2009 which can either be DD/MM or MM/DD. Put it into a format like '10 Oct 2009' so that SQL Server can be certain of the format.
Bob Ashfield Consultants Ltd Proud to be a 2009 Code Project MVP
-
jeshra279 wrote:
+ dateofj +
Is where your problem lies I suspect. I expect it is in a format something like 10/10/2009 which can either be DD/MM or MM/DD. Put it into a format like '10 Oct 2009' so that SQL Server can be certain of the format.
Bob Ashfield Consultants Ltd Proud to be a 2009 Code Project MVP
Or use a SqlParameter in which case it can be passed directly as a date without having to worry about format at all.
-
Hi All, In my sql database table, date is stored as "09/15/2009" i.e. in "MM/dd/YYYY" format. Now i want to convert this in to "dd/MM/YYYY" i.e.15/09/2009. How to do this? I tried the following: Date cdate = #09/15/2009# ( retrieved from database) Dim ddto As string ddto = cdate.ToString("dd/MM/yyyy") --> get converted to string Dim conDate As Date conDate = DateTime.Parse(ddto,Globalization.CultureInfo.CreateSpecificCulture("pt-US")) Using above i am not able to convert the date back from string format to date format using Parse function. I want finally the date format as dd/MM/YYYY instead of string format. How to do this? Thanks.
A double advice: - store dates and datetimes as such in the database, never store them as strings; - pass dates and datetimes as SQL paramters, avoid passing them as strings. If you do both, you'll never have any date formatting problems, as there no longer is any formatting going on. Formatting should be relevant only in the user interface. :)
Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]
I only read code that is properly indented, and rendered in a non-proportional font; hint: use PRE tags in forum messages
-
A double advice: - store dates and datetimes as such in the database, never store them as strings; - pass dates and datetimes as SQL paramters, avoid passing them as strings. If you do both, you'll never have any date formatting problems, as there no longer is any formatting going on. Formatting should be relevant only in the user interface. :)
Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]
I only read code that is properly indented, and rendered in a non-proportional font; hint: use PRE tags in forum messages
-
As per Your 2nd point, am i passing the dates and datetimes as SQL paramters in my following select command: cmd = New SqlCommand("SELECT [Client ID] FROM client_details WHERE [Date of Joining] = '" + dateofj + "'GROUP BY [Client ID]", con)
No you are not. You are passing a string, and C# is implicitly calling dateofj.ToString() to perform a string concatenation afterwards; that is where the regional settings are sneaking in your code. You need this[^] to be safe. :)
Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]
I only read code that is properly indented, and rendered in a non-proportional font; hint: use PRE tags in forum messages
-
As per Your 2nd point, am i passing the dates and datetimes as SQL paramters in my following select command: cmd = New SqlCommand("SELECT [Client ID] FROM client_details WHERE [Date of Joining] = '" + dateofj + "'GROUP BY [Client ID]", con)
Instead of cmd = New SqlCommand("SELECT [Client ID] FROM client_details WHERE [Date of Joining] = '" + dateofj + "'GROUP BY [Client ID]", con) use: cmd = New SqlCommand("SELECT [Client ID] FROM client_details WHERE [Date of Joining] = @DateOfJ GROUP BY [Client ID]", con) cmd.Parameters.AddWithValue(@DateOfJ, YourDateValueHere)