-
Jun 18th, 2018, 08:19 AM
#1
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.
ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·
-
Jun 18th, 2018, 08:47 AM
#2
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)
-
Jun 18th, 2018, 09:22 AM
#3
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?
ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·
-
Jun 18th, 2018, 10:02 AM
#4
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.
ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·
-
Jun 18th, 2018, 10:48 AM
#5
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
-
Jun 18th, 2018, 11:24 AM
#6
Re: Datetime grouping fails when converting
Originally Posted by sapator
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!
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|