Afficher un message
Vieux 25/03/2008, 15h52   #2
Plamen Ratchev
Aucun Avatar
 
Messages: n/a
Hébergeur:
Par défaut Re: needed for converting varchar value in to Datetime

The problem is that you have some strings that are invalid British format
dates. NULLs are not a problem. See this example:

SELECT CONVERT(DATETIME, x, 103) AS mydate
FROM (SELECT '20/01/2008'
UNION ALL
SELECT '22/01/2008'
UNION ALL
SELECT NULL
UNION ALL
SELECT NULL) AS T(x)
ORDER BY mydate

You can use the ISDATE function to check if the string is valid date format
(but your language settings have to match the date format in the strings):

SET LANGUAGE british

SELECT CASE WHEN ISDATE(x) = 1
THEN
CONVERT(DATETIME, x, 103)
END AS mydate
FROM (SELECT '20/01/2008'
UNION ALL
SELECT '22/01/2008'
UNION ALL
SELECT NULL
UNION ALL
SELECT '2a/01/AAAA' -- Invalid date string
UNION ALL
SELECT NULL) AS T(x)
ORDER BY mydate

HTH,

Plamen Ratchev
http://www.SQLStudio.com

  Réponse avec citation
 
Page generated in 0,05358 seconds with 9 queries