mS SQL query error

[harish_babu5]

Hi guys

I have an interface running successfully in SAP PO Production server.

The interface is from MS SQL to SAP ECC, asynchronous scenario

It was working fine so far. but the client says they missed data on the dates 28th and 29th. The interface is triggered every five minutes. The last time it was triggered was on 28th morning 7:16 AM and then it started polling again on 1st morning 4:31 AM.

This was the querry that we used there:

select ID, Date, Time, TransType, PIN, Address from

TO_SAP_PunchDetails where InsertionDateTime >= DATEADD

(HH,-24,getdate())

I replaced it with this query

select ID, Date, Time, TransType, PIN, Address from TO_SAP_PunchDetails where InsertionDateTime >= 2016-02-28 07:15:00.000 and InsertionDateTime <= 2016-02-28 19:15:00.000

and this is the error that I got: I have attached screen shot

What is the solution?

Harish

[vishnu_pallamreddy · Accepted solution]

Hi Babu,

Please try to execute the same query in SQL Server.

[harish_babu5 · Accepted solution]

I don't have access to SQL server. Can you check if the query is right or wrong?

Harish

[vishnu_pallamreddy · Accepted solution]

Ask your SQL Team to check the query. whether it is fetching the records or not?

I don't have SQL Server.

Your SQL team can help u...

[former_member441596 · Accepted solution]

Hi Babu,

You can download SQL developer tool and use this tool to connect sql server then execute your SQL query.below screen is helpful.

Thanks & Regards,

Naidu

[former_member186851 · Accepted solution]

Hello Harish,

Try the below query

select ID, Date, Time, TransType, PIN, Address from TO_SAP_PunchDetails where InsertionDateTime >= "2016-02-28 07:15:00.000" and InsertionDateTime <= "2016-02-28 19:15:00.000"

Refer the belwo link for constructing queries.

SQL SELECT Statement

[harish_babu5 · Accepted solution]

HI Raghu

It is working!

It didn't work with double quotes, but when I used single quote it worked!

This query worked:

select ID, Date, Time, TransType, PIN, Address from

TO_SAP_PunchDetails where InsertionDateTime >= '2016-02

-28 07:15:00.000' and InsertionDateTime <= '2016-02-28

19:15:00.000'

Regards

Harish

[former_member186851 · Accepted solution]

Ok Harish..It should work with double quotes as well. might be something with SQL version,anyways i am not sure on that.

Glad that it worked.

[iaki_vila]

Hi Harish,

AFAIK you can't set directly the date and you should use the convert function SQL Server CONVERT() Function

Check also:

SQL Server 2008 - Date Formats

http://stackoverflow.com/questions/5184196/convert-date-against-where-clause

Regards.