Hi,
I don't know the default sp/function for this types of work, but i create a function for this.
I create this for my customize date format. You need to create 2 function for this. See function below...
1st Function for Split data:
Create Function dbo.Split
(
@RowData NvarChar(2000),
@SplitOn NvarChar(5)
)
Returns @RtnValue Table
(
Id Int Identity(1,1),
Data NvarChar(100)
)
As
Begin
Declare @Cnt Int
Set @Cnt = 1
While (Charindex(@SplitOn,@RowData)>0)
Begin
Insert Into @RtnValue (data)
Select
Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))
Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))
Set @Cnt = @Cnt + 1
End
Insert Into @RtnValue (data)
Select Data = ltrim(rtrim(@RowData))
Return
End
2nd Function for Convert datetime to your given format:
Create Function ConvertToDateTime(@Date NVarChar(50), @Time NVarChar(50), @Format NVarChar(50)) Returns NVarChar(50)
As
Begin
Declare @RetDateTime NVarChar(50), @FullDateTime NVarChar(50), @M NVarChar(50), @MN NVarChar(50), @MName NVarChar(50), @D NVarChar(50), @WeekDay NVarChar(50), @WKD NVarChar(50), @Y NVarChar(50), @T NVarChar(50), @AP NVarChar(50), @TimeZone NVarChar(6)
If @Time='' Begin
Select @FullDateTime=Convert(NVarChar,Convert(DateTime,@Date))
End
Else Begin
Select @FullDateTime=Convert(NVarChar,Convert(DateTime,Convert(NVarChar(11),@Date,110) + ' ' + @Time))
End
Declare @TempTable
Table(
ID Int,
Data NVarChar(50)
)
Declare @ID Int, @Data NVarChar(50)
Declare @Cur Cursor
Set @Cur=Cursor Fast_Forward
For
Select Data From dbo.Split(@FullDateTime,' ') Where RTrim(LTrim(Data))<>''
Open @Cur
Fetch Next From @Cur
Into @Data
While @@Fetch_Status = 0
Begin
Set @ID=IsNull(@ID,0)+1
Insert Into @TempTable Values (@ID,@Data)
Fetch Next From @Cur
Into @Data
End
Close @Cur
Deallocate @Cur
Select @M=Data From @TempTable Where ID=1
Select @D=Data From @TempTable Where ID=2
Select @Y=Data From @TempTable Where ID=3
Select @T=Data From @TempTable Where ID=4
Select @MN=
Case
When @M='Jan' Then '01'
When @M='Feb' Then '02'
When @M='Mar' Then '03'
When @M='Apr' Then '04'
When @M='May' Then '05'
When @M='Jun' Then '06'
When @M='Jul' Then '07'
When @M='Aug' Then '08'
When @M='Sep' Then '09'
When @M='Oct' Then '10'
When @M='Nov' Then '11'
When @M='Dec' Then '12'
End
Select @MName=
Case
When @M='Jan' Then 'January'
When @M='Feb' Then 'February'
When @M='Mar' Then 'March'
When @M='Apr' Then 'April'
When @M='May' Then 'May'
When @M='Jun' Then 'June'
When @M='Jul' Then 'July'
When @M='Aug' Then 'August'
When @M='Sep' Then 'September'
When @M='Oct' Then 'October'
When @M='Nov' Then 'November'
When @M='Dec' Then 'December'
End
If @D<10 Begin Set @D='0'+@D End
Set @WeekDay=DateName(DW,@FullDateTime)
Set @WKD=Left(@WeekDay,3)
Set @AP=Right(@T,2)
Set @T=Left(@T,Len(@T)-2)
Select @RetDateTime=
Case
When @Format='DDDD, MMMM DD, YYYY' Then @WeekDay + ', ' + @MName + ' ' + @D +', ' + @Y
When @Format='hh:mm tt' Then @T + ' ' + @AP
When @Format='MM/DD/YYYY hh:mm tt' Then @MN + '/' + @D +'/' + @Y + ' ' + @T + ' ' + @AP
When @Format='DD/MM/YYYY hh:mm tt' Then @D + '/' + @MN +'/' + @Y + ' ' + @T + ' ' + @AP
When @Format='MMM DD' Then @M + ' ' + @D
When @Format='MMM DD hh:mm tt' Then @M + ' ' + @D + ' ' + @T + ' ' + @AP
When @Format='DDD, MM/DD/YYYY hh:mm tt' Then ' ' + @WKD + ', ' + @MN + '/' + @D +'/' + @Y + ' ' + @T + ' ' + @AP
When @Format='MM/DD/YYYY' Then @MN + '/' + @D +'/' + @Y
When @Format='DD/MM/YYYY' Then @D + '/' + @MN +'/' + @Y
End
Return @RetDateTime
End
You can change this function for your datetime formate...
Try this I think it's help you...
Also you can call this function like this...
Select dbo.ConvertToDateTime('04/29/2013', '04:25:34.432', 'MM/DD/YYYY hh:mm tt')
Select dbo.ConvertToDateTime('04/29/2013 04:25:34.432', '', 'DD/MM/YYYY hh:mm tt')
Select dbo.ConvertToDateTime('04/29/2013', '', 'MM/DD/YYYY')
Select dbo.ConvertToDateTime('04/29/2013', '', 'DD/MM/YYYY')