[dba-SQLServer]International dates, dateformat, cast and convert

Just thought I share a SS2K tip of the day for today with everybody:

It is really important for text dates to be EXPLICITLY convert into datetime 
datatype when first concatenating dates in text (especially when working in 
international environment)...

See below:

/* -- cut here -- */
/* for example, display the first of next month */
SET NOCOUNT ON
SET DATEFORMAT YMD
-- returns 2003-04-01 (with no convert style)
SELECT CAST(CONVERT(VARCHAR(7), DATEADD(m,1,GETDATE()), 111) + '/01' AS 
DATETIME)
-- returns 2003-04-01 (with convert style)
SELECT CONVERT(DATETIME, CONVERT(VARCHAR(7), DATEADD(m,1,GETDATE()), 111) + 
'/01', 111)

SET DATEFORMAT YDM
-- returns 2003-01-04 (again with no convert style)
SELECT CAST(CONVERT(VARCHAR(7), DATEADD(m,1,GETDATE()), 111) + '/01' AS 
DATETIME)
-- returns 2003-04-01 (with convert style)
SELECT CONVERT(DATETIME, CONVERT(VARCHAR(7), DATEADD(m,1,GETDATE()), 111) + 
'/01', 111)
SET NOCOUNT OFF
/* -- cut here -- */

Billy

_________________________________________________________________
Protect your PC - get McAfee.com VirusScan Online  
http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963