Post reply

Use only date part of datetime field for GROUP BY

  • February 4, 2015 at 1:37 am

    #98855

    Hi All!

    I have this table:

    [dbo].[Login_details](

    [login_data_id] [int] IDENTITY(1,1) NOT NULL,

    [login_no] [int] NULL,

    [logindate] [datetime] NULL,

    [logoutdate] [datetime] NULL,

    CONSTRAINT [PK_Login_details] PRIMARY KEY CLUSTERED

    (

    [login_data_id] ASC

    )

    This query:

    Select count(*) from dbo.Login_details group by logindate

    Gives only one pr logindate, because the logindate has a time part.

    So, I want to GROUP BY (datepart of logindate)

    Looked quite a bit around in daypart, but that was not the answer!

    Best regards

    Still very newbie

    Edvard Korsbæk

  • February 4, 2015 at 2:03 am

    #1775225

    Edvard Korsbæk (2/4/2015)

    Hi All!

    I have this table:

    [dbo].[Login_details](

    [login_data_id] [int] IDENTITY(1,1) NOT NULL,

    [login_no] [int] NULL,

    [logindate] [datetime] NULL,

    [logoutdate] [datetime] NULL,

    CONSTRAINT [PK_Login_details] PRIMARY KEY CLUSTERED

    (

    [login_data_id] ASC

    )

    This query:

    Select count(*) from dbo.Login_details group by logindate

    Gives only one pr logindate, because the logindate has a time part.

    So, I want to GROUP BY (datepart of logindate)

    Looked quite a bit around in daypart, but that was not the answer!

    Best regards

    Still very newbie

    Edvard Korsbæk

    Have you tried

    CAST(logindate as DATE)

    On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
    —Charles Babbage, Passages from the Life of a Philosopher

    How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

  • February 4, 2015 at 2:06 am

    #1775226

    When you know how, its very,very easy!

    Thanks!

  • February 4, 2015 at 2:08 am

    #1775227

    You're welcome.

    On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
    —Charles Babbage, Passages from the Life of a Philosopher

    How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

Viewing 4 posts - 1 through 3 (of 3 total)

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