Extract current date without timestamp in the dataset

Question & Answer
, ,
1

Hi all,

In the dataset, how can I extract data for current_date in quicksight to return only the date without timestamp? I tried using now() and CURRENT_DATE withformatDate and parseDate but it throws a SQL error

time frame required: Current_Date - 180 to Current_Date.

Thanks
Tanisha

2

You can use truncDate.

truncDate('DD',now())
3

Hi Max, I encounter an error upon using this. I get the same when I use formatDate as well.

image
image1385×639 56.7 KB

4

@Tanisha_Shetty - It seems you are using SQL syntax. In that case you have to stick to the underlying database’s SQL syntax. What @Max suggested is to use the native QuickSight function in the calculated columns and NOT in the Custom SQL query. If you are unable to do it in SQL, please let us know the database type and we can try to provide you the code to get what you are looking for. Hope this helps!

5

Hi Sagnik,

Thanks for the clarification. Yes, it is failing at the custom SQL level. I tried it via SQL code as well and it fails. I’m using Redshift database type.

image
image954×795 61.3 KB

Many thanks in advance!

Regards,
Tanisha

6

@Tanisha_Shetty - Thanks for the confirmation. Given that you are trying to extract only the date portion from a timestamp, did you try the TRUNC function as documented in the following link ? Let me know if that works for you!

1 Like
7

Thanks Sagnik!! This worked well… I was trying to use a wildcard character RUNDATE instead of the sysdate command… Thank you!

1 Like