troubleshooting Question

How can I create a query or SP to correct SQL Date format issue

Avatar of rhill52
rhill52 asked on
Microsoft SQL Server 2005Microsoft SQL Server 2008
5 Comments5 Solutions302 ViewsLast Modified:
Hi I have some dates stored as Varchar within an SQL database, because of code issues in the application it is not possible to change the SQL fields to DateTime, I can report on the data by date if I Convert the string to 103 UK format, but I have many users who are US based is there anyway to check the local/format of the fields then have sql return the correct data by either a view or SP  as I do not really want to have seperate reports due to Date issues?

US systems report the error attached.

SELECT
  Table1.AD_Vendor,
  Table1.AD_Owner,
  Table1.AD_ContractPhone,
  Table1.AD_AgreementNumber,
  Table1.AD_ContractNumber,
  Table1.AD_ContractStartDate,
  Table1.AD_ContractEndDate
   FROM
  Table1
WHERE
  CONVERT(datetime, AD_ContractEndDate, 103) < DATEADD(MONTH, 3, GETDATE())
Capture.JPG
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 5 Answers and 5 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 5 Answers and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros