nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Displaying Invalid Dates

kpelzer29

I would like to create a BIRT report to display invalid dates in the database. If a date is entered with an extra digit, for example 2/9/22010 (instead of 2/9/2010) and I try to convert the value to a Date data type in the SQL query and preview the data set, the following error message box pops up in BIRT: 
 
Cannot execute the statement. 
SQL error #1:Arithmetic overflow error converting expression to data type datatime. 
 
If I remove the SQL to convert the value to a Date and instead just return the value for the date, I do not get an error message and a string is returned such as "7344701". 
 
Is there a way I can display the value in the field as a formatted Date so the reason for date being invalid will be recognizable to someone viewing the report (for example, 2/9/22010) instead of a series of numbers like "7344701"? Thank you!

Find more posts tagged with

Comments

mwilliams

It appears that that number is the number of days from the default date if you did new Date(0,0,0). So, if you took your number that's returned and put it in the days slot in the new Date() function, like:

new Date(0,0,yourNumber), you just might get your date.

kpelzer29

Hi mwilliams, 
 
Thank you for your suggestion! I first created a computed column using the new Date() function you described below and found the default date is Dec 31, 1899. I am using SQL Server 2005 as the database where the dates are stored and the default date for SQL Server is Jan 1, 1901. The difference between the two dates is 366 days, so if I add 366 to the number returned from the database, I can display the date in a way recognizable to someone viewing the report. 
 
For example, 
new Date(0,0,yourNumber + 366) 
 
Thank you so much for your help! 
 
<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="95558" data-time="1328843521" data-date="09 February 2012 - 08:12 PM">
It appears that that number is the number of days from the default date if you did new Date(0,0,0). So, if you took your number that's returned and put it in the days slot in the new Date() function, like: 
 
new Date(0,0,yourNumber), you just might get your date. </blockquote>

mwilliams

I noticed it was a little off, if the number was the one returned for the date you gave, but I figured it was something like that.

Glad it worked!