R
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.FieldValue("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!
<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.FieldValue("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!