Timestamp to Date returns null

[shirleyc40]

I have a timestamp from SQL in the form 'MM/DD/YYYY hh:mm:ss.fff TT' The type of it is varchar

I tried Date(timestamp#(eventDate, 'MM/DD/YYYY hh:mm:ss.fff TT'))  but it returns null. I tried using subfield: 

Date#(SubField(EventDateTime, ' ', 1), 'MM/DD/YYYY') which returns 'MM/DD/YYYY', but when I try to wrap it in the date function, I get null again. But if I manually put in a string it works. I'm confused on how I can make this timestamp  into a date so I can use it in the date picker

[arulsettu]

Maybe like this

Date(Floor(eventDate)) as Date

[Vegar]

It could be that you have some initial or ending white space in your string from SQL. 

What is the output of eventDate and what is the output of timestamp#(eventDate, 'MM/DD/YYYY hh:mm:ss.fff TT') do they give a numerical (right aligned in a filter box) or string (right aligned in a filter box) output?

If eventDate it self is numerical then you could just wrap an DayName() () around it like this: DayName(eventDate)

If timestamp#(eventDate, 'MM/DD/YYYY hh:mm:ss.fff TT') is numerical then you could wrap a DayName() around it like this: DayName(Timestamp#(eventDate, 'MM/DD/YYYY hh:mm:ss.fff TT'))

If neither of the two are numerical then I would try to check for white spaces in the field values. Try wrapping a Trim() around your expressions like I've done below:

DayName(Timestamp#(trim(eventDate), 'MM/DD/YYYY hh:mm:ss.fff TT'))

DayName(trim(eventDate))

I hope some of these tips will help you out.

/Vegar

[NitinK7]

Hi,

try below may be it will work

Date(Date#(eventDate, 'MM/DD/YYYY hh:mm:ss.fff TT'),'DD/MM/YYYY')

[shirleyc40]

They're both strings, and I tried the trim, but it is still null 😞 

[NitinK7]

can you paste here some sample data?

it will help to find out exact solution

[shirleyc40]

Like upload a file? Or paste some data? If it's a file, does it matter that there is a SQL connection in it?

[NitinK7]

get some data in excel file and upload

[NitinK7 · Accepted solution]

Hi,

try below

DATE(DATE#(SubField(eventDate, ' ',1),'DD/MM/YYYY'),'DD/MM/YYYY')

it is work for me