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"

[Katerina Kovriga {Stiltsoft} · Accepted solution]

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.

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:

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()))

[Katerina Kovriga {Stiltsoft}]

Please use the two Table Transformers for this table:

1) The internal TT will add a new column with a date that corresponds to the start of the week

SELECT *,
FORMATDATE(DATEADD(DAY,1-WEEKDAY(NOW()),NOW())) as 'Start of the week'
FROM T1

2) The external TT will compare the dates:

SELECT T1.'Assigned on Date', T1.'Supported/Approved Date'
FROM T1
WHERE T1.'Assigned on Date' >= T1.'Start of the week'
OR T1.'Supported/Approved Date' >= T1.'Start of the week'

So, it is your original query, but we simplify it (split it into two parts). In this case it works correctly for me.

[Rachi Manwal]

Thank you so much, it worked but I have one query why it didn't worked with single TT

[Katerina Kovriga {Stiltsoft}]

As it's not a fully functional SQL database, some complex combined queries may work unstable. Your idea was right, sorry for the inconvenience.

[Rachi Manwal]

It's fine, thank you for the quick help