I was cleaning up some of the date output from selects in SQL Query nodes this afternoon, removing the time from datetime fields using CONVERT(DATE,DATEVALUE) and noticed that when I ran the LPA, two days were removed along with the time. I checked a few different date fields and they were two days short of the date value stored in the database. I set up an isolated test, creating a LPA flow with a single SQL Query node with the following select statement:
SELECT CONVERT(date,GETDATE()), GETDATE(), cast(GETDATE() as DATE)
Here was the output:
Activity started: TEST (Run Id: 5) TEST_1 = 2016-09-20 TEST_ = 2016-09-20 TEST_2 = 2016-09-22 16:50:45.387 TEST_ = 2016-09-22 16:50:45.387 TEST_3 = 2016-09-20 TEST_ = 2016-09-20 TEST_RESULTSET_NUMBER = 1
If I take that same select statement and run it in SQL Server Management Studio, I get:
2016-09-22,2016-09-22 16:58:02.100,2016-09-22
I have no idea what might be causing this. I can do some varchar formatting with the convert/case statements to format the dates they way I need, but I thought this was just completely bizarre. I forwarded my test LPA to a co-worker and she got the same results. We are on Landmark 10.1.1.43.