Timestamp to date conversion

[prashantsanchet]

Hi Community,

I have a database column in the form of following. i want to get the date in (MM/DD/YYYY) how we write expression for same

Date_TS

20-Jan-18 05.12.12.000000000 AM

21-Jan-18 05.12.10.000000000 AM

[YoussefBelloum]

Hi,

you will need to use Date() and floor() functions.

try this on the script:

=Date(floor(your_field_name))

[prashantsanchet]

This is not working

[YoussefBelloum]

did you replace the field name ?

can you show us what you have so far on the script and on a listbox via images ?

also, try this on the script:

=Date(Floor(Timestamp#(your_field,'DD-MM-YY hh. mm. ss TT'))) as date

and you use this date field on a listbox

[sunny_talwar]

May be something like this

=Date(Floor(Date#('20-Jan-18 05.12.12.000000000 AM', 'DD-MMM-YY hh.mm.ss.fff TT')))

Replace the hard-coded date with your field name in the above example

[ckarras22]

Hi

date(left('20-Jan-18 05.12.12.000000000 AM',9),'MM/DD/YYYY')

[sasiparupudi1]

=Date(Floor(Timestamp#(Date_TS,'DD-MMM-YY hh.mm.ss.fff TT')),'MM/DD/YYYY')

[sumanta12]

Hi Prashant,

You can try like this:

DATE(Date#(LEFT(Date_TS,9),'DD-MMM-YY'),'MM/DD/YYYY')

[Anonymous]

Hi Prashant,

If you have auto generated section in data load editor with autoCalender script then autoCalendar.Date may help you as it converts your date column Timestamp to various formats including  MM/DD/YYYY format.

DateColumn.autoCalendar.Date

If you don't have autocalender then below expression will work for sure.

=DATE(Date#(LEFT(Date_TS,9),'DD-MMM-YY'),'MM/DD/YYYY')

Thank you!

Best Regards,

Sumit Kulkarni

[Anonymous]

Hi,

try below code,i have given tow expressions one only date and one for time stamp

1.Timestamp(Timestamp#(Date_TS,'DD-MMM-YY HH.MM.SS.[FFFFFFFFF] TT'),'MM/DD/YYYY hh:mm:ss tt')  AS DATE,

2.Date(Timestamp#(Date_TS,'DD-MMM-YY HH.MM.SS.[FFFFFFFFF] TT'),'MM/DD/YYYY') AS DATE2

I got o/p like below