{}

브랜드

Impact-Company-Logo-English Black-01-177x54

슈나이더 일렉트릭 코리아 웹사이트에 방문해 주셔서 감사합니다.

슈나이더 일렉트릭 코리아 웹사이트 방문자 분들 모두 환영합니다.
FAQ 검색
How to create SQL queries that return specific calculated dates respective to today's date and to avoid hard coding these dates within the query itself

Issue
How to create SQL queries that return specific calculated dates respective to today's date and to avoid hard coding these dates within the query itself.

Product
Power Monitoring Expert 8.x
Power Monitoring Expert 9.x
Power Monitoring Expert 2020
Power Monitoring Expert 2021
Power Monitoring Expert 2022
Power Monitoring Expert 2023

Environment
SQL Server Management Server

Resolution
*Warning: Irreparable database damage can occur. This procedure should only be performed by users familiar with SQL Server Management Studio. Databases should be backed up prior to performing this procedure.*

The following sample queries (ex. 1-5) depict the use of various SQL functions to calculate and return specific dates respective to today's date.
These SQL queries can then be used as sub-queries within another SQL query's WHERE clause to retrieve specific period of data based on the dynamically calculated date range. (ex. 6)  The below queries illustrate just a small sample of the possible use cases. These can be used in many other situations and tailored to meet your specific requirement.

1) Select CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(GETDATE())),GETDATE()),101) AS Date_Value, 'Last Day of Previous Month' AS Date_Type

2) Select CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(GETDATE())-1),GETDATE()),101) As Date_Value, 'First Day of Current Month' AS Date_Type

3) Select CONVERT(VARCHAR(25),GETDATE(),101) As Date_Value, 'Today' As Date_Type

4) Select CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,GETDATE()))),DATEADD(mm,1,GETDATE())),101) As Date_Value, 'Last Day of Current Month' As Date_Type

5) Select CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,GETDATE()))-1),DATEADD(mm,1,GETDATE())),101) As Date_Value, 'First Day of Next Month'As Date_Type

6) Select * from DataLog2
WHERE
TimestampUTC >=  (SELECT CONVERT(VARCHAR(25),GETDATE(),101))
AND
TimestampUTC <=  (SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,GETDATE()))),DATEADD(mm,1,GETDATE())),101))

슈나이더 일렉트릭 Korea

자세히 알아보기
제품군
유용한 문서
자세히 알아보기
제품군