Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
I have a sql query as follows:
select * from Txn_Master
where Txn_Type = 'TPD' and
Txn_Date = Getdate()
order by Txn_ID DESC
But in this sql-server query when i enter date manually like '01/06/2023' it shows data in table but whenever i enter getdate() function to get current date automatically it shows no data only shows blank table
when i enter date manually like '01/06/2023' it shows data in table but whenever i enter getdate() function to get current date automatically it shows no data
GETDATE() returns the current system timestamp as a DATETIME value (date and time to the milliseconds). That's obviously different than just the DATE value string you provided of '01/06/2023'. Your Txn_Date probably truly doesn't have any values with the same milliseconds as the instant in time when you ran the query with GETDATE().
To make your query work with GETDATE(), you need to cast it to just a DATE data type like so:
select * from Txn_Master
where Txn_Type = 'TPD' and
Txn_Date = CONVERT(DATE, GETDATE())
order by Txn_ID DESC
