none
Create a TO_DATE function for use in SQLServer 2005

    Question

  • Hi ,

    I 'm working with visual studio 2005 and I have created an SQLServer Project.
    I'm using the CLR functionality which comes with SQLserver 2005. This means that I can write VB.nEt code and use it inside Sqlserver 2005.So far so good.
    I am now inside the .NET
    I have created a Function(must remind you that I have created an SQLserver Project) which takes two string arguments. The date value in a string format and the string format.

    In Our case the function returns a string.It will return a datetime although.
    So we have

    Dim Datetime_Val As DateTime = Nothing
    Dim Date_Val As Date = Nothing
    Dim StrTemp As String = ""
    Dim StrDateTemp As String = Nothing
    Dim StrTimeTemp As String = Nothing
    Dim ls_return As String = Nothing
    Dim lindexof As Integer
    Dim Counter As Integer = 0

    lindexof = 0
    Select Case StrFormat
    Case "DD-MM-YYYY HH24:MITongue TiedS"
    For Counter = 1 To 2
    lindexof = StrDate.IndexOf("-", lindexof + 1)
    Next
    lindexof += 5

    StrDateTemp = StrDate.Substring(0, lindexof).Trim
    StrTimeTemp = StrDate.Substring(StrDateTemp.Length, StrDate.Length - StrDateTemp.Length).Trim
    ls_return = StrDateTemp & " " & StrTimeTemp

    End Select

    The above is a simple code. As you can see I'm trying to convert the TO_DATE function ,which work with ORACLE, to make it work with SQLServer 2005.
    I've been trying unsuccessfully to combine the variables StrDateTemp and StrTimeTemp into a datetime value. I used the following code but nothing

    Datetime_Val = CDate(StrDateTemp & " " & StrTimeTemp)
    Didn't work

    Datetime_Val = Convert.ToDateTime(StrDateTemp & " " & StrTimeTemp)
    Didn't work

    Datetime_Val = DateTime.Parse(StrDateTemp & " " & StrTimeTemp)
    Didn't work

    Inside SQlServer I used this SQL statement

    Select dbo.TO_DATE('31-12-1990 00:26:46','DD-MM-YYYY HH24:MITongue TiedS')

    But I am receiveing an error. I want to avoid changing all of my applications with a specific format.This sql statement without the dbo prefix I'm using in Oracle. I want to keep the format of the SQL and let VB.NET do the parsing for me. It is easier for me to put in my SQLs the dbo infront rather changing the complete SQL.
    I have two questions . How am I going to create a TO_DATE function which Oracle uses and write something similar in SQLserver ?
    And If I cannot do that how am I going to get the database 's datetime format and create with VB.NET the Datetime value from the two variables ?

    My problem I believe is quite complex. I would be mostly appreciated if you could help me on this.

    Thank you
    Thursday, February 28, 2008 6:24 AM

Answers

  •  

    Hello

     

    You can do it with Bultin functionality in SQL in just in any query.

     

    Select Cast('Jan 12 2008 12:20:54:190' as DateTime)

     

    Or if you still want to do it using VB.NET then you can use DateTime.Pars method which will return you the data and you can return that from the CLR based UDF

    Thursday, February 28, 2008 6:36 AM
  • Yes

     

    You can pass the formater object to the parse method.

     

    Thursday, February 28, 2008 7:39 AM
  • Yes

     

    You can pass the formater object to the parse method.

     

    Or you can use

    SELECT CONVERT(datetime, ' 7/05/1421 12:14:35:727PM', 131)

    this will convert the above arabic date to Georgian date

    Thursday, February 28, 2008 7:43 AM

All replies

  •  

    Hello

     

    You can do it with Bultin functionality in SQL in just in any query.

     

    Select Cast('Jan 12 2008 12:20:54:190' as DateTime)

     

    Or if you still want to do it using VB.NET then you can use DateTime.Pars method which will return you the data and you can return that from the CLR based UDF

    Thursday, February 28, 2008 6:36 AM
  • Hi  and thank you for your reply.

    I don't want to change all of my querries that is why I want something generic.

    I found an example for the Datetime.Parse method you suggested me in MSDN. I will give it a try. It troubles me although about the culture. The example is for En-US. This mean that I will have problem if the local settings are different ???

    Can it be more generic?

     

    Thank you

     

    Thursday, February 28, 2008 7:29 AM
  • Yes

     

    You can pass the formater object to the parse method.

     

    Thursday, February 28, 2008 7:39 AM
  • Yes

     

    You can pass the formater object to the parse method.

     

    Or you can use

    SELECT CONVERT(datetime, ' 7/05/1421 12:14:35:727PM', 131)

    this will convert the above arabic date to Georgian date

    Thursday, February 28, 2008 7:43 AM
  • I must do now my investigation and try the parse method. I'll revert.

    Thank you very much

    Thursday, February 28, 2008 7:46 AM