CODEWITHSUNDEEP
sql-serveroracle-databaset-sqldate
Prerna
Prerna

Reputation: 111

In SQL Server how to have a paymentdate greater than the month and year ignoring the day part?

In Oracle:

select 
    name, 
    case 
       when PaymentDate is not null 
            and PaymentDate <= to_date('202001','yyyyMM')
          then 'Paid'  
          else NotPaid
where 
    empId  in(%s)) ;

I need the SQL Server equivalent of the above query. I just need to check the month and year part of the date with paymentdate's month and year. Please advice.

Need the to_date('202001','yyyyMM') equivalent in SQL Server.

Upvotes: 0

Views: 771

Answers (4)

rsreji
rsreji

Reputation: 180

select * from payment_table where month(payment_date) > 2 and year(payment_date) >= 2020;

Upvotes: 0

GMB
GMB

Reputation: 222632

In Oracle, to_date('202001','yyyyMM') produces a date value that represents January 1st, 2020.

You can get the same result in SQL Server with an expression such as cast('2020-01-01' as date) or cast('20200101' as date).

If your input ('202001') is coming from outside the query and you cannot modify its value, you can concatenate '01' at the end: cast(concat(@myparam, '01') as date).

The rest of your query has syntax errors:

  • missing quotes around 'Not Paid'
  • missing end keyword at the end of the case expression
  • missing from clause
  • superfluous closing parenthese at the end of the query

It is also worth noting that the condition in the case expression can be simplified by removing the superfluous is not null predicate:

select 
    name, 
    case when PaymentDate <= cast('2020-01-01' as date)
        then 'Paid'  
        else NotPaid
    end
from mytable
where empId in (%s);

Upvotes: 0

Sandesh Herwade
Sandesh Herwade

Reputation: 21

Oracle always return 1st day of the month when you convert to_date. So in this case it is 1st Jan 2020.

Generally SQL server have default date format at YYYY-MM-DD. If you want to match date as per SQL server then you need to manipulate the input string to as per date format

SELECT CAST((LEFT('202001',4) + '-' + RIGHT('202001',2) + '-01') as Date)

As per your query

select name, case when PaymentDate is not null and PaymentDate <= CAST((LEFT('202001',4) + '-' + RIGHT('202001',2) + '-01') as Date) then 'Paid'
else 'NotPaid' where empId in(%s)) ;

Upvotes: 0

scottsaenz
scottsaenz

Reputation: 90

Here's how to get the beginning of the month. You can apply it to both PaymentDate and Date, then do the comparison

SELECT DATEADD(month, DATEDIFF(month, 0, PaymentDate), 0) AS StartOfMonth

Upvotes: 0

Related Questions