It's been a year since I asked this question and I still have this problem. I must be the only person experiencing this issue. Please tell me what is going on. Below are the steps I've taken to reproduce this issue.
Using an "Execute SQL" action I run the following query to create a date value
When I "Run Now" and "Execute" it returns the expected result
I create the following variable to store this data
I store in the variable the returned value from "Execute SQL" action
And I log the output
I add a "Create item" action and configure it to store this value as a basic list item
Publishing and running the workflow creates a new list item
...with the wrong date
But the in log history variable is correct
The thread seems to have a resolution of not using UTC during data calculations. My issue feels different ( perhaps UTC is the culprit, but... ) I'm not performing date calculations so date format is not an option, I'm simply always getting the wrong date as demonstrated above. More importantly - so are any of my users who create workflows - then they abandon the workflows because the dates can't be trusted. This is a HUGE issue and seems like an enormous bug. I've got payroll and expense workflows relying on the accuracy of dates! please help!
as I understand it, the other thread says that if calculate date action is added into workflow in between actions that populate date variable and one that writes is to list item field, it works as a workaround.
so in your scenario I would try to add date calculation action between SQL request action and create item action and see whether it helps or not.
it sounds to me that date variable populated by SQL request or query list (or maybe some other) actions is not internally populated very correctly and if immediately used to be written to list field it gets shifted. once passed through calculate date action, internal structure gets corrected. however, that just my guess.
This must be due to the fact that your date variable has no indication as to which time zone it belongs to. Midnight in the default time zone may be a few hours back in the previous day in the SharePoint site's time zone, so try changing the field to show both date and time and you'll most likely see the offset.