Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Filter Date in Query

Hi all,    I would like to filter always last year and this year from my data source in the query. However, if i were to change the date column from text to date format (so that i can achieve that ...
Need Help
  • v-kkf-msft's avatar
    v-kkf-msft
    4 years ago

    Hi Anonymous ,

     

    If the text date column of your source data contains the year information, you can use the text functions to extract it out. Of course, it will be more convenient if you add a year column to your source data.

    Here is an example when I use SQL Server as my data source.

     

    SELECT [Date],[Year]
FROM [CaseTest].[dbo].[Table_2]
WHERE RIGHT(TRIM([Date]), 4) in (YEAR(SYSDATETIME ( )),YEAR(SYSDATETIME ( ))-1)

     

    If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
    Best Regards,
    Winniz
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Thanks for the quick response - however, I would need it in the query where data is extracted so that i can limit the dataset. 🙂 

v-kkf-msft's avatar
v-kkf-msft
Community Support
4 years ago

Hi Anonymous ,

 

If the text date column of your source data contains the year information, you can use the text functions to extract it out. Of course, it will be more convenient if you add a year column to your source data.

Here is an example when I use SQL Server as my data source.

 

SELECT [Date],[Year]
FROM [CaseTest].[dbo].[Table_2]
WHERE RIGHT(TRIM([Date]), 4) in (YEAR(SYSDATETIME ( )),YEAR(SYSDATETIME ( ))-1)

 

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.