Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Talk to Experts, a LIVE Q&A Webinar. Bring your Qlik Sense Business questions on Aug. 4th. Register
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor
Contributor

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

Re: Timestamp to Date returns null

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

Re: Timestamp to Date returns null

Maybe like this

Date(Floor(eventDate)) as Date

Highlighted
Partner
Partner

Re: Timestamp to Date returns null

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
Tags (2)
Highlighted
Creator III
Creator III

Re: Timestamp to Date returns null

Hi,

try below may be it will work

 

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

Highlighted
Contributor
Contributor

Re: Timestamp to Date returns null

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

Highlighted
Creator III
Creator III

Re: Timestamp to Date returns null

can you paste here some sample data?

it will help to find out exact solution

Highlighted
Contributor
Contributor

Re: Timestamp to Date returns null

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

Re: Timestamp to Date returns null

get some data in excel file and upload

Highlighted
Contributor
Contributor

Re: Timestamp to Date returns null

 
Highlighted
Creator III
Creator III

Re: Timestamp to Date returns null

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