Rabu, 05 September 2012

sql between

BETWEEN SQL Server operator use to get a range of data between two values.

SELECT ColumnName(s) FROM TableName WHERE ColumnName BETWEEN value1 AND value2.

Example 1:
select InvoiceNo from (
    select ('YAN/08/12/001')InvoiceNo,('2012/08/20 09:43:29.993')InvoiceDate union
    select ('YAN/08/12/002'),('2012/08/21 09:44:29.993') union
    select ('YAN/08/12/003'),('2012/08/22 09:45:29.993') union
    select ('YAN/08/12/004'),('2012/08/23 13:46:29.993') union
    select ('YAN/08/12/005'),('2012/08/24 09:47:29.993')
)v0 where InvoiceDate BETWEEN ('2012/08/21') AND ('2012/08/23')

#Results:
InvoiceNo
YAN/08/12/002
YAN/08/12/003

In fact there are 3 invoice for that period, but on above query result only 2. Modif above query like below (Example 2) to get actual results.

 Example 2:
select InvoiceNo from (
    select ('YAN/08/12/001')InvoiceNo,('2012/08/20 09:43:29.993')InvoiceDate union
    select ('YAN/08/12/002'),('2012/08/21 09:44:29.993') union
    select ('YAN/08/12/003'),('2012/08/22 09:45:29.993') union
    select ('YAN/08/12/004'),('2012/08/23 13:46:29.993') union
    select ('YAN/08/12/005'),('2012/08/24 09:47:29.993')
)v0 where CONVERT(DATE,InvoiceDate BETWEEN ('2012/08/21') AND ('2012/08/23')

#Results:
InvoiceNo
YAN/08/12/002
YAN/08/12/003
YAN/08/12/004

Tidak ada komentar:

Posting Komentar