Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor II
Contributor II

Timestamp to Date returns null

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

Labels (2)
1 Solution

Accepted Solutions
Highlighted
Creator III
Creator III

Hi,

try below

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

it is work for me

Capture.PNG

View solution in original post

13 Replies
Highlighted
Master III
Master III

Maybe like this

Date(Floor(eventDate)) as Date

Highlighted
Partner
Partner

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

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
Highlighted
Creator III
Creator III

Hi,

try below may be it will work

 

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

Highlighted
Contributor II
Contributor II

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

Highlighted
Creator III
Creator III

can you paste here some sample data?

it will help to find out exact solution

Highlighted
Contributor II
Contributor II

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

Highlighted
Creator III
Creator III

get some data in excel file and upload

Highlighted
Contributor II
Contributor II

 
Highlighted
Creator III
Creator III

Hi,

try below

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

it is work for me

Capture.PNG

View solution in original post