Date in DataFeed is 2days prior to Date in SQL Database
Oh my do I have a curious situation...
So I have a Database source in my datafeed, connecting to a SQL database and doing a very simple "select" which includes a Date in the result.
When I check the "Configure Columns" in my Data Feed, the Date field is correctly identified as type "Date" - so no problems there.
If I however on that same "Source: Database" object I click on the "Calculate" button to show me the interim result set, all the dates have shifted back 2 days. E.g. 2015-11-05 from my SQL Database is now shown as 2015-11-03 in my datafeed result.
To clarify, this result within my datafeed has had no other inputs/manipulations etc - it is the first component in my datafeed.
I've seen somewhere that someone tried within their SQL statement to do first convert the sql date to a varchar with a specific type (111) and the to cast this back to a date. The T-SQL code is as follows:
cast(convert(nvarchar(25), CreateDate, 111) as Date)
While it defies all logic to me, I've since tried it, to no avail.
If it is of any use, the date in my SQL table is of type Date, and not of type DateTime - thus there is no Time in my date in theSQL database, however in my datafeed result set it shows the dates as 2015-11-03T00:00:00 - I don;t see this as a problem, but thought perhaps to mention it.
I've considered an issue with TimeZones, but the SQL Server and MashZone are both on my own box, so there should be no settings. Unless perhaps there are some "Regional Settings" within Windows I should be playing with?
The last thing I can think of is perhaps the SQL Collation - admittedly this is out of my league a bit, but perhaps there is someone who can point me in the right direction?
As usual, any and all help available :)