none
Convert Long to Datetime

    Question

  • Hi.

    I need to convert a vb function into a user-defined function in a stored procedure.
    The vb function converts a long datatype into Date datatype.

    Here is the VB function:

    Function LongToTime(lTime As Long) As Date
        LongToTime = (lTime \ 10000) / 24 + ((lTime Mod 10000) \ 100) / 1440 + (lTime Mod 100) / 86400
    End Function

    The function is used to convert a timestamp(hhmmss) into a more readable format.
    Would it be possible to create a function similar to this in SQL?

    Thanks
    Wes
    Friday, July 21, 2006 8:05 AM

Answers

  • declare    @lTime as int

    select    @lTime = 153459

    select convert(datetime, stuff(stuff(convert(varchar(6), @lTime), 3, 0, ':'), 6, 0, ':'))

    result : 1900-01-01 15:34:59.000

    Sunday, July 23, 2006 7:36 AM

All replies

  • hi

    there is no mod function in sql server

    you have to use %

    i think you can go ahead from here

    regards

     

    % (Modulo)

    Provides the remainder of one number divided by another.

    Syntax

    dividend % divisor

    Arguments

    dividend

    Is the numeric expression to divide. dividend must be any valid Microsoft® SQL Server™ expression of the integer data type category. (A modulo is the integer that remains after two integers are divided.)

    divisor

    Is the numeric expression to divide the dividend by. divisor must be any valid SQL Server expression of any of the data types of the integer data type category.

    Result Types

    int

    Remarks

    The modulo arithmetic operator can be used in the select list of the SELECT statement with any combination of column names, numeric constants, or any valid expression of the integer data type category.

    Examples

    This example returns the book title number and any modulo (remainder) of dividing the price (converted to an integer value) of each book into the total yearly sales (ytd_sales * price).

    USE pubs
    GO
    SELECT title_id, 
       CAST((ytd_sales * price) AS int) % CAST(price AS int) AS Modulo
    FROM titles
    WHERE price IS NOT NULL and type = 'trad_cook'
    ORDER BY title_id
    GO
    
    Friday, July 21, 2006 3:22 PM
  • declare    @lTime as int

    select    @lTime = 153459

    select convert(datetime, stuff(stuff(convert(varchar(6), @lTime), 3, 0, ':'), 6, 0, ':'))

    result : 1900-01-01 15:34:59.000

    Sunday, July 23, 2006 7:36 AM