CODEWITHSUNDEEP
sqldatabasedate
user3006617
user3006617

Reputation: 13

How to query today's date in SQL when date not properly formatted

I have a device that generates logs into a database.

I need to query the database

  1. Query1 = give me all the records and columns from TODAY from table01

  2. Query2 = give me all the records and columns from YESTERDAY from table01

The problem I am having is that the date field is not exactly in a nice mm-dd-yyyy format.

Fields are:

datetime, device, controlnumber, result

the datetime field outputs data that looks like this yyyy-mm-ddTHH:MM:SS (not sure why there is a T except maybe its just plainly saying this is the time):

2018-07-16T13:34:00
2018-07-16T18:15:00
2018-07-16T18:33:00
2018-07-16T18:50:00
2018-07-16T19:02:00

Can anyone help me through a quick (i know this is not right): 

select * from table01
where datetime = Today()
and datetime = Yesterday()
Order by controlnumber

Upvotes: 0

Views: 2463

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270713

In standard SQL you would do this for today:

select t.*
from table01 t
where t.datetime >= current_date and
      t.datetime < current_date + interval '1 day'
Order by controlnumber;

Yesterday is similar:

select t.*
from table01 t
where t.datetime >= current_date - interval '1 day' and
      t.datetime < current_date 
Order by controlnumber;

Just because this is standard SQL doesn't mean that all databases support it. However, all databases do support date arithmetic, although the syntax might vary.

Upvotes: 1

Ehsan
Ehsan

Reputation: 785

I think the easiest way to do it is using DATEDIFF() function as follow:

SELECT * FROM Ttable
WHERE DATEDIFF(day,Ttable.DatetimeColumn ,GETDATE()) = 0 -- For today

SELECT * FROM Ttable
WHERE DATEDIFF(day,Ttable.DatetimeColumn ,GETDATE()) = 1 -- For yesterday

Upvotes: 0

Related Questions