SQL Query that will run on schedule and query data need to be currentdate.

SELECT *
  FROM [Prod].[Data].[Data_IncomingUpdate]
  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
LVL 1
WeTiAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ManjuIT - Project ManagerCommented:
SELECT *
  FROM [Prod].[Data].[Data_IncomingUpdate]
  WHERE InsertTime >=getdate()
  PRINT @@ROWCOUNT
0
Scott PletcherSenior DBACommented:
If InsertTime is a date or datetime column, you don't need or want to use a string format.  Do this instead:

SELECT *
FROM [Prod].[Data].[Data_IncomingUpdate]
WHERE InsertTime >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
PRINT @@ROWCOUNT

The reason for an expression that looks complicated is: (1) get entire day by stripping the time / forcing it to 00:00:00 (very important); (2) it's a consistent way to easily get any time frame.  For example, say you decide to look at the whole month rather than one day:

WHERE InsertTime >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)
1
WeTiAuthor Commented:
what is the output of this:
DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
0
Challenges in Government Cyber Security

Has cyber security been a challenge in your government organization? Are you looking to improve your government's network security? Learn more about how to improve your government organization's security by viewing our on-demand webinar!

ManjuIT - Project ManagerCommented:
Output is 2018-02-08 00:00:00.000.

Try this


SELECT *
  FROM [Prod].[Data].[Data_IncomingUpdate]
  WHERE InsertTime >=convert(date,DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0))
  PRINT @@ROWCOUNT

Output of convert(date,DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)) is 2018-02-08 (today's date)
0
WeTiAuthor Commented:
Well I want it to be only 2018-02-08 tho. I don't want those 00:00:00
0
ManjuIT - Project ManagerCommented:
pls check my previous comment. It will have only the date.
0
WeTiAuthor Commented:
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)
0
ManjuIT - Project ManagerCommented:
Run This in SQL:

SELECT *
  FROM [Prod].[Data].[Data_IncomingUpdate]
  WHERE InsertTime >=convert(date,DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0))
  PRINT @@ROWCOUNT

Or

Select convert(date,DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0))
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
WeTiAuthor Commented:
Thanks for the kindly help, worked fine.
0
Scott PletcherSenior DBACommented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Powershell

From novice to tech pro — start learning today.