Hi,

Please help to convert date format

my table contains date as " 13FEB2015:08:54:45 " need to be change in datetime or date

hel

Well, contains is not verb we use here. Important is: As what data type is it stored? When it is not a DATE, TIME or DATETIME value, then the format matters. Cause it must be one of those supported by CONVERT(). Otherwise you need to do some string manipulation.

But in this case, when it is a VARCHAR() column, then this is sufficient:

DECLARE @Text VARCHAR(255) = '13FEB2015:08:54:45';

SELECT  CONVERT(DATE, LEFT(@Text, 9), 112);

Try the below query

DECLARE @Date VARCHAR(255) = '13FEB2015:08:54:45';
SELECT  CONVERT(DAtetime, LEFT(@Date, 9), 112)+CONVERT(Datetime,RIGHT(@Date, 8));

Hello GSKR: You can try this:

-- Declarations
DECLARE @strDate VARCHAR(255) = '13FEB2015:08:54:45';
SELECT CAST ( CONVERT(DATE, LEFT(@strDate, 9), 112) as varchar) + ' ' + 
CAST ( CONVERT(TIME, RIGHT(@strDate, 8), 112) as varchar)

-- OR You can also use the same query against ColumnName in Your Table such as:
SELECT CAST ( CONVERT(DATE, LEFT(YourColName, 9), 112) as varchar) + ' ' + 
CAST ( CONVERT(TIME, RIGHT(YourColName, 8), 112) as varchar) FROM YourTableName

Hope this helps !

GSKR,

What if the date is '1FEB2015:08:54:45'? All the proposed queries will result in errors converting strings to dates.

The solution is to find the position of ':' to convert.

So try this query (it will work for any date):

DECLARE @Text VARCHAR(255) = '1FEB2015:08:54:45';
--For dates
SELECT  CONVERT(DATE, LEFT(@Text, CHARINDEX(':',@text)-1), 112);

-- For dates and times
SELECT  CONVERT(DAtetime, LEFT(@Text, CHARINDEX(':',@Text)-1), 112)+CONVERT(Datetime,RIGHT(@Text, 8));

Hope it helps!

This is very good point Berimi (+1),

and there are other options as well like '01FEB2015:08:54:45',  '1FEB2015:08:54:45',  '1FEB15:08:54:45', and more :-)

There is another simple solution using SQLCLR. With CLR we can use Regular Expression (RE), and cover all these cases in simple query. Using RE there is a built-in function named REPLACE which replace pattern with Pattern. If this interesting someone I will post him the full C# code or the DLL ready. This solution might be much better in performance as well (need to be checked if & how much).

Using this solution we can use simple query like this:

DECLARE @Text VARCHAR(255) = '13FEB2015:08:54:45';
select CONVERT(DATETIME,dbo.Ariely_RegexReplace_fn(@Text,'(?<a>[0-9]+)(?<b>[A-Za-z]+)(?<c>[0-9]+)(?<d>.)','${a} ${b} ${c} '),13)
GO

Explanation in short:

I have 4 groups in the source pattern a,b,c,d. groups are marks with (group content). In the group content we can declare group name like this <group name here> and this is not pert of the pattern. Now, in the result pattern, we can use the groups name from the source, using the sign ${group name come here}.

a: get the pattern [0-9]+ which mean any number and it can be one char or more (cover the cases like 01 and 1, 2015 and 15)

same way b is English Letters one or more, and c is like a. d is just 1 char for the ":"

 

Nope, my query works on systems with German locale region settings ;)

• Edited by Stefan HoffmannMVP 11 hours 31 minutes ago

Update the column data as above guide line.

Using Convert /Cast also see the msdn reference. 

Nope, my query works on systems with German locale region settings ;)

• Edited by Stefan HoffmannMVP Friday, July 24, 2015 7:37 PM

Build a look-up table based on the front of this string to get  a DATE value. Apparently, you do not need the time fields.