T-SQL String2Date function


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 🙂

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s