Post reply

logic needed for date

  • February 27, 2008 at 9:51 am

    #187148

    select getdate() as todaydate, cast(getdate() as varchar) "diff format" ,cast(getdate()+1 as int) as intformat--- as convert

    todaydate diff format intformat

    ----------------------- ------------------------------ -----------

    2008-02-27 11:35:37.750 Feb 27 2008 11:35AM 39504

    (1 row(s) affected)

    select cast (39504 as datetime) as abc ,getdate() todaydate

    abc todaydate

    ----------------------- -----------------------

    2008-02-28 00:00:00.000 2008-02-27 11:50:28.123

    (1 row(s) affected)

    so i coudlnt understand the logic for the int converting to data and the date to 5 digits int plz let me know the logic over there....plz thkz in advance

    Thanks,
    Chinna

    Its the Journey which gives you Happiness not the Destination-- Dan Millman

  • February 27, 2008 at 9:57 am

    #782658

    when converting a number to datetime, the date portion is left of the decimal place, and the time portion is to the right of the decimal point.

    I think it really is stored as a decimal, and formatted for display based on settings or convert() applied to it.

    the definition is the number of days since 01/01/1900....approximately 365 x 108 years...

    [font="Courier New"]

    select

      getdate() as TheDate,

      convert(decimal(10,4),getdate()) As decimaldate

    TheDate                 decimaldate

    ----------------------- ---------------------------------------

    2008-02-27 11:54:39.630 39503.4963

    [/font]

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • February 27, 2008 at 10:02 am

    #782663

    Actually, it's stored as a "fixed place float"...

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • February 27, 2008 at 10:03 am

    #782666

    datetime fields are stored as float values. You can verify this by converting a datetime to a float, as shown below.

    declare @dt datetime

    set @dt = '2008-02-28 00:00:00.000'

    select cast (@dt as float)


    My blog: http://jahaines.blogspot.com

  • February 27, 2008 at 10:04 am

    #782667

    Well #77 beat me too it 😛


    My blog: http://jahaines.blogspot.com

  • February 27, 2008 at 10:04 am

    #782669

    thkz but did it works how abt time is it the same way is it in second of the day or what format is that ???

    Thanks,
    Chinna

    Its the Journey which gives you Happiness not the Destination-- Dan Millman

  • February 27, 2008 at 10:06 am

    #782673

    but whats the logic is it the number of days from 1990 yr is that the one i jus want to know that logic ....

    Thanks,
    Chinna

    Its the Journey which gives you Happiness not the Destination-- Dan Millman

  • February 27, 2008 at 10:09 am

    #782676

    Maybe this example will help clarify. You will note the date portion is always to the left of the period. The hours/min/sec etc are on the right. Use the code below to change the current date and time into a float value the cast it back the other way.

    select cast(getdate() as float) [datetime w hr_min_sec], cast(39503 as datetime) [date w 0s]

    As far how the decimal represents the time, I really have no idea that's why I convert it. Maybe someone else has that answer.


    My blog: http://jahaines.blogspot.com

  • February 27, 2008 at 10:12 am

    #782683

    It starts from 1900 and moves forward. The result from below is 0. Anything below '1900-01-1 00:00:00.000' returns a negative number.

    declare @dt datetime

    set @dt = '1900-01-1 00:00:00.000'

    select cast(@dt as float)


    My blog: http://jahaines.blogspot.com

  • February 27, 2008 at 10:14 am

    #782686

    kurraraghu (2/27/2008)

    but whats the logic is it the number of days from 1990 yr is that the one i jus want to know that logic ....

    yes, the logic is the number of days since 01/01/1900. 39503 days so far...and the right of the decimal is the portion of a day... so .5000 would be exactly have a day/12 hours, so it

    39503.5000 would be 12 noon for today...

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • February 27, 2008 at 10:17 am

    #782694

    declare @dt datetime

    set @dt = '1900-01-1 00:00:00.000'

    select cast(@dt as float)

    ----------------------

    0.105555555555556

    (1 row(s) affected)

    here how is the time calculate is it seconds of the day or milliseconds smthng like could plz let me knoe i have got the date logic thkz to all u guys ....

    Thanks,
    Chinna

    Its the Journey which gives you Happiness not the Destination-- Dan Millman

  • February 27, 2008 at 10:18 am

    #782696

    thkz lowell gotch u ....n thkz to all of them...

    Thanks,
    Chinna

    Its the Journey which gives you Happiness not the Destination-- Dan Millman

  • February 27, 2008 at 10:22 am

    #782700

    but lowell i havea ques

    select cast (39504.5023 as datetime) as abc ,getdate() todaydate

    abc todaydate

    ----------------------- -----------------------

    2008-02-28 12:03:18.720 2008-02-27 12:20:13.577

    (1 row(s) affected)

    so how did that 5023 converted to 12:03:18:720 ....

    select cast (39504.5000 as datetime) as abc ,getdate() todaydate

    abc todaydate

    ----------------------- -----------------------

    2008-02-28 12:00:00.000 2008-02-27 12:22:14.373

    (1 row(s) affected)

    where as 5000 is 12:00:00:000 how is that converted

    Thanks,
    Chinna

    Its the Journey which gives you Happiness not the Destination-- Dan Millman

  • February 27, 2008 at 10:29 am

    #782709

    kurraraghu (2/27/2008)

    declare @dt datetime

    set @dt = '1900-01-1 00:00:00.000'

    select cast(@dt as float)

    ----------------------

    0.105555555555556

    (1 row(s) affected)

    here how is the time calculate is it seconds of the day or milliseconds smthng like could plz let me knoe i have got the date logic thkz to all u guys ....

    No... that's not right...

    declare @dt datetime

    set @dt = '1900-01-1 00:00:00.000'

    select cast(@dt as float)

    -----------------------------------------------------

    0.0

    (1 row(s) affected)

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • February 27, 2008 at 10:29 am

    #782710

    Kurraghu - it's stored in increments of a day. so it's .5023 of a day.

    So 1day = 24 hour=24*60 minutes = 24*60*60 secs,

    therefore .5023*24*60*60=43398.72 sec = 723 minutes 18.72 secs = 12h3 minutes 18.72 seconds.

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Viewing 15 posts - 1 through 15 (of 20 total)

You must be logged in to reply to this topic. Login to reply