Met a German customer, their time format is JJJJ-TT-DD HH:MM:SS, the program in accordance with this format to insert time into the database, but the SQL comes with the conversion function convert, the cast process error, the online search has said with convert, Cast can be converted directly, but this customer's machine is not, there is no way to write a conversion function for everyone to refer to:
Because the GetDate method cannot be used directly inside a custom function, a small function that gets local time is created first:
Create view v_getdate as select getdate() as Now_ Date
Then the conversion function:
1 CREATE FUNCTIONToDateTime (@DateStr varchar( -))2 RETURNS datetime 3 as 4 BEGIN 5 Declare @OutTime datetime6 Select @OutTime=Now_date fromv_getdate7 if(Len(@DateStr)< -)8 Set @DateStr=substring(@DateStr+'00:00:00',1, -)9 Set @OutTime=DateAdd( Year,0- Year(@OutTime)+cast(substring(@DateStr,1,4) as int),@OutTime)Ten Set @OutTime=DateAdd(Month,0-Month(@OutTime)+cast(substring(@DateStr,6,2) as int),@OutTime) One Set @OutTime=DateAdd( Day,0- Day(@OutTime)+cast(substring(@DateStr,9,2) as int),@OutTime) A Set @OutTime=DateAdd(Hour,0-DATEPART(HH,@OutTime)+cast(substring(@DateStr, A,2) as int),@OutTime) - Set @OutTime=DateAdd(Minute,0-DATEPARTN@OutTime)+cast(substring(@DateStr, the,2) as int),@OutTime) - Set @OutTime=DateAdd(Second,0-DATEPARTS@OutTime)+cast(substring(@DateStr, +,2) as int),@OutTime) the return @OutTime - END
Invocation Example: SELECT dbo. ToDateTime (' 2014-04-05 06:07:08 ')