Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Guys,
I have data in my excel as 2015-07-04 00:36 format which is M/D/YYYY h:mm:ss[.fff] TT' but suddenly some data came in excel as Sep 7, 2015 5:19:42 PM format so it is not takinf date values that are in new format. if i try to convert to M/D/YYYY it takes only till old format not taking the new format. How can i handle this?
May be this:
Date(Alt(Date#(DateField, 'YYYY-MM-DD hh:mm'), Date#(DateField, 'MMM D, YYYY hh:mm:ss TT'))) as DateField
May be this:
Date(Alt(Date#(DateField, 'YYYY-MM-DD hh:mm'), Date#(DateField, 'MMM D, YYYY hh:mm:ss TT'))) as DateField
use
alt
alt(timestamp#('07/4/2015 00:36:34.000 AM','M/D/YYYY h:mm:ss[.fff] TT'),timestamp#('Sep 7,2015 5:19:42 PM','MMM DD,YYYY hh:mm:ss TT'))
hth
Sasi
Try like
alt(Timestamp#(Date, 'yyyy-mm-dd hh:mm'), Timestamp#(Date, 'MMM dd, yyyy h:mm:ss TT')) as NewDate
can't it be handled without using alt function?
Correct the date format in the source excel file
If you don't want to use Alt, you can use If function, but Alt would be an easier route to take
i mean to say, before whatever format date format data used to come in excel it used to convert to M/D/YYYY but this format it is not converting, user wants us to make it dynamic with whatever format they enter