I read date by cell_read and insert it into database base field of type date , but its stored as 1900-01-01
what shall i do to save the date correctly
SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.Locale: en-USDateFormat: yyyy-MM-ddPID: 9932 ('w3wp', Started='9/13/2022 12:47:15 PM', Priv=758Mb, Virt=2111115Mb)TID: 131Thread Name:
ReadExcelFile System.Data.SqlTypes.SqlTypeException: SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.
Even cell formatting is not working : TextToDate(FormatDateTime(TextToDate(PONeedDate), "dd/MM/yyyy"))
The table field is
and the excel file is
Hi Omar,
Did you debug your code? Did you check what date was actually returned from the cell_read action? Apparently it's an invalid date for SQL, so what date was it?
This is the string value thats return from Cell_Read : 1900-01-01
If that's the string value, and you convert it with TextToDate(), there's no way that you get that SqlDateTime overflow error. So there must be something else going on.
now i deleted all excel rows and kept only one value
when i used this hard code it works
TextToDate(FormatDateTime(TextToDate("2015-01-01"), "yyyy-MM-dd"))
when i use the value from Cell_Read it returns 1900-01-01
TextToDate(FormatDateTime(TextToDate(PONeedDate), "yyyy-MM-dd"))
!
and no SQL issue appear
i wanna know why Cell_Read , convert it to1900-01-01
by the way the culture on server is en-US and culture on my PC is en-Au is that reason do i need to format the excel ?
So why are you using TextToDate twice, and FormatDateTime as well? That's totally superfluous. TextToDate("2015-01-01") already gives you a Date, there's absolutely no need to make it a text again, then make it a date again.
Also, in the second example, you convert PONeedDate to a Date, but since PONeedDate is already a date, given your image in the original post, it's first implicitly converted to Text, then converted to a date again, then to a text again, then to a date again. You really should not do that! :)
Cell_Read returns CellValue, which is Text. You say that it returns "1900-01-01", but I find that highly unlikely, as 1900-01-01 is an OutSystems null date, but not something specific to Excel. So I'd examine the output of Cell_Read, before you convert it to a Date to store in PONeedDate, and I bet it's not "1900-01-01".
I tried to extend the extension
public void MssCell_Read_Date(object ssWorksheet, string ssCellName, int ssCellRow, int ssCellColumn, string ssDateFormat, out DateTime ssCellValue)
{
//ssCellValue = "";
//if (string.IsNullOrEmpty(ssCellName) && ssCellColumn <= 0 && ssCellRow <= 0)
//{
// throw new Exception("You need to specify a valid cell name (i.e. A4) or cell index (row/column combination)");
//}
//ExcelWorksheet ws = ssWorksheet as ExcelWorksheet;
//try
// ExcelRange cell;
// if (string.IsNullOrEmpty(ssCellName))
// {
// cell = ws.Cells[ssCellRow, ssCellColumn];
// }
// else
// cell = ws.Cells[ssCellName];
// //TextToDate(FormatDateTime(CurrDateTime(), "yyyy-MM-dd"))
// cell.Style.Numberformat.Format = ssDateFormat;
// ssCellValue = Convert.ToString(cell.Value);
//catch (Exception)
// ssCellValue = String.Empty;
ssCellValue = DateTime.MinValue;
if (string.IsNullOrEmpty(ssCellName) && ssCellColumn <= 0 && ssCellRow <= 0)
throw new Exception("You need to specify a valid cell name (i.e. A4) or cell index (row/column combination)");
}
ExcelWorksheet ws = ssWorksheet as ExcelWorksheet;
try
CultureInfo culture = new CultureInfo("en-US");
Thread.CurrentThread.CurrentCulture = culture;
Thread.CurrentThread.CurrentUICulture = culture;
ExcelRange cell;
if (string.IsNullOrEmpty(ssCellName))
cell = ws.Cells[ssCellRow, ssCellColumn];
else
cell = ws.Cells[ssCellName];
cell.Style.Numberformat.Format = ssDateFormat;
ssCellValue = DateTime.ParseExact(cell.Text, ssDateFormat, culture);
catch (Exception)
} // MssCell_Read_Date
still not working
I will check this now
this work TextToDate("2015-01-01")
but when i use this TextToDate(PONeedDate) it return 1900-01-01
even when i store PONeedDate into Text attribute in the entity its stored 1900-01-01
No. If you want me to help you, please do as I say. Check the output of Cell_Read. What is the value of Cell_Read.CellValue.
TextToDate(PONeedDate) is not valid, sensible code. PONeedDate is already a date. You are just converting it to text and to date again. PONeedDate is not the output of Cell_Read.
As @Kilian Hekhuis is suggesting,
try to debug, not just look at the result in your database, that's a step too far.
When I do a cell read on a date cell (and don't set the ReadText to True), it returns a number. Googling date format in excel, you can find that Excel stores dates as a sequence number starting with 01-01-1900 being the date with sequence number 1.
So you can simply add that value with the AddDays function to the NullDate in Outsystems (which by coincidence also happens to be 01-01-1900) to create a correct date value to store in your database.
Caveat 1 : as sequence number 1 represents 01-01-1900, you have to add one day less than the number you read.
Caveate 2 : there is an error in the excel sequence number for all dates larger than 28 february 1900 (so almost all), as excel is considering 29 february 1900 as a real date, which it is not. So substract one more day to correct for this error.
In other words :
entity.dateattribute = AddDays(NullDate(), TextToInteger(Cell_Read.CellValue)-2)
Dorine
and here's the reason for the need to substract 2 instead of 1