how to get the date field from mm/dd/yyyy hh:mm:ss PM

[#1]

can anyone help me on this....................

[swuehl]

To parse a text field with that format as timestamp:

Timestamp#(TEXTFIELD, 'MM/DD/YYYY hh:mm:ss TT')

to create a Date from that:

Date(daystart(Timestamp#(TEXTFIELD, 'MM/DD/YYYY hh:mm:ss TT')),'MM/DD/YYYY')

[#3]

it is not working

[swuehl]

Works for me:

=Date(daystart(Timestamp#('06/24/2013 08:09:23 PM', 'MM/DD/YYYY hh:mm:ss TT')),'MM/DD/YYYY')

So either your format is different or maybe you need to check for typos.

BTW: 'it is not working' is not a very useful comment

[#5]

As it is i tried but unable to get it

Date(daystart(Timestamp#('Concatdate', 'MM/DD/YYYY hh:mm:ss TT')),'MM/DD/YYYY') as Date

can you please check it

[swuehl]

Concatdate is a field name, right?

Then use it without the single quotes as shown in my first example.

Date(daystart(Timestamp#( Concatdate, 'MM/DD/YYYY hh:mm:ss TT')),'MM/DD/YYYY') as Date

[#7]

I tried it but it still not showing up

Date(daystart(Timestamp#(Concatdate, 'MM/DD/YYYY hh:mm:ss TT')),'MM/DD/YYYY') as Date,

[Anonymous]

please provide a sample file with that date field

[stevelord · Accepted solution]

Try:

Date(Floor(Fieldname)) as Fieldname

Floor() drops everything after the decimal, which in the case of the Date 'number' is the hours, minutes, and seconds portion.

Floor()ing might cause issues though, if a given record happens more than once in the same day.  If this issue might exist, you should be able to let the timestamp through as is, then use the Number tab of the object to show the number as a particular kind of date.