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?
 
ManjuConnect With a Mentor IT - 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
 
ManjuIT - Project ManagerCommented:
SELECT *
  FROM [Prod].[Data].[Data_IncomingUpdate]
  WHERE InsertTime >=getdate()
  PRINT @@ROWCOUNT
0
 
Scott PletcherConnect With a Mentor Senior 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
Simplify Active Directory Administration

Administration of Active Directory does not have to be hard.  Too often what should be a simple task is made more difficult than it needs to be.The solution?  Hyena from SystemTools Software.  With ease-of-use as well as powerful importing and bulk updating capabilities.

 
WeTiAuthor Commented:
what is the output of this:
DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
0
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.