[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Handling null dates

Posted on 2008-02-10
4
Medium Priority
?
763 Views
Last Modified: 2012-06-21
Hello,
I 'm having a rough time handling null dates.  I'm pulling data via odbc with a Script Component.  I keep getting a Specified Cast is not valid everytime it comes
across a date in the source database that's null. For example, I have a quote table with ClosedDate. If the quote is not closed, this field is null.
I've tried using IsDBNull, and IsDate in an if statement to catch it and set it to 01/01/1900 if the date was null, but it seems to ignore them.  
Any help would be greatly appreciated.
Thanks
Chad
0
Comment
Question by:ChadMarsh
  • 3
4 Comments
 
LVL 29

Accepted Solution

by:
QPR earned 2000 total points
ID: 20863673
Can you do this within the select?

select isNull(ClosedDate,'01/01/1900') as ClosedDate
0
 
LVL 2

Author Comment

by:ChadMarsh
ID: 20868281
Thanks for the reply.
The database I'm pulling the data from is a Progress database that only supports SQL-89 so unfortunately, isnull and even ifnull is not an option
0
 
LVL 2

Author Comment

by:ChadMarsh
ID: 20885291
I found a way around it. Instead of pulling the data in as a Reader, pull it in as a Dataset which then using ISDbNull actually works.
I have to put an if statement on just about every field but it works. So for the dates I use
If IsDBNull(OHedRow.Item(10)) Then
                    .OrderDate = Convert.ToDateTime("01/01/1900")
                Else
                    .OrderDate = OHedRow.Item(10)
                End If
Then I have a clean up procedure once it get into MSSQL that sets all the 01/01/1900 dates back to null
Kind of a round about way, but it works.
Thanks for you response. You seem to be the only what that wanted to respond.
0
 
LVL 2

Author Closing Comment

by:ChadMarsh
ID: 31429697
I meant to give you the points..
0

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

612 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question