Results 1 to 6 of 6

Thread: Datetime grouping fails when converting

  1. #1

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    6,597

    Datetime grouping fails when converting

    Hi. SQL 2008R2
    I have 2 dates like 2018-05-18 21:04:44.717 and 2018-05-18 21:04:44.917
    I want to get rid of the seconds milliseconds part .
    Doing this convert(varchar,thedate,120) as mydate
    and grouping by thedate as convert(varchar,thedate,120) will group it but if there is a second difference then it will not group it.

    So I would need something like this (that will not work but is to give you a clue)
    select convert(varchar,thedate,120) as mydate
    group by convert(varchar,thedate,112) -- yyyymmdd

    Of course this will complain.

    I don't want to go to overcomplicated solutions with tables grouping tables grouping tables.
    Is there a simple solution?
    Thanks.
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

  2. #2
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: Datetime grouping fails when converting

    If you are grouping by the 112 (yyyymmdd) then you must have an aggregate function on the selected field.

    Are you really using varchar WITHOUT a size - so as to get varchar(16) - which is the default??

    At any rate:

    Select Max(Convert(Varchar(16),TheDate,120) From... Group by Convert(varchar(8),TheDate,112)

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  3. #3

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    6,597

    Re: Datetime grouping fails when converting

    Hi.
    It seems to work, however I cannot grasp the Max idea.
    Are we using it just to bypass the exception of SQL?
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

  4. #4

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    6,597

    Re: Datetime grouping fails when converting

    Also another question.
    I have this on 2 union tables.
    I'm trying to do something like this to get the midnight until 6 to appear last.
    So Case when convert(varchar(8),mydate,108) between '00:00:00' and '06:00:00' then 2 else 1 end
    Not sure I am doing it right , maybe i need 1 and then 2 ? Anyhow it will again complain that the column is ambiguous.

    I could just put the whole union in a temp or CTE and group it but is there a way to do that on the spot?
    thanks.
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

  5. #5
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: Datetime grouping fails when converting

    sounds like you're not giving the column an alias - a name... so it doesn't know what to call it, making it ambiguous.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  6. #6
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: Datetime grouping fails when converting

    Quote Originally Posted by sapator View Post
    Hi.
    It seems to work, however I cannot grasp the Max idea.
    Are we using it just to bypass the exception of SQL?
    When you GROUP BY something you are asking "break" logic to create rows based on that "grouped value".

    If you are grouping on YYYYMMDD you are CREATING ONE row for each DATE.

    That means you can show only one DATE / TIME on that row. I choose MAX() to get the latest one. You could do a MIN() and a MAX() in two different columns to show the "range" of date/times on that SINGLE DAY.

    So to answer your question - NO I did not use MAX() to get around the error message - I used MAX() to give you what you are asking for!

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width