Search FAQs
You are currently viewing the content available in Vietnam. If you are looking for information for another region, please select the correct country from the top-left dropdown in the page and 'Navigate to Browse FAQs' in the Support menu.
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
Struxureware Power Monitoring
Power Monitoring Expert
ION Enterprise
ION EEM
Environment
SQL Server (any version)
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 EEM_DataLog
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))
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
Struxureware Power Monitoring
Power Monitoring Expert
ION Enterprise
ION EEM
Environment
SQL Server (any version)
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 EEM_DataLog
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))
Released for:Schneider Electric Vietnam
Explore more
Product:
Explore more
Product: