SQL_CHAR (1) data to Date

[bnelson111]

I have data which does not respond to the following load, which would normally give me a Month.

month(Date(LastUpdatedDateTime)) as [Month Updated],

Text Displayed from LastUpdatedDateTime is 2019-11-19 15:04:19:631 + 00:00.

The above doesn't seem to respond to the  override document settings format function either. 

Thanks Brett

Outcome required for above is the month 11, i want to do same for Day 19 and finally year 2019 so i can create search bars.

[bnelson111]

I have now managed to get what appears to be a date by following

Left(LastUpdatedDateTime, 10) as DateField,

Returns 2019-11-21

i want to in the load statement get the day which is 21. Any ideas.

Year(Date#(Left(LastUpdatedDateTime, 4),'YYYY')) as [Year Updated],

this works for Year returns 2019

[sunny_talwar]

Try this may be

Month(TimeStamp#(Trim(SubField(LastUpdatedDateTime, '+', 1)), 'YYYY-MM-DD hh:mm:ss.fff)) as [Month Updated],

[bnelson111]

Month(TimeStamp#(Trim(SubField(LastUpdatedDateTime, '+', 1)), 'YYYY-MM-DD hh:mm:ss.fff)) as [Month Updated],

Unfortunately no return of data on a List Box for Month Updated

[bnelson111]

Year(Date#(Left(LastUpdatedDateTime, 4),'YYYY')) as [Year Updated],

Above does return a usage year format?? if i could do something similaur on Day and Month from data below.

2019-11-21 09:17:44:780 + 00:00

Thanks Brett

[sunny_talwar · Accepted solution]

Sorry, try this

Month(TimeStamp#(Trim(SubField(LastUpdatedDateTime, '+', 1)), 'YYYY-MM-DD hh:mm:ss:fff')) as [Month Updated],

This time I tested it with your data in a text box object using the following expression

=Month(TimeStamp#(Trim(SubField('2019-11-21 09:17:44:780 + 00:00', '+', 1)), 'YYYY-MM-DD hh:mm:ss:fff'))

[bnelson111]

Absolutely super, in basic terms what was wrong?

[sunny_talwar]

I think with my TimeStamp#() I was readding the micro seconds by using a .fff, whereas the format was using :fff. That changed and secondly, I missed a single quote after my format.