Compare null value for datetime

Question

I am storing date as string in sql table. If date is not passed then it will be null. Now I want to check whether date is null from the design page of asp.net. I am getting error of type cast. Any one is having idea how to overcome this?

<%# ((DateTime)Eval("Date")).ToString("yyy-MM-dd hh:mm tt") %>">

Here data is getting as string input.

Thanks

You're just trying to cast the string to a DateTime here, you need to parse it properly. Also, don't store dates in your database as string, store them as dates! — DavidG, Nov 13 '17 at 14:03
*"I am storing date as string in sql table."*. **There is your problem!**. Read Aaron Bertrand's [Bad habits to kick : choosing the wrong data type](https://sqlblog.org/2009/10/12/bad-habits-to-kick-choosing-the-wrong-data-type) — Zohar Peled, Nov 13 '17 at 14:06
It's apparent that the casting error is being caused by the unmatching data types which you're trying to compare, consider changing your date data type from `string` to `DateTime` in the database. — Tebogo Khanye, Nov 13 '17 at 14:14
Also, what type of input control are you using for the date input, e.g. `DateTimePicker` or `TextBox`? — Tebogo Khanye, Nov 13 '17 at 14:18
Thanks for reply, Actually I am displaying table data as it is on UI. If I make column as datetime it will store default date and that will display on UI. To Avoid default date display I made column as varchar. But now I want to I want to perform some operation and that is not allowing null value. — Swapnali Hadawale, Nov 13 '17 at 16:33

score 3 · Answer 1 · answered Nov 13 '17 at 14:10

3

I am storing date as string in SQL table.

This is your first problem (here is a good explanation of why this is a problem).

I am getting error of type cast.

This is how your first problem (storing a date as a string) causes your second problem (type cast error). Although it is possible to fix this by parsing the date from a string using DateTime.Parse, DateTime.ParseExact, or DateTime.TryParse, I would strongly recommend against using any of these approaches.

The good news is that once you fix the first problem, the second problem goes away by itself!

Use this approach to change the type of the column without losing the data:

Add a new column of type date to your table
Run an update statement to populate the new date column from the old varchar column that contains string representations of dates
Drop the old varchar column

answered Nov 13 '17 at 14:10

Sergey Kalinichenko

714,442
84
1,110
1,523

...And hope that nothing references the old column :-) – Zohar Peled Nov 13 '17 at 14:22
@ZoharPeled Right. On the other hand, switching references away from a column of a wrong type is usually an easy exercise of removing unnecessary conversion code, so it may not be all that bad. – Sergey Kalinichenko Nov 13 '17 at 14:29
Well, it depends how old and big is the database and application(s) that uses it. In any case your recommendation is correct IMHO. It's better to fix it (and maybe suffer a bit while fixing everything related to it) than leaving it wrong just for the sake of laziness :-) – Zohar Peled Nov 13 '17 at 14:40

score 1 · Answer 2 · answered Nov 13 '17 at 14:18

Your result variable must be of nullable type (you can not assign null to DateTime). Use DateTime.TryParseExact(). If parsing fails, assign null, else assign the parsed value.

As DavidG noted, this is a hack, actually you should store dates as datetime or datetime2 SQL type!

using System.Globalization;

const string format = "yyyy-MM-dd hh:mm tt";
CultureInfo enUS = new CultureInfo("en-US"); 

string dateString = Eval("Date"); // e.g. "2017-11-13 02:16 PM"

DateTime? result; // final result or null stored here

DateTime parseResult;
if (DateTime.TryParseExact(dateString, format, enUS, DateTimeStyles.None, out parseResult)) {
    result = parseResult;
}
else {
    result = null;
}

// check for null before displaying
string display = result.HasValue ? result.Value.ToString(format) : "null";

.net Fiddle

Compare null value for datetime

2 Answers2