The database has a date field defined as varchar. It's causing problems in my app. How can I convert the varchar to datetime so that my date picker will read the date appropriately? In my app I have to specify all project closed after the given date.
In the database, the column is defined as varchar(11). Since it is possible that the values in that column can be NULL, valid datetime or something non-convertible to datetime, you can try using the safe TryParse method of DateTime struct. If the call succeeds, your DateTime variable will be set to some valid datetime. If not then your variable will remain to be DateTime.MinValue (the one it receives when you dim it). For example (using your code from the previous question): Dim dt As DateTime If reader("EndDate") Is DBNull.Value Then dt = DateTime.MinValue Else DateTime.TryParse(reader("EndDate").ToString(), dt) End If Since dt is passed as out parameter, actually ByRef in
VB.NET which does not have out parameters yet despite all the talking about CLR and language compatibilities, your dt will be set to the valid value read from the reader if the value can be converted to valid datetime. Oleg
If the date values in db have some of the standardized formats and the formats are not combined, you can convert them to DateTime during select using CONVERT. SELECT CONVERT(datetime, dbfield, 112) FROM aTable The third parameter is a format specifier and defines the format of the DateTime value in db. For the details take a look in [BOL] :
Why is your Date field defined as VARCHAR? If this column is a part of SARG then using functions or Conversion on your SARG Columns makes the index ineffective (if it have any indexes), results in Index scan operation. If it is specially meant for Date column then its better to change the column to Date type.
If you actually want to convert them within your database then my suggestion would be to create a new table with the data from the existing table and then once the conversion has been verified as accurate drop the original table and rename the new table with the old name. eg: CREATE TABLE CharDates ( ID INT , VarCharDate VARCHAR(12) ) INSERT INTO CharDates ( ID, VarCharDate ) VALUES ( 1, '2010-sep-01' ), ( 2, '01-Mar-2010' ), ( 3, '01-feb-2009' ), ( 4, '30-feb-2009' ) GO SELECT ID AS ID , CASE WHEN NULLIF(ISDATE(varchardate), 0) = 1 THEN CAST(varchardate AS DATETIME) ELSE NULL END AS DateDate INTO DateDates FROM chardates SELECT * FROM DateDates DROP TABLE chardates DROP TABLE datedates