Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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
Try
Subfield(Google_Date,' ',1) as DatePart
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)).
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
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.
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.
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.