CODEWITHSUNDEEP
sql-server
Safwan
Safwan

Reputation: 85

SQL Server Current Date compare with specific date

I got 1 table which is dbo.Invoice. My current query now is able to select "SalesRef" that does not have invoice for "Mvt_Type" = '122'. However, I need to extend my query with PostDate field.

My problem is current query still display an SalesRef that does not have invoice for "Mvt_Type" = '122' with Postdate today( 8/8/2017). My expected result is it can only be display if no invoice was made more than 2 days after the Postdate. So, it suppose to display on 11/8/2017 or more.

Table dbo.Invoice

| PO_NUMBER     | TYPE  | MVT_TYPE  | QUANTITY  | SALESREF  | DEBIT     | POSTDATE      |
|-----------    |------ |---------- |---------- |---------- |-------    |------------   |
| 10001001      | GR    | 101       | 1000.00   | 5001      | S         | 2017-01-08    |
| 10001001      | GR    | 101       | 2000.00   | 5002      | S         | 2017-02-08    |
| 10001001      | GR    | 122       | 1000.00   | 5001      | H         | 2017-01-08    |
| 10001001      | INV   | 000       | 1000.00   | 5001      | S         | 2017-01-08    |
| 10001001      | INV   | 000       | 2000.00   | 5002      | S         | 2017-02-08    |
| 10001001      | GR    | 122       | 1500.00   | 5002      | H         | 2017-02-08    |
| 10001001      | INV   | 000       | 1000.00   | 5001      | H         | 2017-01-08    |

Below is my current query :

  SELECT * 
FROM dbo.INVOICE i 
WHERE MVT_TYPE = '122' AND SALESREF IS NOT NULL AND POSTDATE > CONVERT(VARCHAR(10), dateadd(day,2,getdate()),101) 
AND NOT EXISTS (SELECT 1 
FROM dbo.INVOICE 
WHERE DEBIT = 'H' AND  MVT_TYPE = '000' AND SALESREF = i.SALESREF )

Expected Result is same like below. But this time need to add PostDate.

| PO_NUMBER     | TYPE  | MVT_TYPE  | QUANTITY  | SALESREF  | DEBIT     | POSTDATE      |
|-----------    |------ |---------- |---------- |---------- |-------    |------------   |
| 10001001      | GR    | 122       | 1500.00   | 5002      | H         | 2017-02-08    |

Upvotes: 2

Views: 6721

Answers (3)

Dimitri
Dimitri

Reputation: 7013

If PostDate is DATE or DATETIME, instead of casting you could use DATEDIFF function to get the days between two dates and do the INT comparison:

WHERE DATEDIFF(DAY, PostDate, GETDATE())>2

If PostDate is varchar, stored in the format shown in the OP:

SET LANGUAGE british

SELECT ....
WHERE DATEDIFF(DAY, CAST(PostDate as datetime), GETDATE())>2

EDIT: Apparently DATEDIFF will work if PostDate is VARCHAR data type as well

DECLARE @PostDate VARCHAR(50)

SET @PostDate='08-01-2017'

SELECT DATEDIFF(DAY, @PostDate, GETDATE()) -- GETDATE() is 08-08-2017

-- Returns 7

Having said this, it is a good practice to keep Dates and Times as proper data types. In your case, you could change the data type to DATE, if possible. Will speed up lookups

EDIT 2: Please note, SQL Server works with ISO 8601 Date Format, which is YYYY-MM-DD, but the dates in OP's example, even though as per OP refer to dates in August 2017, are given incorrectly (referring to Jan and Feb 2017) and are stored as varchar. For correct results, these need to be either converted to DATE/DATETIME data type, or reformatted with the correct ISO format.

EDIT 3: Showing an example of casting OP's date format into proper, ISO format before calling DATEDIFF:

SET LANGUAGE british

DECLARE @PostDate VARCHAR(50)

SET @PostDate='2017-01-08'

SELECT DATEDIFF(DAY, CAST(@PostDate AS DATETIME), GETDATE()) -- GETDATE() is 08-08-2017

-- Returns 7

And the WHERE clause would be as follows:

-- In the begining of the select statement

SET LANGUAGE british

SELECT *
FROM ...
WHERE DATEDIFF(DAY, CAST(PostDate as datetime), GETDATE())>2

Upvotes: 1

sepupic
sepupic

Reputation: 8687

Your problem is that you store a date as a varchar.

To compare 2 dates correctly you should compare their DATE rappresentation, not strings.

So I suggest you to convert your varchar to date, i.e. instead of CAST(POSTDATE AS DATE) > CONVERT(VARCHAR(10), dateadd(day,2,getdate()),101)

you should use DATEFROMPARTS ( left(POSTDATE, 4), right(POSTDATE, 2), substring(POSTDATE,6,2)) > dateadd(day,2,cast(getdate() as date));.

DATEFROMPARTS function is available starting with SQL Server 2012, let me know if you are on the earlier version and I'll rewrite my code

Upvotes: 0

Dmitrij Kultasev
Dmitrij Kultasev

Reputation: 5745

Is the POSTDATE - date column? If no then you are comparing strings and the result is as expected as '2017-01-08' > '08/10/2017' ('2' > '0'). Most probably you just need to cast the POSTDATE. See the example:

select
case
when '2017-01-08'  > CONVERT(VARCHAR(10), dateadd(day,2,getdate()),101) THEN 1
ELSE 0
end without_cast,
case
when CAST('2017-01-08' AS DATE) > CONVERT(VARCHAR(10), dateadd(day,2,getdate()),101) THEN 1
ELSE 0
end with_cast

enter image description here

So what you need is:

  SELECT * 
FROM dbo.INVOICE i 
WHERE MVT_TYPE = '122' AND SALESREF IS NOT NULL AND CAST(POSTDATE AS DATE) > CONVERT(VARCHAR(10), dateadd(day,2,getdate()),101) 
AND NOT EXISTS (SELECT 1 
FROM dbo.INVOICE 
WHERE DEBIT = 'H' AND  MVT_TYPE = '000' AND SALESREF = i.SALESREF )

Upvotes: 0

Related Questions