Date Conversion with +GMT

[#1]

Hi

Im stuck trying to convert this string into a date field:

2015-02-20 10:55:14.1840296 +12:00

I can parse the line manually like this:

Date(Left([dv_rowstartdate],10),'DD/MM/YYYY') As Startdate2,

But would prefer not to.

I was trying this:

Date#([dv_rowstartdate], 'YYYY-MM-DD hh.mm.ss.fffffffff GMT') As StartDate1,

Timestamp#([dv_rowstartdate], 'YYYY-MM-DD hh.mm.ss.fffffffff GMT')  as StartdateTime1,

But I know I havent got it quite right.

[PrashantSangle]

Hi,

try like

Timestamp(timestamp#(trim(subfield([dv_rowstartdate],'+','1')),'YYYY-MM-DD hh:mm:ss.tttttt'))

or

date(date#(trim(subfield([dv_rowstartdate],'+','1')),'YYYY-MM-DD hh:mm:ss.tttttt'),'YYYY-MM-DD hh:mm:ss.tttttt')

Regards

[tamilarasu]

Hi Shane,

Try the below one and let me know,

Date(Date#( [dv_rowstartdate] ,'YYYY-MM-DD hh:mm:ss[.fffffff] +hh:mm')) as StartDate,

Date(Date#( [dv_rowstartdate] ,'YYYY-MM-DD hh:mm:ss[.fffffff] +hh:mm'))  as StartdateTime1

Note: I just used 7 f's [.fffffff] (Milliseconds) based on your time input time format.

[#4]

That worked perfect thanks.

Noob question, why are the microseconds in brackets?

[tamilarasu]

It will work even If you remove the square brackets. See the below thread for example.

Including tenths/hundredths of second in Time

Date(Date#( [dv_rowstartdate] ,'YYYY-MM-DD hh:mm:ss.fffffff +hh:mm'))