nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

extracting only date from datetime

Rachi Manwal

Hi, I was trying to extract only date from date time, but not sure which function to use. It would be very helpful if someone can guide.

SELECT DATEADD(DAY,1-WEEKDAY(NOW()),NOW())

output: Mon Sep 26 2022 21:09:46 GMT+0530 (India Standard Time)

expected output: I only want date "Sep 26 2022"

Accepted answers

Katerina Kovriga {Stiltsoft}

Hi @Rachi Manwal ,

By the provided query I assume you are using the Table Transformer macro provided by our app that is Table Filter and Charts for Confluence.

If this is so, go to the macro settings -> Settings tab -> Date format. Specify the desired output format. For example, "Sep 26 2022" corresponds the "M d yy" string (type it manually).

Then return to your query and add the FORMATDATE function before the manipulations with dates, for example, for the provided piece:

SELECT FORMATDATE(DATEADD(DAY,1-WEEKDAY(NOW()),NOW()))
FROM T1

This should help your case.

All comments

Katerina Kovriga {Stiltsoft}

Hi @Rachi Manwal ,

By the provided query I assume you are using the Table Transformer macro provided by our app that is Table Filter and Charts for Confluence.

If this is so, go to the macro settings -> Settings tab -> Date format. Specify the desired output format. For example, "Sep 26 2022" corresponds the "M d yy" string (type it manually).

Then return to your query and add the FORMATDATE function before the manipulations with dates, for example, for the provided piece:

SELECT FORMATDATE(DATEADD(DAY,1-WEEKDAY(NOW()),NOW()))
FROM T1

This should help your case.

Rachi Manwal

Hi @Katerina Kovriga _Stiltsoft_ Thank you, I was able to extract date part from the complete date. However, greater than and smaller than are not working fine after I formatted the date.

This is the query, I have used:-

SELECT [Assigned on Date], [Supported/Approved Date]
from T2 where FORMATDATE([Assigned on Date]) >= FORMATDATE(DATEADD(DAY,1-WEEKDAY(NOW()),NOW()))
or FORMATDATE([Supported/Approved Date]) >= FORMATDATE(DATEADD(DAY,1-WEEKDAY(NOW()),NOW()))

Katerina Kovriga {Stiltsoft}

Hi @Rachi Manwal ,

As I understand now, you are trying to compare your dates with the today's date plus/minus several days.

Wed 7-1.png

For example, for this table I need only dates with a red background: today is 28 Sep 2022, so the start date will be 26 Sep 2022. It means that it will be "today - 3 days". Then every day the "today" date moves and the start date will be 27 Sep 2022, 28 Sep 2022, etc.

Here is my SQL query:

SELECT T2.'Assigned on Date', T2.'Supported/Approved Date'
FROM T2 WHERE DATEDIFF(day, 'Assigned on Date', CURRENT_TIMESTAMP) <= 3 OR
DATEDIFF(day, 'Supported/Approved Date', CURRENT_TIMESTAMP) <= 3

And here is the result:

Wed 7-2.png

As you can see, this query works without additional FORMATDATE functions and looks rather simple - hope it helps.

Rachi Manwal

Actually I am comparing the dates with the current week start date, as I have to filter records which are created in the current week, and for the same I am using below query. But through this time is getting included in the comparison which is giving me incorrect results

SELECT * FROM T1
WHERE (DATEADD(DAY,0,[Assigned on Date]) >= DATEADD(DAY,1-WEEKDAY(NOW()),NOW()) AND DATEADD(DAY,0,[Assigned on Date]) <= DATEADD(DAY,7-WEEKDAY(NOW()),NOW()))

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

confluence

filter

table

table-filter

table-filter-and-charts

table-transformer