Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Removing the Time from Timestamp from Google Doc

I'm trying to remove the time from the Timestamp of a google doc spreadsheet. I need to get the date only for display and grouping purposes.

Anyone have an idea?

Dan

1 Solution

Accepted Solutions
nagaiank
Specialist III
Specialist III

Try

Subfield(Google_Date,' ',1) as DatePart

View solution in original post

12 Replies
Not applicable
Author

Hi Dan,

To convert a timestamp to a date, you need to round the underlying numeric value to remove the decimals corresponding to the time. After this you convert the numeric value to a date.

Example:
LOAD
date(floor(TimestampField)) AS Date
FROM DataSource;

Kind regards

Staffan

Not applicable
Author

When I add the code from above it loads a blank field. I've added a quick Qlikview that has a link to a timestamp in a Google Doc.

Dan

Not applicable
Author

Does anyone have any ideas on getting the timestamp out of a google doc?

I tried the date(floor(timestampfield)) as DATE but it did not work for me. Can anyone make it work?

Dan

nagaiank
Specialist III
Specialist III

Try

Subfield(Google_Date,' ',1) as DatePart

Not applicable
Author

Are you trying to get the time or the date? date(floor(timestampfield)) will give you the date associated with the field and would give you a null if you tried to specify it was a timestamp.

If you have something like

12//01/2011 10:55:22

and you wanted to get 10:55:22 you could do the following:

timestamp(right(timestampfield,8))

or

timestamp(subfield(timestampfield,' ',2)).

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

The datetime field is a string so you need to use the timestamp#() function to read it. eg

timestamp#(F2,'MM/DD/YYYY hh:mm:ss') as Datetime_Google

Attached is a an example -- using preceeding load instead of the RESDENT load. The timestamp#() method remains the same.

-Rob

colinh
Partner - Creator II
Partner - Creator II

You can also use dayname() instead of date(floor()).  dayname() sets the timestamp to 12:00:00.

dayname(Datetime_Google) as Date should do the same as date(floor(Datetime_Google)) as Date.

Not applicable
Author

Rob's addition definitely should get this to work for you.

Just wanted to add a note regarding the use of string functions as left() and right() to convert timestamps.
What you need to keep in mind is that timestamps, dates and times are represented by both a string value and a numeric value.

Example:
String | Numeric
2011-06-07 09:00:00 | 40701.375

2011-06-07 12:00:00 | 40701.5

If you were to read this field using left(Field, 10), you would strip the time from the string representation but not from the numeric value. The effect this has is that they will be considered as two different values by QlikView and will not link to the same values in other fields.

To avoid this you should instead modify the numeric value and then convert the field using e.g. date() to the desired string representation.

Not applicable
Author

swn,

Thanks for the explaining. One question though. If you use the TimeStamp#() function don't you keep the numerical date but only format the field to a timestamp? If Sith88 is trying to get only get the timestamp, regardless of day, is there any way do this without converting it to a string?

In the past I've had to resort to timestamp#(timestamp(right(F2,8))) to get it to match other true timestamp fields.