CODEWITHSUNDEEP
sqldatetimesql-server-2008-r2
user3892880
user3892880

Reputation: 5

How to run same day query for SQL Server

I am creating a package that will run every fifteen minutes and write a csv file into a shared folder. I am stuck on the query. I need the query to only go back to 12:00AM of the same day. Once it's the next day I need to have the query run only for that same day and not go back to the previous day.

I have tried this 

DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE() - 1))

but obviously that isn't producing the result I need.

Everything else on the query is fine and I am receiving the appropriate data I need. However, I'm setting the dates at this time and that is not efficient.

Any help is greatly appreciated. I'm not a SQL Server expert and drudging through the TechNet documents has lots of information. I'm just having a difficult time connecting their information to my implementation.

Upvotes: 0

Views: 2527

Answers (3)

Kritner
Kritner

Reputation: 13765

You can use the following to get the current date (I'm sure there are many other methods as well)

SELECT CONVERT(DATETIME, CONVERT(VARCHAR(10)DATE, GETDATE(), 101))

as the -1 comment pointed out using varchars with dates is a bad idea (though everyone seems to do it all the time. Another solution would be:

SELECT CONVERT(DATE, GETDATE()))

though this won't work until a specific version of SQL server.

Yet another way to do it would be:

SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))

which i believe would work on all sql server versions (though i find the hardest to understand)

The inner (convert varchar) gives you your date without time, then it is converted back into date format.

So for your query you could use

DECLARE @today DATETIME
SET @today = (SELECT CONVERT(DATE, GETDATE()))

SELECT *
FROM table
WHERE date >= @today

Upvotes: 0

steoleary
steoleary

Reputation: 9278

If you are using SQL Server 2008 + you can simply use:

SELECT CAST(GETDATE() AS DATE)

To get the current date.

Upvotes: 2

nbirla
nbirla

Reputation: 610

You can directly use date(now()) it will take 00:00:00 time by default.

Upvotes: 0

Related Questions