473,322 Members | 1,540 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

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)

Nov 19 '05 #1
6 2474
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
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
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

8
by: Gerrit Holl | last post by:
Posted with permission from the author. I have some comments on this PEP, see the (coming) followup to this message. PEP: 321 Title: Date/Time Parsing and Formatting Version: $Revision: 1.3 $...
9
by: Thomas W | last post by:
I'm developing a web-application where the user sometimes has to enter dates in plain text, allthough a format may be provided to give clues. On the server side this piece of text has to be parsed...
1
by: hansiman | last post by:
In a form a user enteres two dates (from and to) in format dd-mm-yyyy. How do I check if to >= from. All the different attempts end up in "String was not recognized as a valid DateTime" when I...
2
by: Jon | last post by:
I have an asp.net app that I've localized using satellite assemblies. It works wonderfully, even for displaying dates. My problem comes when I try to use one of those dates in a SQL query. I...
1
by: John | last post by:
Hi I am using the below code to assign a date (entered in uk format) to a datetime variable. Dim myDateTimeUK As System.DateTime Dim ukCulture As CultureInfo = New CultureInfo("en-GB") ...
1
by: database | last post by:
I AM FACING A PROBLEM WHEN I ACCESS ONE OF THE PAGES. IT USED TO WORK BEFORE BUT AFTER RESTARTING THE APPLICATION SEVER IT GIVES THE FOLLOWING ERROR. - Server Error ...
5
by: Smokey Grindle | last post by:
Ok I must admit I stink at regular expressions... been trying to learn them for a while now and its not sticking how I wish it would... but I am trying to take a very long string (about 30KB) and...
4
by: koonda | last post by:
Hi all, I am working on a project. Everything is working fine except the date formating. I have five listboxes and a dropdownlist box controls. The 5 listboxes I have populated from the underlying...
0
by: Andres Bohren | last post by:
Hi i have a GridView and have Problems to put the values into the right Datatypes. Protected Sub GridView1_RowUpdating(ByVal sender As Object, ByVal e As...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.