I have a private project going on where I’m in the data cleaning and import phase. This weekend’s problem has been a lot of date columns stored as string on 2 different formats (yyyymmdd and ddmmyyyy) and different seperator characters (‘-‘ and ‘.’) all over the place. And these strings I want in date datatype. I’m a lazy programmer and had to make string2date function to use for my import stuff. Here is my first version of the function.
CREATE FUNCTION [dbo].[String2Date] ( @string NVARCHAR(100), @style smallint = 104 ) RETURNS date AS BEGIN IF (CHARINDEX('-', @string, 1) = 5 OR CHARINDEX('.', @string, 1) = 5) BEGIN SET @style = 102 ; END; RETURN CONVERT(date, @string, @style) ; END
This function is not perfect, but suits my purpose for the moment. It would probably give some exceptions now and then, but I don’t care 🙂