Any Idea for convert Varchar into Datetime in a Function

[Edimator]

I have a varchar column with data like '20020304113200' with the format YYYYmmDDhhMMss in 24(h), and i need convert this data to datetime value.

I really appreciate any kind of help.

Sorry about my bad english, my natural language is spanish.

 

[jhall156]

I would say you could use convert(datetime,left(yourval,4)+'-'+substring(yourval,5,2)+'-' etc. ,20) to force your varchar to look like a convertable string and then convert it, but just out of curiosity, how are you getting this varchar data? JHall

 

[Edimator]

Thanks jhall156, i create this function and works fine.

ALTER FUNCTION VarcharToDate
(@date varchar(20))
RETURNS datetime
AS
BEGIN
declare @cad varchar(20)

set @cad = substring(@date,1,4)+'/'+substring(@date,5,2)+'/'+substring(@date,7,2)+' '+substring(@date,9,2)+':' +substring(@date,11,2)+':'+substring(@date,13,2)

return convert(datetime,@cad,120)
END


i really apreciate if any body know another better way.

 

[tlbroadbent]

There are many ways to write such a function. I present two.

declare @strdate varchar(20)
set @strdate='20020304113200'

-- Function 1
Select DateTime1=
convert(datetime,left(@strdate,8),112) +
convert(datetime,stuff(stuff(right(@strdate,6),5,0,':'),3,0,':'),108)

-- Function 2
set @strdate=stuff(stuff(@strdate,13,0,':'),11,0,':')
set @strdate=stuff(stuff(stuff(@strdate,9,0,' '),7,0,'/'),5,0,'/')

select DateTime2=convert(datetime,@strdate)

I've created a page explaining "Date Handling in SQL Server." I willl post it as a FAQ in this forum when the FAQ submision process is fixed. In the meantime you canview it at...

http://tlbroadbent.home.attbi.com/sql_server_datetime.htm

Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions in the SQL Server forum. Many of the ideas apply to all forums.

 

[Edimator]

Thanks a lot for your response, its was very useful.

What do i have to make to convert the datetime back to varchar in the same format YYYYmmDDhhMMss.

jhall156 - This data is the rawdata generated by GSM Switch (its the mainframe on a cellular telephony system). Is grab in a binary file and bulk inserted in the database.

 

[tlbroadbent]

Two methods for converting datetime to string of format yyyymmddhhmmss.

Select MyDateString=
Convert(char(8), getdate(), 112) +
Replace(Convert(varchar(8), GetDate(), 8), ':', '')
go

Select MyDateString=
Replace(Replace(Replace(Convert(varchar(20), GetDate(), 120),':', ''),'-',''), ' ', '')
go Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions in the SQL Server forum. Many of the ideas apply to all forums.

 

[Edimator]

Thanks a lot, the second method works great!