CODEWITHSUNDEEP
sqlsql-server
newbie3
newbie3

Reputation: 93

Compare datevalue with today

I tried to select data when datevalue < than today, but the where condition that I make is not working, it always shows the value even that not right.

This is the query design table database [1]: https://i.sstatic.net/ZgM3n.png

This is the query

SELECT *
FROM iu_trdetail_peminjaman 
WHERE CONVERT(char(10),dt_tgl_pinjam,110) <= CONVERT(char(10), GetDate(),126)
and dt_id_detailpeminjaman  = @p1

Thank you for your help.

Upvotes: 0

Views: 45

Answers (1)

Thorsten Kettner
Thorsten Kettner

Reputation: 94914

You are converting the datetimes to strings in order to compare only the date part. But mistakenly you compare format 110 ('mm-dd-yyyy') with 126 ('yyyy-mm-ddThh:mi:ss.mmm'). You'd have to compare format 23 ('yyyy-mm-dd') for both dates to make this work, because this format only contains the date part and has an appropriate sort order.

A better option is to convert the datetime you get with GETDATE to a DATE. Add a day, so to be able to ask for yourdate < tomorrow (at midnight).

SELECT * 
FROM iu_trdetail_peminjaman 
WHERE dt_tgl_pinjam < DATEADD(DAY, 1, CONVERT(DATE, GETDATE()))
and dt_id_detailpeminjaman  = @p1;

Upvotes: 1

Related Questions