Date conversion for 1/5/2016
Adrian Harris asked 7 years ago
I received the error below
Error in “MsGPDestination:.
Line ‘taGLTransactionHeaderInsert_
Parameter ‘Transaction date’
Parameter set value event
1/6/2015 120:00 AM could not be converted to a date :String was not recognized as a valid DateTime
on the csv file the date is 1/6/2016
tried date Calulation Column
date
source Data :system format
Source Format: GP Date Format
Target Format: GP Date Format
also tried c# script
Error in “MsGPDestination:.
Line ‘taGLTransactionHeaderInsert_
Parameter ‘Transaction date’
Parameter set value event
1/6/2015 120:00 AM could not be converted to a date :String was not recognized as a valid DateTime
on the csv file the date is 1/6/2016
tried date Calulation Column
date
source Data :system format
Source Format: GP Date Format
Target Format: GP Date Format
also tried c# script
Answers
@GP_Beat answered 7 years ago
I see that this thread was never answered in fulll.. I’ve upgraded my SC 20.14.0.18 to 20.16.0.31 on a brand new server.. my 2014 SC remains untouched until I’ve validated everything works in the new release..
Now my frustration starts to reach a point where I don’t know what to look for anymore.. Maps that were perfectly working in SC 2014 are no longer working with SC2016.. for all kinds of strange reasons.
I’m too getting this strange “string to date conversion” failure .. the source files are in Excel.. so there is not 100 ways of entering a date in Excel.. but it’s not recognized as such in SC 2016 anymore..
I get also other messages, but I’ll put that into a different thread to not mix-up topics.
Thanks for posting a reply here on why date formats suddenly have changed.
Now my frustration starts to reach a point where I don’t know what to look for anymore.. Maps that were perfectly working in SC 2014 are no longer working with SC2016.. for all kinds of strange reasons.
I’m too getting this strange “string to date conversion” failure .. the source files are in Excel.. so there is not 100 ways of entering a date in Excel.. but it’s not recognized as such in SC 2016 anymore..
I get also other messages, but I’ll put that into a different thread to not mix-up topics.
Thanks for posting a reply here on why date formats suddenly have changed.
ITo replied 6 years ago Were you able to resolve this issue ?
Jo answered 2 years ago
I had a strange source-data date ‘string’ as well, it was a .csv source file and the date that looked like it was MDDYYYY, but on occasion where there was a month that was two digits, such as November, December, etc, then it changed to MMDDYYYY.
What I ended up doing was a two-part translation, the first was an IF statement to put the date string a standard-uniform format:
I then used a standard ‘DATE TRANSLATION’ type of calculation to further convert it to something easily read by GP
You do have to make sure you order your calculations correctly for this, so the CONVERT_DATE if statement had to be before the regular date conversion.
What I ended up doing was a two-part translation, the first was an IF statement to put the date string a standard-uniform format:
if fn.LEN(_DATE) < 8 then
return “0”+_DATE
else
return _DATE
end if
I then used a standard ‘DATE TRANSLATION’ type of calculation to further convert it to something easily read by GP
You do have to make sure you order your calculations correctly for this, so the CONVERT_DATE if statement had to be before the regular date conversion.
Adrian,
I’m assuming that the datetime you provided is a typo
1/6/2015 120:00 AM
as obviously “120:00 AM” isn’t correct so that error would make sense.
But guessing really
1/6/2015 12:00 AM
Not sure that SQL would take that format as I think it would rather have the format in hh:mm:ss format.
Be that as it may, this is a sql conversion error.
For me, I would re-arrange the date format into a friendly SQL format, I use:
YYYYMMDD
making your own custom date format ought to work.
or if it was me, just use vb or C# to split your string up and then return a string in the above format.
SQL should accept that.
patrick
Hello Patrick the date on the .csv file is 1/6/2016, there is no time associate with it 12:00 is defaulted typo on my part
I tried
DateTime dt = DateTime.Parse(_DATE);
return dt.ToString(“yyyy-MM-dd”);
should return String ‘2016-01-16’
I received the error below.
The best overloaded method match for “System.DateTime.Parse(String) has some invalid arguments
Argument 1: cannot convert from System.DateTime to string. ‘2016-01-16’
If I put the code into c# method and call it returns ‘2016-01-16’
static String datetm(string _DATE)
{
DateTime dt = DateTime.Parse(_DATE);
return dt.ToString(“yyyy-MM-dd”);
}
I tried calling c# returning dt;
I tried calling c# return dt.ToString();
Is it possible to post images to forum?
Getting the same issue after upgrading to 2016