By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,583 Members | 617 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,583 IT Pros & Developers. It's quick & easy.

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

P: n/a
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)

Nov 19 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
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)

Nov 19 '05 #2

P: n/a
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)

Nov 19 '05 #3

P: n/a
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

Nov 19 '05 #4

P: n/a
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

Nov 19 '05 #5

P: n/a
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

Nov 19 '05 #6

P: n/a
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!

Nov 19 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.