Date format got changed b/w two different version of SQL Server for the same web request

Question

Post reply

Date format got changed b/w two different version of SQL Server for the same web request

windows_mss

SSCrazy

Points: 2426
More actions
September 18, 2015 at 8:11 am

#303192

Hi,
I'm facing some strange issue in our two development environment, we are having the same code base in both the environment but we have different version of SQL Server Version(Env 1 - SQL 2012, Env 2 - SQL 2008). We are getting the expected result in Env - 1 but not in the ENV -2. So we tried to get the SQL trace for the web request we found that Date format getting changed.
2012 - Date Format in the SQL Trace for the request '2015-09-10 00:00:00' which is working as expected.
2008 - Date Format in the SQL Trace for the request 'Sep 10 2015 12:00:00:000AM' which is Not working.
Complete SQL Trace for Request.
Evn - 1 - SQL Server 2012
exec dbo.usp_GetWorkQueue @ClientName='CLT',@HasFilter=1,@FacilityName='',@StartDate='2015-09-10 00:00:00',@EndDate='2015-09-18 00:00:00',@OrderDirection='D',@StartIndex=1,@PageSize=20
Env - 2 - SQL Server 2008
exec dbo.usp_GetWorkQueue @ClientName='CLT',@HasFilter=1,@FacilityName='',@StartDate='Sep 10 2015 12:00:00:000AM',@EndDate='Sep 18 2015 12:00:00:000AM',@OrderDirection='D',@StartIndex=1,@PageSize=20
Please guide me on this, Thanks.
___
Known Is An Drop,Unknown Is An Ocean....
Njoy Programming 🙂

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 1

This sounds like a Windows issue. SQL Server doesn't have a set format for datetimes, that I know of.

Check the Windows settings on each server for date and time format.

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Lowell SSC Guru Points: 323518 More actions · Answer 2

if the parameters are datetime data types, it should not make any difference, right?

the values are valid, you are not getting errors, you just happen to see the difference in the trace?

or is there an error downstream based on the parameters?

can we see the definition of usp_GetWorkQueue?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

windows_mss SSCrazy Points: 2426 More actions · Answer 3

Thanks a lot Alvin Ramard & Lowell for your kind guidance, find the root cause issue it's because of one environment saving the date in UTC timing due to that it's added +4 hours from the current time due to that record is not coming in given time frame.

___
Known Is An Drop,Unknown Is An Ocean....
Njoy Programming 🙂