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

rhill52
rhill52 used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Your problem is that the strings values that you have in the AD_ContractEndDate column is in a format that is not recognized by either 103 code(you have dates in different formats) or it is not a valid date value at all.

If the dates are valid but in different formats then you should directly compare teh column regardless of the format in the column.

...
WHERE
  AD_ContractEndDate < DATEADD(MONTH, 3, GETDATE())

 If that doesn't work you have to find out which dates are in an unrecognizable format by using ISDATE() function:

SELECT AD_ContractEndDate FROM table WHERE ISDATE(AD_ContractEndDate )<>1

If the query above returns any row you will have to correct the date to a valid format. The problem is if you will still get dates in bad formats then you have to control it in your application.
Try
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 Year(AD_ContractEndDate) = Year(DATEADD(MONTH, 3, GETDATE()))       
AND Month(AD_ContractEndDate) = Month(DATEADD(MONTH, 3, GETDATE()))       
AND Day(AD_ContractEndDate) = Day(DATEADD(MONTH, 3, GETDATE())) 

Open in new window

disregard that, I didn't read the full question
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014
Commented:
>> 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? <<

Yes.

Easiest to me would be to use a computed column, with how it's converted based on the local date format.

For example, something like this:

ALTER TABLE table1 ADD
    AD_ContractStartDate_Datetime  AS  CONVERT(datetime, AD_ContractStartDate,
        CASE @@LANGUAGE WHEN 'us_english' THEN 101 ELSE 103 END), --PERSISTED,
    AD_ContractEndDate_Datetime  AS  CONVERT(datetime, AD_ContractStartDate,
        CASE @@LANGUAGE WHEN 'us_english' THEN 101 ELSE 103 END) --PERSISTED

Optionally, you can add PERSISTED if you want SQL to physically store the column; otherwise, when it is referenced in a query, SQL materializes it at run time.
Senior DBA
Most Valuable Expert 2018
Top Expert 2014
Commented:
Naturally you could add additional WHENs to check for other languages / date formats (I don't know the exact values except for the U.S. :-); use SELECT @@LANGUAGE on a UK server to see what its value is).

PERSISTED also allows you to index the column, of course.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial