none
TSQL converting from int to DateTime

    Question

  • Hi,
    I reviewed all the answers posted but nothing seems to point me in the right direction.  Below is what I am doing to get the integer value.

    Declare
    @LoginStartTime int,
    @loginDate DateTime

    Set
    @LoginDate = getdate()  --2009-03-04 08:25:16.913

    Set @LoginStartTime = CEILING(datepart(ms,@LoginDate)/1000) + datepart(s,@LoginDate)+datepart(mi,@LoginDate)*60+datepart(hh,@LoginDate)*3600 --30316


    Now I need to use the integer value produced and get the same date and time.

    Your assistance will be highly appreciated.

    Thanks,


    kashif
    Wednesday, March 04, 2009 1:26 PM

Answers

  • I agree with Aaron:  This doesn't seem like a good way of implementing something.  Still attempting to answer the original question, you might consider changing the datatype of your INTEGER variable to something else -- perhaps something like this is in the vicinity of what you seek:


    Declare @LoginStartTime numeric(9,3),
    @loginDate DateTime

    Set @LoginDate = getdate()  --2009-03-04 08:25:16.913

    Set @LoginStartTime = (cast(datepart(ms,@LoginDate) as numeric(7,3)) / 1000) + datepart(s,@LoginDate)+datepart(mi,@LoginDate)*60+datepart(hh,@LoginDate)*3600 --30316

    select
      @LoginDate as [@LoginDate],
      @loginStartTime as [@LoginStartTime],
        right('0' + cast(floor(@loginStartTime / 3600) as varchar(2)), 2) + ':'
          + right('0' + cast(floor((@loginStartTime % 3600) / 60) as varchar(2)), 2) + ':'
          + right('0' + cast(floor(@loginStartTime % 60 ) as varchar(2)), 2)
          + right(cast(@LoginStartTime % 1 as char(5)), 4)
      as not_sure

    /* -------- Sample Output: --------
    @LoginDate              @LoginStartTime                         not_sure
    ----------------------- --------------------------------------- ------------
    2009-03-04 10:05:04.733 36304.733                               10:05:04.733
    */



    • Marked as answer by Kpbutt Wednesday, March 04, 2009 4:33 PM
    Wednesday, March 04, 2009 2:17 PM

All replies

  • Are you sure your computation of the ms portion of this is correct?  What exactly is @loginStartTime supposed to look like?  Do you intend to drop the milliseconds?  I am not sure where to go from here and I am not sure what @loginStartTime is supposed to look like.  Here is what I have at the moment:


    Declare @LoginStartTime int,
    @loginDate DateTime

    Set @LoginDate = getdate()  --2009-03-04 08:25:16.913

    Set @LoginStartTime = CEILING(datepart(ms,@LoginDate)/1000) + datepart(s,@LoginDate)+datepart(mi,@LoginDate)*60+datepart(hh,@LoginDate)*3600 --30316

    --select
    --  @loginDate [@loginDate],
    --  @loginStartTime [@loginStartTime],
    --  CEILING(datepart(ms,@LoginDate)/1000) ms,
    --  datepart(s,@LoginDate) s,
    --  datepart(mi,@LoginDate)*60 mi,
    --  datepart(hh,@LoginDate)*3600 hh

    select
      @LoginDate as [@LoginDate],
      @loginStartTime as [@LoginStartTime],
      right('0' + cast(@loginStartTime/3600 as varchar(2)),2) + ':'
        + right('0' + cast((@loginStartTime % 3600) / 60 as varchar(2)), 2) + ':'
        + right('0' + cast(@loginStartTime % 60 as varchar(2)), 2)
      as not_sure

    /* -------- Sample Output: --------
    @LoginDate              @LoginStartTime not_sure
    ----------------------- --------------- --------
    2009-03-04 08:54:25.873 32065           08:54:25
    */





    Kent Waldrop Mr09
    Wednesday, March 04, 2009 1:48 PM
  • Why are you doing this?  There is probably a better solution, whatever your need may be.

    You need to add the current date (without hours, minutes, or seconds) to the number of seconds that you're calculating.  Because you're only calculating the seconds that have elapsed since the start of the day, that needs to be added to the current date.  Like this:

    Declare @LoginStartTime int, @loginDate DateTime, @ReincarnatedDate datetime  
     
    Set @LoginDate = getdate()  
     
    Set @LoginStartTime =   CEILING(datepart(ms,@LoginDate)/1000) +   
                            datepart(s,@LoginDate)+  
                            datepart(mi,@LoginDate)*60 +   
                            datepart(hh,@LoginDate)*3600  
                              
    SET @ReincarnatedDate = dateadd(s, @LoginStartTime , dateadd(dd, datediff(dd, 0, @LoginDate), 0))  
     
    SELECT @loginDate, @ReincarnatedDate 

    Does this help?
    Aaron Alton | thehobt.blogspot.com
    Wednesday, March 04, 2009 1:50 PM
  • I agree with Aaron:  This doesn't seem like a good way of implementing something.  Still attempting to answer the original question, you might consider changing the datatype of your INTEGER variable to something else -- perhaps something like this is in the vicinity of what you seek:


    Declare @LoginStartTime numeric(9,3),
    @loginDate DateTime

    Set @LoginDate = getdate()  --2009-03-04 08:25:16.913

    Set @LoginStartTime = (cast(datepart(ms,@LoginDate) as numeric(7,3)) / 1000) + datepart(s,@LoginDate)+datepart(mi,@LoginDate)*60+datepart(hh,@LoginDate)*3600 --30316

    select
      @LoginDate as [@LoginDate],
      @loginStartTime as [@LoginStartTime],
        right('0' + cast(floor(@loginStartTime / 3600) as varchar(2)), 2) + ':'
          + right('0' + cast(floor((@loginStartTime % 3600) / 60) as varchar(2)), 2) + ':'
          + right('0' + cast(floor(@loginStartTime % 60 ) as varchar(2)), 2)
          + right(cast(@LoginStartTime % 1 as char(5)), 4)
      as not_sure

    /* -------- Sample Output: --------
    @LoginDate              @LoginStartTime                         not_sure
    ----------------------- --------------------------------------- ------------
    2009-03-04 10:05:04.733 36304.733                               10:05:04.733
    */



    • Marked as answer by Kpbutt Wednesday, March 04, 2009 4:33 PM
    Wednesday, March 04, 2009 2:17 PM
  •  I suppose that @LoginStartTime below...

    Set @LoginStartTime =   CEILING(datepart(ms,@LoginDate)/1000) +      
                            datepart(s,@LoginDate)+     
                            datepart(mi,@LoginDate)*60 +      
                            datepart(hh,@LoginDate)*3600     
                                 
    SET @ReincarnatedDate = dateadd(s, @LoginStartTime , dateadd(dd, datediff(dd, 0, @LoginDate), 0))     
     

    ...could also be calculated by just finding the difference between @LoginDate and @LoginDate without the time... similar to what was done with @ReincarnatedDate (except backwards).

    Set @LoginStartTime = datediff(s, dateadd(dd, 0, datediff(dd, 0, @LoginDate)), @LoginDate) 
    Wednesday, March 04, 2009 3:01 PM
  • Thanks for your help.  Exactly what I wanted.  Also, I would like to thank Aaron as well.  I understand there could be a better solution to this, but I am accessing data already calculated by another process.  Thanks for helping me in reverse engineering.
    kashif
    Wednesday, March 04, 2009 4:33 PM