WeTi
asked on
SQL Query that will run on schedule and query data need to be currentdate.
SELECT *
FROM [Prod].[Data].[Data_Incomi
WHERE InsertTime >='2018-01-03'
PRINT @@ROWCOUNT
Dear expert
Up there is a simple query that shows InsertTime >='2018-01-03' now i want instead of catch "2018-01-03' I want it first catch execution date, like today is 8/2/2018, I want first make a variable like: $Currentdate and reformat it to '2018-02-08' then add it after the WHERE InsertTime >= $CurrentDate is this possible to do? This is for i want this to run every month 2th of feb. And the data I want to query is current date and format need to be like that '2018-02-02' etc.
Thanks
FROM [Prod].[Data].[Data_Incomi
WHERE InsertTime >='2018-01-03'
PRINT @@ROWCOUNT
Dear expert
Up there is a simple query that shows InsertTime >='2018-01-03' now i want instead of catch "2018-01-03' I want it first catch execution date, like today is 8/2/2018, I want first make a variable like: $Currentdate and reformat it to '2018-02-08' then add it after the WHERE InsertTime >= $CurrentDate is this possible to do? This is for i want this to run every month 2th of feb. And the data I want to query is current date and format need to be like that '2018-02-02' etc.
Thanks
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
what is the output of this:
DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
Output is 2018-02-08 00:00:00.000.
Try this
SELECT *
FROM [Prod].[Data].[Data_Incomi
WHERE InsertTime >=convert(date,DATEADD(DAY
PRINT @@ROWCOUNT
Output of convert(date,DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)) is 2018-02-08 (today's date)
Try this
SELECT *
FROM [Prod].[Data].[Data_Incomi
WHERE InsertTime >=convert(date,DATEADD(DAY
PRINT @@ROWCOUNT
Output of convert(date,DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)) is 2018-02-08 (today's date)
ASKER
Well I want it to be only 2018-02-08 tho. I don't want those 00:00:00
pls check my previous comment. It will have only the date.
ASKER
Ran this in sql studio
Output of convert(date,DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)) is 2018-02-08 (today's date)
Error:
Msg 102, Level 15, State 1, Line 6
Incorrect syntax near 'Output'.
Msg 105, Level 15, State 1, Line 6
Unclosed quotation mark after the character string 's date)
Output of convert(date,DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)) is 2018-02-08 (today's date)
Error:
Msg 102, Level 15, State 1, Line 6
Incorrect syntax near 'Output'.
Msg 105, Level 15, State 1, Line 6
Unclosed quotation mark after the character string 's date)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for the kindly help, worked fine.
Well I want it to be only 2018-02-08 tho. I don't want those 00:00:00
That's not really logical. If it's a datetime, it will always have a time. The time of midnight / 00:00:00 is start of day and is there whether you code it or consider it or not.
Good luck with future qs.
FROM [Prod].[Data].[Data_Incomi
WHERE InsertTime >=getdate()
PRINT @@ROWCOUNT