I have the date column with the data type DATE ; I have stored data from 2018-01-01 00:00:00.0 to this date.
At the time of making a query I would like to know how I do it so that it shows me only the data of the current date.
If I use the getdate() it does not show me any data because it looks for the date and time of the system.
This is the query I have:
select * from SGT_VIST_PRUEBA where TERMINAL='TTQ' AND FECHA=GETDATE() ORDER BY FECHA DESC
I converted the Date column into a short date format
SELECT CONVERT(VARCHAR(10), GETDATE(), 103) -- dd/MM/yyyy format
You only have to convert the current date to the type of date you want by converting the getdate () into date only to bring you only the date, I hope it works for you
select * from SGT_VIST_PRUEBA where TERMINAL='TTQ' AND FECHA=CONVERT(DATE,GETDATE()) ORDER BY FECHA DESC
Convert the two dates to a short date:
select * from SGT_VIST_PRUEBA where TERMINAL='TTQ' and convert(varchar(12),FECHA,105)=convert(varchar(12),GETDATE(),105)
When you use the function GETDATE() not only do you get the date you also get the current time so you do not have any record that corresponds to the current time of the system. The solution would be to only take the date by using CONVERT(DATE, GETDATE()) and filter the data only with the date.
SELECT * FROM SGT_VIST_PRUEBA
WHERE TERMINAL='TTQ'
AND FECHA = CONVERT(DATE, GETDATE())
ORDER BY FECHA DESC
I see that there are already many answers but I still presneto my proposal:
SELECR *
FROM SGT_VIST_PRUEBA
WHERE TERMINAL='TTQ'
AND DATEDIFF(day,FECHA,GETDATE())=0
ORDER BY FECHA DESC;
You must transform it to date the getdate () because by default it's datetime, just like your column, that's why your column has hours.
select * from SGT_VIST_PRUEBA
where TERMINAL='TTQ' AND FECHA=convert(date,GETDATE()) ORDER BY FECHA DESC