Time format

[#1]

Hi,

I have couple of data sources containing different time formats.

I unsuccessfully tried different different ways to format them as i need... maybe you can help me?

One looks like: "2016-05-01 00:45:00+02:00" and i need to split it in date and time.

best,

A

[sunny_talwar · Accepted solution]

May be this

Date(Floor(TimeStamp#(Left(DateTimeField, 19), 'YYYY-MM-DD hh:mm:ss'))) as Date,

Time(Frac(TimeStamp#(Left(DateTimeField, 19), 'YYYY-MM-DD hh:mm:ss'))) as Time,

[PrashantSangle]

If dates are in different format then check alt() in Help menu.

might help you.

If need more help then some sample data will help us to give better solution.

Regards,

[ahaahaaha]

Hi,

Try, as variant

Date(Timestamp#(Trim(SubField(F, '+', 1)), 'YYYY-MM-DD hh: mm: ss'))

and

Time(Timestamp#(Trim(SubField(F, '+', 1)), 'YYYY-MM-DD hh: mm: ss'))

[#5]

Thank you very much!

[#6]

Thank you Andrey,

Prashant 's solution works, i don't know why your is not but i really appreciate your help.

[hugmarcel]

Use these functions for any known Date Format:

'Formats any DateTime based on any date format ("YYYYMMDD_HHMISS", "DD.MM.YYYY HHhMImSSs", etc)

'Pseudocode:
Function GetDateTime(theDateTime, theFormat)
  vFormDT     = theFormat
  vFormDT     = Replace(vFormDT,"YYYY",AZ(Year(theDateTime),4))
  vFormDT     = Replace(vFormDT,"MM",  AZ(Month(theDateTime),2))
  vFormDT     = Replace(vFormDT,"DD",  AZ(Day(theDateTime),2))
  vFormDT     = Replace(vFormDT,"HH",  AZ(Hour(theDateTime),2))
  vFormDT     = Replace(vFormDT,"MI",  AZ(Minute(theDateTime),2))
  GetDateTime = Replace(vFormDT,"SS",  AZ(Second(theDateTime),2))
End Function

'LPad with "0"
Function AZ(num,pos)
  AZ = LeftPad(num, "0", pos)
End Function