I have a fields Name| ID | Timestamp
Timestamp is string like '06/29/2000 00:00:00'
Now I have to filter the table based on date- let say
Select Name
,ID
,Timestamp
From Table Where **Function**(Timestamp)= '2000-06-29' (or 2000/06/29 or 06/29/2000)
I am using SQL assistant as UI tool with Hadoop HI
I tried TO_DATE and couple of other functions.
Please advise
roh :
You can first change your timestamp format and apply the to_date function to trim the time from timestamp.\n\nYou can convert the timestamp format as below.\n\nselect from_unixtime(unix_timestamp('06/29/2000 00:00:00' ,'dd/MM/yyyy HH:mm:SS'), 'yyyy-MM-dd HH:mm:SS') from table;\n\n\nApply to_date function to the above sql. \n\nSelect Name\n ,ID\n ,Timestamp\n From Table Where to_date(from_unixtime(unix_timestamp('06/29/2000 00:00:00' ,'dd/MM/yyyy HH:mm:SS'), 'yyyy-MM-dd HH:mm:SS'))= '2000-06-29'\n\n\nI haven't tried the above solution, as i don't have environment with me right now. Let me know if you ran into any errors.",
2018-07-04T03:02:21
Hanebambel :
You say Timestamp is as string? Have you tried to compare strings?\n\nSelect Name\n ,ID\n ,Timestamp\nFrom Table \nwhere SUBSTR(Timestamp, 1, 10) = '06/29/2000'\n",
2018-07-03T17:33:34