none
How do I convert a SQL datetime to seconds?

    Question

  • Hi all,

    How is a sql datetime stored internally? Is it the number of seconds from some baseline? I would like to create a column that contains the number of seconds from that baseline so I can do some subtraction with that number.

    Here's the problem:

    I need to find if my lab date is in a window of exactly 7 days (to the second) from hospital admission or 24 hrs (exactly in seconds) after hospital discharge. I am doing this in the following where clause:

    WHERE
      ( dbo.fn_diffsecond( DBO.VISTADATETODT(PTF.DATE), DBO.VISTADATETODT(LABS.DATE) ) >= 0 ) AND
      ( dbo.fn_diffsecond( DBO.VISTADATETODT(PTF.DATE), DBO.VISTADATETODT(LABS.DATE) ) <= 604800)

      AND
       ( dbo.fn_diffsecond( DBO.VISTADATETODT(LABS.DATE), DBO.VISTADATETODT(PTF.DDTE) ) >= 0) AND
       ( dbo.fn_diffsecond( DBO.VISTADATETODT(LABS.DATE), DBO.VISTADATETODT(PTF.DDTE) ) <= 86400 )

    The fn_diffsecond function was provided to me from forum member KH Tan. This function works fine but it takes over 17 hours to go through about 350M records. I also read that you shouldn't use functions in a WHERE clause. So I thought I could create new columns in my two tables that would store the original varchar date to a sql datetime value and another column as the number of seconds (and I don't know what the baseline is to be the number of seconds from that date). I could then use those columns to do my windowing calculation. Does this make sense? I am new at TSQL so I am grasping at straws to improve the efficiency of my query.

    Any suggestions you can provide would be very helpful.

    Thanks.

    Dave

    Friday, March 26, 2010 9:02 AM

Answers

  • Yes.

    1. create a new column with datetime data type. Update the new column when ever you update the LBS.DATE, PTF.DATE etc

    2. use the new date time column for the query

     

    WHERE LBS.DATE2 <= DATEADD(SECOND, -604800, PTF.DATE2)
    
    AND    LBS.DATE2 >   DATEADD(SECOND, 86400, PTF.DDTE2)
    try and see how's the performance
    KH Tan
    • Marked as answer by brewerdi Friday, March 26, 2010 5:15 PM
    Friday, March 26, 2010 9:55 AM

All replies

  • How is a sql datetime stored internally?

    see http://msdn.microsoft.com/en-us/library/ms187819%28SQL.90%29.aspx

     

    The main performance issue is because you are storing you datetime as a string in the table and you required the function DBO.VISTADATETODT() to convert it to datetime.

    Is it possible for you to change the data type to datetime ? If this is possible than you should use the other method that i suggested instead of then_diffsecond. Cause once you are applying a function to a column, SQL Server will not be able to utilize any indexes on the datetime column.

     


    KH Tan
    Friday, March 26, 2010 9:34 AM
  • Hi KH Tan,

    Thanks for getting back to me so quickly. Is this what you are suggesting I should use after I create new columns that hold the converted sql datetime values?

    "alternatively another way is not to find the difference in LABS.DATE and PTF.DATE in seconds but rather add the seconds to the date and compare it.

     WHERE DBO.VISTADATETODT(LABS.DATE) <= DATEADD( SECOND, -604800, DBO.VISTADATETODT(PTF.DATE) )

    AND    DBO.VISTADATETODT(PTF.DDTE) <= DATEADD( SECOND, -86400, DBO.VISTADATETODT(LABS.DATE) )  "

    I would change the function names to the new column names storing this data. Correct?

    Thanks again for your help.

    Dave

     

    Friday, March 26, 2010 9:43 AM
  • Yes.

    1. create a new column with datetime data type. Update the new column when ever you update the LBS.DATE, PTF.DATE etc

    2. use the new date time column for the query

     

    WHERE LBS.DATE2 <= DATEADD(SECOND, -604800, PTF.DATE2)
    
    AND    LBS.DATE2 >   DATEADD(SECOND, 86400, PTF.DDTE2)
    try and see how's the performance
    KH Tan
    • Marked as answer by brewerdi Friday, March 26, 2010 5:15 PM
    Friday, March 26, 2010 9:55 AM
  • If adding a new column , better to add it as a compueted column to calculate the seconds itself directly with PERSISTED property. So that it can be directly used in the where clause and no need for the DATEADD function tooo
    Friday, March 26, 2010 10:26 AM
  • KH Tan,

    Thanks for your help. Greatly appreciated. Do you know how to code Sorna's suggestion in case he doesn't get back to me?

     

    Sorna,

    Please provide an example of what you are suggesting.

    Thanks.

    Dave

    Friday, March 26, 2010 10:30 AM
  • what Sorna suggested is using computed column

    example

     

    alter table LBS
    add DATE2 as .VISTADATETODT(DATE) PERSISTED 

     



    KH Tan
    Friday, March 26, 2010 10:35 AM
  • I don't understand how this is any different than your suggestion. Isn't that how I create a new column with a datetime data type as you suggested above? I'm starting to get confused on your suggestion and Sorna's suggestion.

    Can you elaborate?

    Thanks again for your continued help.

    Dave

    Friday, March 26, 2010 10:41 AM
  • Thanks tan for providing the example.

    This will reduce the overhead of computing the seconds in the query each and every time. This computed column will calculate and store the data for each row which can be straight away used in the WHERE clause.

     

    Friday, March 26, 2010 10:43 AM
  • Sorna,

    Are you saying that I create two columns using the PERSISTED keyword and then use those columns in the following WHERE clause?

    WHERE LBS.DATE2 <= DATEADD(SECOND, -604800, PTF.DATE2)

    AND    LBS.DATE2 >   DATEADD(SECOND, 86400, PTF.DDTE2)

    If not, how would I change the WHERE clause?

    Please set this newbie straight.

    Thanks.

    Dave

    Friday, March 26, 2010 10:48 AM
  • the PERSISTED means SQL Server will physically store the value in the table and update the value when the other column value changed.

    The advantage of computed column vs creating a new normal column is with the new column you have to manually update it whenever you update the record. (it can also be auto update via trigger). With computed column, you don't need to manually updated the new column.

    So with either method (Computed column or New Column) you can use the following query. Assuming the DATE2 is the computed column or new column

     

    WHERE LBS.DATE2 <= DATEADD (SECOND , -604800, PTF.DATE2)

    AND     LBS.DATE2 >   DATEADD (SECOND , 86400, PTF.DDTE2)

     


    KH Tan
    Friday, March 26, 2010 11:26 AM
  • Hi KH Tan,

    While trying to alter my table, I get the following error: "cannot be persisted because the column is non-deterministic."

    Here is my ALTER statement:

    alter

     

    table DEB_LABS_DATE30907_SITE539

     

    add DATE2 as dbo.VISTADATETODT(DATE) PERSISTED

    The VISTADATETODT function (which I did not write):

    USE

     

    [Extracts]

    GO

    /****** Object: UserDefinedFunction [dbo].[VistaDateToDT] Script Date: 03/26/2010 08:18:09 ******/

    SET

     

    ANSI_NULLS ON

    GO

    SET

     

    QUOTED_IDENTIFIER ON

    GO

    /****** Object: UserDefinedFunction [dbo].[ConvertVistaDate] Script Date: 02/12/2010 15:22:52 ******/

    ALTER

     

    FUNCTION [dbo].[VistaDateToDT](@mdate [varchar](40))

    RETURNS

     

    datetime

    AS

     

    BEGIN

     

    /* -----------------------------------------------------------------

    * Function VistaDateToDT

    * Purpose: Converts a Vista datetime string to a SQL datetime variable

    * Vista Fileman datetime string looks like this:

    * 3100101.211801xxx

    * 3 = millenium, 10 = 2 digit year, 01=month, 01=day

    * 21 = hour, 18 = minutes, 01 = seconds, xxx= decimal seconds

    * But, time may not be included and instead of attaching insignificant zeros

    * Mumps just leaves blanks. We have to pad with zeros to convert time

    * to Tsql's datetime format.

    * NOTE: We are dropping decimal seconds!

    * By Eric Wagner and Evan Nelson

    * Created Feb 2010

    * ----------------------------------------------------------------- */

     

    -- Quick initial date check

     

    if @mdate is null return null -- Returns null if passed a null

     

    set @mdate = RTRIM(LTRIM(@mdate)) -- Trim leading and trailing blanks

     

    if @mdate = '' return null -- return null if date is now empty

     

    declare @returnvalue datetime

     

    declare @time varchar(6)

     

    declare @year varchar(5)

     

    declare @month varchar(2)

     

    declare @day varchar(2)

     

    declare @dotlocation integer

     

    declare @dateportion varchar(7)

     

    declare @timeportion varchar(20)

     

     

    --Split date and time values, if time is not set assume midnight (00:00:00)

     

    set @dotlocation = charindex('.', @mdate) --location of the decimal point in the time string

     

    if @dotlocation = 0 begin

     

    set @timeportion = '000000' -- if there is no decimal point then set time string to all zeros (time will = midnight)

     

    set @dateportion = @mdate

     

    end

     

    else begin

     

    set @dateportion = substring(@mdate, 1, @dotlocation - 1)

     

    set @timeportion = substring(@mdate, @dotlocation + 1,6)

     

    end

     

     

    --If date format is not valid return a null

     

    if (LEN(@dateportion) <> 7) OR (isnumeric(@dateportion) <> 1) return null --return null if year can't be converted to a number (Note: some sites were returning "Missing" as a date)

     

     

    --Separate date into day, month, and year

     

    set @year = SUBSTRING(@dateportion,1, 3)

     

    set @year = cast((cast(@year as integer) + 1700) as varchar(5)) --Fileman conversion to year is to add 1700 to millenium/year value

     

    set @month = substring(@dateportion,4,2)

     

    set @day = substring(@dateportion,6,2)

     

     

    --Pad the time portion with zeros

     

    set @time = @timeportion + replicate('0', 6 - LEN(@timeportion))

     

     

    --Compile the return value

     

    set @mdate = @month + '-' + @day + '-' + @year + ' ' + substring(@time, 1, 2) + ':' + substring(@time, 3, 2) + ':' + substring(@time, 5, 2)

     

     

    --Check that return value is actually a valid date before attempting cast

     

    if isdate(@mdate) = 1 set @returnvalue = cast(@mdate as datetime)

     

    else set @returnvalue = null

     

     

    return @returnvalue

     

     

    END

    GO

     What can I do to get rid of this error?

    Dave

    Friday, March 26, 2010 12:21 PM
  • looks like one of the function used inVistaDateToDT is non-deterministic.

    Should be the cast()

    see http://msdn.microsoft.com/en-us/library/ms178091.aspx


    KH Tan
    Friday, March 26, 2010 1:21 PM
  • KH Tan,

    Thanks for your solution. I am running a couple of small tests now and everything looks good. I am going to run a full blown query next week, so keep your fingers crossed :=}

    I didn't need to create a peristed column, just a new datetime column as you suggested. I also had to adjust your solution because I needed all data between the two windows.

    Thanks again to all for your suggestions and guidance.

    Dave

    Friday, March 26, 2010 5:18 PM