Formatting Dates in ASP.NET / String was not recognized as a valid DateTime

I know this is a very common issue but cannot find a thread that
resolves the issue.

I need to re-format my dates to "MMM d, yyyy". The problem I have is I
have an MSSQL database that allows <NULLS>.

I'm using this code to format the dates:
<%# IIF((dsCase.FieldValue("inj_beg", Container) <>
nothing),DateTime.Parse(dsCase.FieldValue("inj_beg ",
Container)).ToString("MMM d, yyyy"), "") %>

It works well until it evaluates a <NULL> date value. The code above
still evaluates the <NULL> as true and tries to parse the <NULL> value
which throws the exception:

String was not recognized as a valid DateTime

I've been stuck on this one for over 10 hours...
Please Help! I send you a case of beer.

Thanks so much

gary (rockie card)

Rookie Card

In the SQL statement that pulls the dat try using something like
-------------
coalesce(inj_beg,cast('1901-01-01' as datetime)) as inj_beg
--------------
this will return the "selected bogus date" instead of a NULL value.

I drink Heineken(sp?)

"Rookie Card" wrote:

> I know this is a very common issue but cannot find a thread that
> resolves the issue.
>
> I need to re-format my dates to "MMM d, yyyy". The problem I have is I
> have an MSSQL database that allows <NULLS>.
>
> I'm using this code to format the dates:
> <%# IIF((dsCase.FieldValue("inj_beg", Container) <>
> nothing),DateTime.Parse(dsCase.FieldValue("inj_beg ",
> Container)).ToString("MMM d, yyyy"), "") %>
>
> It works well until it evaluates a <NULL> date value. The code above
> still evaluates the <NULL> as true and tries to parse the <NULL> value
> which throws the exception:
>
> String was not recognized as a valid DateTime
>
> I've been stuck on this one for over 10 hours...
> Please Help! I send you a case of beer.
>
> Thanks so much
>
> gary (rockie card)
>
>

Also, each field of a DataRecord in a DataTable in a Dataset will have an
IsNull property.

"Rookie Card" wrote:

> I know this is a very common issue but cannot find a thread that
> resolves the issue.
>
> I need to re-format my dates to "MMM d, yyyy". The problem I have is I
> have an MSSQL database that allows <NULLS>.
>
> I'm using this code to format the dates:
> <%# IIF((dsCase.FieldValue("inj_beg", Container) <>
> nothing),DateTime.Parse(dsCase.FieldValue("inj_beg ",
> Container)).ToString("MMM d, yyyy"), "") %>
>
> It works well until it evaluates a <NULL> date value. The code above
> still evaluates the <NULL> as true and tries to parse the <NULL> value
> which throws the exception:
>
> String was not recognized as a valid DateTime
>
> I've been stuck on this one for over 10 hours...
> Please Help! I send you a case of beer.
>
> Thanks so much
>
> gary (rockie card)
>
>

Thanks Brad...
Your talking about the SQL statement that pulls the Data from the
Dataconnector in to the Dataset? right?
If so, I'm trying to figure out where to put coalesce...
My SQL statement that pulls from the DataSet is:

"SELECT dbo.v_case.case_no, dbo.v_case.dob, dbo.v_case.inj_beg,
dbo.v_case.inj_end, dbo.v_case.inj_desc, dbo.v_case.claim,
dbo.v_case.wcab_id, dbo.v_case.adj_code, dbo.v_case.app_name,
dbo.v_case.ssn, dbo.v_case.first_name, dbo.v_case.intial,
dbo.v_case.last_name, dbo.v_case.gender, dbo.v_case.add_upated,
dbo.v_case.wcab_loc, dbo.v_case.addr_1, dbo.v_case.addr_2,
dbo.v_case.city, dbo.v_case.""state"", dbo.v_case.zip_5,
dbo.v_case.zip_4, dbo.v_case.phone FROM dbo.v_case WHERE case_no =
@case_no" %>'

and parameter is:
<Parameter Name="@case_no" Value='<%#
IIf((Request.QueryString("case_no") <> nothing),
Request.QueryString("case_no"), "666") %>' Type="Char" />

or are you talking about using coalesce in the code that pulls the data
from the DataSet to the field?

Thanks,
gary

coalesce() is a SQL function.

Example,

"Select coalesce(LastName,'NoLastName') as LastName from Personnel"


"Rookie Card" wrote:

> Thanks Brad...
> Your talking about the SQL statement that pulls the Data from the
> Dataconnector in to the Dataset? right?
> If so, I'm trying to figure out where to put coalesce...
> My SQL statement that pulls from the DataSet is:
>
> "SELECT dbo.v_case.case_no, dbo.v_case.dob, dbo.v_case.inj_beg,
> dbo.v_case.inj_end, dbo.v_case.inj_desc, dbo.v_case.claim,
> dbo.v_case.wcab_id, dbo.v_case.adj_code, dbo.v_case.app_name,
> dbo.v_case.ssn, dbo.v_case.first_name, dbo.v_case.intial,
> dbo.v_case.last_name, dbo.v_case.gender, dbo.v_case.add_upated,
> dbo.v_case.wcab_loc, dbo.v_case.addr_1, dbo.v_case.addr_2,
> dbo.v_case.city, dbo.v_case.""state"", dbo.v_case.zip_5,
> dbo.v_case.zip_4, dbo.v_case.phone FROM dbo.v_case WHERE case_no =
> @case_no" %>'
>
> and parameter is:
> <Parameter Name="@case_no" Value='<%#
> IIf((Request.QueryString("case_no") <> nothing),
> Request.QueryString("case_no"), "666") %>' Type="Char" />
>
> or are you talking about using coalesce in the code that pulls the data
> from the DataSet to the field?
>
> Thanks,
> gary
>
>

Brad,
I added 'coalesce' to my SQL Statement and it succesfully put the Date
"1/1/1901 12:00:00 AM" in place of the <NULL>

Never the less, I am still having issues with evaluation...
I can't seem to correctly evaluate the value coalesce assigned.
I know I am doing something stupid.

IIF ((dsCases.FieldValue("inj_beg", Container) = "1/1/1901 12:00:00 AM
"), "", DateTime.Parse(dsCases.FieldValue("inj_beg",
Container)).ToString("MMM d, yyyy")) %>
**** Reads False****

IIF ((dsCases.FieldValue("inj_beg", Container) = "01-01-1901"), "",
DateTime.Parse(dsCases.FieldValue("inj_beg", Container)).ToString("MMM
d, yyyy")) %> </td>
***Reads False***

IIF ((dsCases.FieldValue("inj_beg", Container) = "(" + Chr(39) +
"1901-01-01" + Chr(39) + " as datetime)), "",
DateTime.Parse(dsCases.FieldValue("inj_beg", Container)).ToString("MMM
d, yyyy")) %> </td>
***Reads False***

Do you have any clues to how I can correctly evaluate the coalesce
assigned value in the above expression?
Any clues?
Thanks,
Gary

I got it... Thanks a million to the square root of 24 Brad! (The code I
posted above worked after I removed a damned space)

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!

[specterman]

Thanks for figuring this one out guys! I just inherited a web-site that is chock full of this date-parsing error. I've inserted the coalesce function into a few pages and it is working great.

Awesome job! And Rookie, I especially liked your summary. Very helpful!