CODEWITHSUNDEEP
sqlsql-serversql-server-2008select
Dvirski
Dvirski

Reputation: 321

Sql query for selecting entries with today's date

I have the following table in sql server 2008 (Called "act_events"):

enter image description here

As you notice some of the datetimes are formated: "yyyy-mm-dd" and some: "yyyy-dd-mm" for some weird reason I still haven't figured..

I have a query as follows:

SELECT * from act_events
WHERE '2013-07-30'>=(CAST(e_start as DATE)) AND 
      '2013-07-30'<=(CAST(e_end as DATE))

Where I want to select events only with today's date.

But I can't figure a way to select both formats..

I tries this query:

SELECT * from act_events 
WHERE( @date1>=(CAST(e_start as DATE)) AND
        @date2<=(CAST(e_end as DATE)) ) OR
      ( @date3>=(CAST(e_start as DATE)) AND
        @date4<=(CAST(e_end as DATE)) )

But it only works for certain dates..

Would appreciate your answers.

Also, if there's a statement that will change all datetime to correct format I would love to hear.

Upvotes: 1

Views: 3694

Answers (2)

Mikael &#214;stberg
Mikael &#214;stberg

Reputation: 17156

Assuming the dates are indeed of type DateTime, what you could do in this case is to use dateadd and datediff.

Run these two statements:

-- Todays date, date part only
select dateadd(dd, 0, datediff(dd, 0, getdate()))
-- Tomorrows date, date part only
select dateadd(dd, 0, datediff(dd, 0, dateadd(dd, 1, getdate())))

Using those two, you can do this (Including edits, thanks @gvee)

select *
from act_events 
where 
        e_start >= dateadd(dd, 0, datediff(dd, 0, getdate())) 
    and 
        e_end < dateadd(dd, 0, datediff(dd, 0, getdate()) + 1)

I should mention that getdate() is a built-in function in SQL Server. You could of course change this to a variable if you are using this in a stored procedure for example.

As a side note, the date in SQL Server is actually a number. The format that comes out when you look at it is for humans and humans are not to be trusted, right?

Upvotes: 1

valex
valex

Reputation: 24144

select *

from t 
where 
   CONVERT(nvarchar(30), GETDATE(), 112)=substring(date_c,1,4)
       +substring(date_c,6,2)
       +substring(date_c,9,2)
   or
   CONVERT(nvarchar(30), GETDATE(), 112)=substring(date_c,1,4)
       +substring(date_c,9,2)
       +substring(date_c,6,2)

SQLFiddle demo

Upvotes: 0

Related Questions