FAQ Reformating the Dates in ASP.NET / MSSQL Database that has some <NULL> values

[Rookie Card]

Issue - Reformating the Dates in ASP.NET from a MSSQL Database that has

<NULL> values in a SmallDateTime Field
to read "Dec 8, 2000" instead of "12/8/2000 12:00:00 AM"


(As you might already know "DateTime.Parse" will throw an exception
when it comes accross a <NULL> date value so some logic has to be
written.


1) In the SQL statement that builds the DataSet I used 'coalesce' to
assign a temp bogus date that will never occur in my database to take
place of the <NULL> (01-01-1901)


Example:
"SELECT coalesce(inj_end,cast(" + Chr(39) + "1901-01-01" + Chr(39) + "
as datetime)) as inj_end
FROM inq_case


2) In the ASP page I have an IFF statement that pretty much says "If
you are "1901-01-01" you get formated as "" and if you are not you get
formated as "MMM d, yyyy"


Example:
<%# IIF ((dsCases.FieldValue("inj_end"­, Container) = "1/1/1901
12:00:00
AM"), "", DateTime.Parse(dsCases.FieldVa­lue("inj_end",
Container)).ToString("MMM d, yyyy")) %>


Notice I had to use "1/1/1901 12:00:00 AM" instead of "1901-01-01"
(That's VB.NET trying to help you with smalldatetime field from MSSQL)


Thats it!

 

[Daniel Fisher\(lennybacon\)]

Why don't you read

_cmd.CommandText = "SELECT inj_end FROM inq_case";
object _result = cmd.ExecuteScalar();
string DateStr = "";
if(result!=null)
{
DateStr =DateTime.Parse(result.ToString()).ToShortDate();
}

If you use DataSets you can check for

if(ds.Tables[0]["inj_end"] != DBNull.Value)
{
DateStr =DateTime.Parse(ds.Tables[0]["inj_end"] .ToString()).ToShortDate();
}

--
Daniel Fisher(lennybacon)
http://www.lennybacon.com/


Issue - Reformating the Dates in ASP.NET from a MSSQL Database that has

<NULL> values in a SmallDateTime Field
to read "Dec 8, 2000" instead of "12/8/2000 12:00:00 AM"


(As you might already know "DateTime.Parse" will throw an exception
when it comes accross a <NULL> date value so some logic has to be
written.


1) In the SQL statement that builds the DataSet I used 'coalesce' to
assign a temp bogus date that will never occur in my database to take
place of the <NULL> (01-01-1901)


Example:
"SELECT coalesce(inj_end,cast(" + Chr(39) + "1901-01-01" + Chr(39) + "
as datetime)) as inj_end
FROM inq_case


2) In the ASP page I have an IFF statement that pretty much says "If
you are "1901-01-01" you get formated as "" and if you are not you get
formated as "MMM d, yyyy"


Example:
<%# IIF ((dsCases.FieldValue("inj_end"­, Container) = "1/1/1901
12:00:00
AM"), "", DateTime.Parse(dsCases.FieldVa­lue("inj_end",
Container)).ToString("MMM d, yyyy")) %>


Notice I had to use "1/1/1901 12:00:00 AM" instead of "1901-01-01"
(That's VB.NET trying to help you with smalldatetime field from MSSQL)


Thats it!