DECLARE @d1 date, @d2 date, @d3 date

DECLARE @v1 varchar(10), @v2 varchar(10), @v3 varchar(10)

SET @v1 = '31/10/2011'
SET @v2 = '10/31/2011'
SET @v3 = '2011/10/31'


SELECT @v1, @v2, @v3, ISDATE(@v1), ISDATE(@v2), ISDATE(@v3)

IF ISDATE(@v1) = 0
	SELECT @d1 = CONVERT(date,SUBSTRING(@v1,4,2)+'/'+SUBSTRING(@v1,1,2)+'/'+SUBSTRING(@v1,7,4))
  ELSE
    SELECT @d1 = CONVERT(date,@v1)

IF ISDATE(@v2) = 0
	SET @d2 = CONVERT(date,SUBSTRING(@v2,4,2)+'/'+SUBSTRING(@v2,1,2)+'/'+SUBSTRING(@v2,7,4))
  ELSE
    SET @d2 = CONVERT(date,@v2)
    
IF ISDATE(@v3) = 0
	SET @d3 = CONVERT(date,SUBSTRING(@v3,4,2)+'/'+SUBSTRING(@v3,1,2)+'/'+SUBSTRING(@v3,7,4))
  ELSE
    SET @d3 = CONVERT(date,@v3)
    
SELECT @d1, @d2, @d3

quote:

---

Originally posted by X002548

I have a front end developer in Domino telling me they can't discern the type of date the are getting...so I proposed a solution.

Is there something better than this? Without changing the default settings?
<snip>

---

quote:

---

Originally posted by Kristen

"So...I'm going with the Bozo Sproc"

I know you know this, but in case you've overlooked:

Presumably, based on the person's-country-format, you could just "fire" a

SET DATEFORMAT dmy

and then everything downstream will be rosy ...

Or perhaps even a SET LANGUAGE for the session created in something that is man-in-the-middle? Get them to connect with ODBC perhaps and set it in the DSN?

---

CREATE usp_TheBozos_are_inTown
    @MyDate varchar(24)
AS
   @UserCountry = SomeDeterminationOfPersonsCountry(xxx)

   IF @UserCountry = 'GB'
       SET DATEFORMAT dmy

   IF @UserCountry = 'USA'
       SET DATEFORMAT mdy

   EXEC Original_NonBozo_SProc @MyDate

	SELECT XXX.AKA_ID, P.LAST_NAME, XXX.Expiration_DT AS varchar_Expiration_DT, D.DATE_FORMAT, D.PRIM_COUNTRY_CD
		, CASE WHEN DATE_FORMAT IS NULL OR DATE_FORMAT IN ('', ' ')
			 								  THEN CONVERT(date,DATEADD(dd,30,GetDate()))
			   WHEN DATE_FORMAT = 'dd/mm/yyyy' 
			    AND ISDATE(SUBSTRING(Expiration_DT,4,2)+'/'
					      +SUBSTRING(Expiration_DT,1,2)+'/'
					      +SUBSTRING(Expiration_DT,7,4))=1
											  THEN CONVERT(date,
												   (SUBSTRING(Expiration_DT,4,2)+'/'
												   +SUBSTRING(Expiration_DT,1,2)+'/'
												   +SUBSTRING(Expiration_DT,7,4)))
				WHEN ISDATE(Expiration_DT) = 1 THEN CONVERT(date,Expiration_DT)			   
											   ELSE CONVERT(date,DATEADD(dd,30,GetDate()))
		   END AS Expiration_DT
	  FROM (SELECT 'X002548' AS AKA_ID, '05/06/2011' AS Expiration_DT UNION ALL -- Good USA Date
			SELECT 'X124192' AS AKA_ID, '06/05/2011' AS Expiration_DT UNION ALL -- Good GBR Date
			SELECT 'X046984' AS AKA_ID, '2011/05/06' AS Expiration_DT UNION ALL -- Good JPN Date
			SELECT 'X169267' AS AKA_ID, '13/42/9999' AS Expiration_DT UNION ALL -- Bad Date
	        SELECT 'X123456' AS AKA_ID, '05/06/2011' AS Expiration_DT) AS XXX	-- No Country Code
 LEFT JOIN PERSON P
		ON XXX.AKA_ID = P.AKA_ID
 LEFT JOIN DATE_FORMATS D
		ON P.PRIM_COUNTRY_CD = D.PRIM_COUNTRY_CD

AKA_ID  LAST_NAME            varchar_Expiration_DT DATE_FORMAT PRIM_COUNTRY_CD Expiration_DT
------- -------------------- --------------------- ----------- --------------- -------------
X002548 KAISER               05/06/2011            mm/dd/yyyy  USA             2011-05-06
X124192 NELSON               06/05/2011            dd/mm/yyyy  GBR             2011-05-06
X046984 HUETZ                2011/05/06            yyyy/mm/dd  JPN             2011-05-06
X169267 MCLAUGHLIN           13/42/9999            mm/dd/yyyy  USA             2011-12-09
X123456 SCHOCH               05/06/2011            NULL        NULL            2011-12-09

(5 row(s) affected)

DECLARE	@strDateFormat	varchar(10)

SELECT	@strDateFormat = 'dmy'	-- i.e. set programatically

SET DATEFORMAT @strDateFormat

SELECT [UK Style]=ISDATE('31/12/2000'), [USA Style]=ISDATE('12/31/2000')