Post reply

how to calculate date time

  • April 9, 2015 at 12:17 am

    #316421

    hi,

    my data is looks like this,

    Date---------------------------------------A

    2015-03-01 13:38:07.343----------------1

    2015-03-01 14:04:04.460----------------1

    2015-03-02 19:33:55.117----------------3

    2015-03-02 19:33:55.117----------------4

    2015-03-02 19:39:26.580----------------1

    i want data looks like this

    Date-------------------------------------------A

    Day 1------------------------------------------2

    Day 2------------------------------------------8

    please can any one provide me a query.

    please help me out

    Thanks for the help

    immad

  • April 9, 2015 at 12:28 am

    #1789772

    immaduddinahmed (4/9/2015)

    hi,

    my data is looks like this,

    Date---------------------------------------A

    2015-03-01 13:38:07.343----------------1

    2015-03-01 14:04:04.460----------------1

    2015-03-02 19:33:55.117----------------3

    2015-03-02 19:33:55.117----------------4

    2015-03-02 19:39:26.580----------------1

    i want data looks like this

    Date-------------------------------------------A

    Day 1------------------------------------------2

    Day 2------------------------------------------8

    please can any one provide me a query.

    please help me out

    Thanks for the help

    Looks like homework to me. What have you tried?

    You might want to look at "Beginning of this day" by Lynn Pettis in his Some Common Date Routines[/url] to get you started.

    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • April 9, 2015 at 12:32 am

    #1789773

    hi,

    i try count function + datename function and also datediff function but no use.

    i cant merge same dates

    please help me out

    thanks for the help

    immad

  • April 9, 2015 at 12:40 am

    #1789775

    immaduddinahmed (4/9/2015)

    hi,

    i try count function + datename function and also datediff function but no use.

    i cant merge same dates

    please help me out

    thanks for the help

    Try converting your DATETIME value using the formula I pointed you to in Lynn's article. That should give you a pretty clear idea on how to proceed with grouping.

    And you'll probably want to use SUM instead of COUNT.

    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • April 9, 2015 at 12:58 am

    #1789778

    You need to return just the date portion of the datetime field. After that, it's easy.

  • April 9, 2015 at 8:05 am

    #1789870

    immaduddinahmed (4/9/2015)

    hi,

    my data is looks like this,

    Date---------------------------------------A

    2015-03-01 13:38:07.343----------------1

    2015-03-01 14:04:04.460----------------1

    2015-03-02 19:33:55.117----------------3

    2015-03-02 19:33:55.117----------------4

    2015-03-02 19:39:26.580----------------1

    i want data looks like this

    Date-------------------------------------------A

    Day 1------------------------------------------2

    Day 2------------------------------------------8

    please can any one provide me a query.

    please help me out

    Thanks for the help

    What do you want for days that might be missing between the first date and the last date? For example, what if your data looked like this? Then what would you want returned?

    Date---------------------------------------A

    2015-03-01 13:38:07.343----------------1

    2015-03-01 14:04:04.460----------------1

    2015-03-04 19:33:55.117----------------3

    2015-03-06 19:33:55.117----------------4

    2015-03-06 19:39:26.580----------------1

    --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

  • April 9, 2015 at 1:12 pm

    #1789986

    And what constitutes Day1? Is Day1 always the earliest date in your table?

    Sigerson

    "No pressure, no diamonds." - Thomas Carlyle

  • April 9, 2015 at 1:25 pm

    #1789989

    Try this:

    select

    cast(YourDateTimeCol as date) CalDate,

    sum(A) A

    from

    dbo.YourTable

    group by

    cast(YourDateTimeCol);

    This will not provide values for missing dates which is the question Jeff Moden was asking you about in his post.

Viewing 8 posts - 1 through 7 (of 7 total)

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