locked
convert from DateTime format to Datetime format RRS feed

  • Question

  • Dear all,

    ther is any way to convert DateTime from format  "1250557173.245134"  the the LocalTime DateTime "8/20/2009 12:00:00".

    Thanks to all
    • Moved by Tom Phillips Thursday, August 20, 2009 1:51 PM TSQL Question (From:SQL Server Database Engine)
    Thursday, August 20, 2009 8:00 AM

Answers

  • Dear all,

    ther is any way to convert DateTime from format  "1250557173.245134"  the the LocalTime DateTime "8/20/2009 12:00:00".

    Thanks to all

    Yes we can. But we need a second reference point.
    • Marked as answer by alinour Saturday, August 22, 2009 8:53 AM
    Thursday, August 20, 2009 2:12 PM
  • I think it might be a "unix date", seconds after 1970-01-01:

    SELECT

     

    DATEADD(SECOND, 1250557173.245134, '19700101')

    So, one can use above and if a certain format is required, then squeeze above inside aproperiate CONVERT function (I prefer doing formatting inclient app, though). I still firmly believe that datetime values should be handles as ... datetime (or date, time, datetime2 whetever fits best), as per my earlier reply.


    Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi
    • Marked as answer by alinour Saturday, August 22, 2009 8:53 AM
    Thursday, August 20, 2009 2:19 PM
  • Less than days off only. That's why I need second reference point.

    SELECT

     

    DATEADD(SECOND, 1250557173, '19700101')

    • Marked as answer by alinour Saturday, August 22, 2009 8:54 AM
    Thursday, August 20, 2009 2:26 PM

All replies

  • HI

    use some thing like this.

    Select

     

    convert(decimal(10,4),getdate(),0)


    Shamas Saeed MCITP-DBD 2005 http://sqlservercoollinks.blogspot.com
    Thursday, August 20, 2009 9:16 AM
  • Datetime is not handled as a numeric values in TSQL. Can you explain the rules for converting that numeric values to datetime?


    Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi
    Thursday, August 20, 2009 10:03 AM
  • Datetime is stored in SQL internaly as Numbers - WHole part represent dates and fractional part - hours,min,sec,msec

    for example If you want to extract only the date from datetime
    you can use this - cast ( floor(cast(getdate() as real) ) as smalldatetime)
    Thursday, August 20, 2009 1:59 PM
  • I understand how datetime values are *internally* stored in SQL Server. After all, I wrote http://www.karaszi.com/SQLServer/info_datetime.asp once upon a time partly to *discourage* using the internal representation of datetime in TSQL code. That is why I in my reply stated "in TSQL" and not "in SQL Server". Furthermore, SQL Server 2008 introduced new date and time related types where the internal representation do not look like the one for the old datetime types - yet another example of why we shouldn't work with the internal representation.

    Also, if you check the values posted by alinour, you will find that the integer (date) part is too large to be an internal representation of date part for a datetime value. The internal "base date" (0) is 1900-01-01 and adding some 3,426,184 years to 1900 is way overflowing the datetime type. That is wjhy I asked for how this value is calculated - it is clearly not just a visualization of the interger and fractional parts for a datetime value.
    Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi
    Thursday, August 20, 2009 2:14 PM
  • I think it might be a "unix date", seconds after 1970-01-01:

    SELECT

     

    DATEADD(SECOND, 1250557173.245134, '19700101')

    So, one can use above and if a certain format is required, then squeeze above inside aproperiate CONVERT function (I prefer doing formatting inclient app, though). I still firmly believe that datetime values should be handles as ... datetime (or date, time, datetime2 whetever fits best), as per my earlier reply.


    Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi
    • Marked as answer by alinour Saturday, August 22, 2009 8:53 AM
    Thursday, August 20, 2009 2:19 PM
  • I think you meant to reply to my other post, Peso... :-)

    Yes, I now see that the "unix date" conversion doesn't match with the date/time poast in the OP(I didn't check against that, I just noticed it produced some reasonably current date and assumed).

    alinour: Are you certain that the date you posted is really right for the decimal value you posted? If so, we need to know more about the encoding...
    Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi
    Thursday, August 20, 2009 2:32 PM
  • Thanks for the article Tibor,
    I think I have been read it before -very nice article.
    This was the article that learn me about the 1753 year and that DATEADD is faster then String manipulation
    If I have a time i will test the FLOOR variant agains String manipulation too.

    Regards
    Ivan
    Thursday, August 20, 2009 3:24 PM
  • Thanks Ivan,

    In my hubmle opinion, performance is secondary to code stability. I do not advocate (as I mentioned before) having code dependeont in the internal represenatation; I prefer to use the documented way to interact with the type system. For instce, say you used the "integer" way to deal with datetime and now want to use date or time instead (SQL 2008):

    SELECT cast ( floor(cast(getdate() as real) ) as date)
    SELECT cast ( floor(cast(getdate() as real) ) as time)

    Both above return error messages.
    Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi
    Friday, August 21, 2009 6:16 PM
  • Thanks Tibor,

    I am still not migrated to SQL 2008

    will consider your opinion when we migrate our code.

     

    Ivan

    Saturday, August 22, 2009 7:30 AM
  • But why produce or recommend code that we now know *will* break in 2008 (if we use the any of the new date and time related types)?


    Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi
    Saturday, August 22, 2009 7:40 AM
  • Thanks Tibork,
    No Im not certain of the date.
    The date I post it is just an example of the conversion.

    Saturday, August 22, 2009 9:01 AM