question

technette avatar image
technette asked

Convert varchar to Datetime

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.
datetime
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Oleg avatar image
Oleg answered
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
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Oleg, Thank you. This, as from former posting worked. I really appreciate everyone's input, very valuable... keepers. I spent most of the day trying to get my multiple input controls to react to the optional parameters. It's really strange that sometimes it takes the input from the text boxes and returns the data and most of the time, I'm getting nothing back. I may have to break this up into several reads. don't know how else to accomplish this feat.
0 Likes 0 ·
Pavel Pawlowski avatar image
Pavel Pawlowski answered
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][1] [1]: http://msdn.microsoft.com/en-us/library/ms187928.aspx
4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@Pavel Pawlowski The column of the table is varchar(11). technette does not have a way to alter the table. It is also possible that some of the values in that column are not convertible. For this reason, altering the procedure to use **convert(datetime, dbfield, 112)** will not work because the procedure will raise error when one of the bad values in encountered. This is why I thought that that outsourcing the conversion activity to the VB.NET code is the best way to go. I believe that technette might agree with this approach.
0 Likes 0 ·
Ah.. didn't noticet it was bound to previous questions. But even then a case can beu used @Fatherjack mentioned in his answer. Finally all depends on the whole implementation of the DB and application.
0 Likes 0 ·
@Pavel Pawlowski Admittedly, the binding is not possible to notice:)
I just happen to remember that in [this question][1], technette lists existing code, something like this:
Dim dt As DateTime

If reader("EndDate") Is DBNull.Value Then
    dt = DateTime.MinValue
Else
    dt = Convert.ToDateTime(reader("EndDate")
End If
Since that question was about breaking the code above, I think that the TryParse should be a good option.
Dim dt As DateTime

If reader("EndDate") Is DBNull.Value Then
    dt = DateTime.MinValue
Else
    DateTime.TryParse(reader("EndDate").ToString(), dt)
End If
It is good though that now technette will have several answers to consider and pick whatever suites the implementation. [1]: http://ask.sqlservercentral.com/questions/28289/string-was-not-recognized-as-a-valid-datetime
0 Likes 0 ·
@Oleg, right if it's this concrete situation, but the way the question it looks like a question how to convert varchar field to DateTime. Anyway if he is able to alter the query I'd suggest to put the case into the query, rather than IF statetim in VB. Chaning the query, he doesn't need to take care amout muliple use of the reader and field in the application.
0 Likes 0 ·
Cyborg avatar image
Cyborg answered
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.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@Cyborg technette mentioned on one of the earlier questions that this is because the table design has already been carved in stone, so there is no way to change it now. As far as the conversion is concerned, not only it makes the predicates not SARGable, it also makes any query dog slow even if the SARGability is not a question (like when it is not possible due to lack of the corresponding index anyway). Additionally, introducing a convert into the query does violate the most basic principle of tiering (all formatting activities should be done in the presentation layer, database should have nothing to do with any of it).
0 Likes 0 ·
Fatherjack avatar image
Fatherjack answered
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
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@Fatherjack I think we are going to have to wait for technette with the verdict on this one. I remember it was mentioned in one of the earlier questions that altering the table is not an option, and currently, there are some bad values there. Once the data is safely bound to the presentation layer using the safe DateTime.TryParse, the erroneous values can be revealed and fixed by the application, which then should allow a request to modify the suspect column data type.
0 Likes 0 ·
@Oleg - earlier question? Why no reference to it in this question? Just to save us wasting our time and scattering answers across multiple locations.
0 Likes 0 ·
@Fatherjack I don't know why, I just remember that there was a [question about optional parameters][1], which included the source of the proc and VB.NET code. It helped me a great deal to answer this one. It is a different question though and the fact that technette made it a separate question is justified. Yes, it could benefit from the reference link :) [1]: http://ask.sqlservercentral.com/questions/28228/entity-framework-optional-parameters
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.