CODEWITHSUNDEEP
sql-serverdatetime
Villager A
Villager A

Reputation: 39

Filter datetime in SQL Server

I know it is a stupid question but I can't get any idea even I asked Google.

Here's my scenario:

I have a table in my database named Sales with a column named dateTime.

The dateTime format is like this 2014-07-29 21:07:36.000.

Now I wish to view all data in 2014-07-XX.

Can anyone help? Thank you and sorry for my bad English :)

Upvotes: 0

Views: 2551

Answers (4)

petelids
petelids

Reputation: 12815

You could use datediff.

Select * from
sales
where datediff(mm, '01-jul-2014', [dateTime]) = 0

This would allow you to pass the comparison date as a parameter easily.

From MSDN -

Returns the count (signed integer) of the specified datepart boundaries crossed between the specified startdate and enddate

In this case we are using mm to get the difference in months of the two dates and checking that their difference is 0.

Upvotes: 1

Mithrandir
Mithrandir

Reputation: 25337

You could do something like:

SELECT <YouColumns> FROM Sales 
WHERE dateTime >= '2014-07-01' AND dateTime < '2014-08-01';

This way SQL Server could even use an index on dateTime, if it had one.

Upvotes: 1

Daniel E.
Daniel E.

Reputation: 2059

Declare @From datetime='7/1/14'
    ,@To datetime='7/31/14'

Select *
FROM Sales
Where [dateTime]>=@From 
    and [dateTime]<@To+1 --to get last day greater than midnight.

This will capture all dateTimes including the last days timestamps. You should probably not use dateTime as a column name.

Upvotes: 0

PeterRing
PeterRing

Reputation: 1797

Something like:

Select * from
sales
where Month(dateTime)=7 and Year(dateTime)=2014

Upvotes: 0

Related Questions