DATETIME datatype

Question

DATETIME datatype

rehook

SSC Enthusiast

Points: 121
More actions
July 29, 2003 at 11:59 am

#81347

Hi,
I am trying to work with datetime results.
I need to drop the time and only return the date. Is there a function that will do this?
I am trying to group the results by date and the time portion is stopping me from doing so.
Thanks
Rick

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

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

SQLBill SSC Guru Points: 51440 More actions · Answer 1

If you are using DATE functions (DATEADD, DATEDIFF) or just comparing dates; you might not be able to CONVERT it to VARCHAR or CHAR.

So, I suggest the following:

CONVERT(DATETIME(CONVERT(VARCHAR(10),mydate,120)))

This will convert your datetime data type field to a varchar with the format yyyy-mm-dd. Then it will convert that back to datetime data type which will append the default time of 00:00:00.

If you must do it without the time, you'll have to convert it to a varchar or char data type.

See CONVERT in the Books OnLine.

-SQLBill

guarddata SSCertifiable Points: 6583 More actions · Answer 2

There are many ways to do this - but not a built-in function that I know. I use

CONVERT( DATETIME, DATEDIFF( DAY, 0, <a Date> ) )

Guarddata-

jpipes SSChampion Points: 12230 More actions · Answer 3

Don't do CONVERT() to a character type. It does an extra lookup to syslanguages to find the date format localization string. If you need to group by the date and not time:



SELECT DATEADD(day, 0 , DATEDIFF(day, 0, DateField)), COUNT(*)

FROM Table

GROUP BY DATEDIFF(day, 0, DateField)

ORDER BY DATEDIFF(day, 0, DateField)

The DATEDIFF function returns an integer, which is why GROUP BY AND ORDER BY are easily served by DATEDIFF. The DATEADD in the SELECT clause returns a DATETIME, so that your output is readable by DATE.

guarddata SSCertifiable Points: 6583 More actions · Answer 4

Thanks Jay - my response was only a partial answer.

Guarddata-

rehook SSC Enthusiast Points: 121 More actions · Answer 5

Thank you kindly for all the help

It worked perfectly!

Rick

rehook SSC Enthusiast Points: 121 More actions · Answer 6

oops,

I made a mistake with my last post.

I generate this error when running the following commands

SELECT DATEADD(day, 0 , DATEDIFF(day, 0, creationdate)), COUNT(*)

FROM tblUsers

GROUP BY DATEDIFF(day, 0, creationdate)

ORDER BY DATEDIFF(day, 0, creationdate)

Server: Msg 8120, Level 16, State 1, Line 1

Column 'tblUsers.CreationDate' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

jpipes SSChampion Points: 12230 More actions · Answer 7

Actually, I made the mistake, didn't I...

I was trying to illuminate the return types of the two functions and messed it all up...

If you do a GROUP BY, you need to match the SELECT expression. WHERE and ORDER BY should not have this limitation.

Cheers.

rehook SSC Enthusiast Points: 121 More actions · Answer 8

Like This?

SELECT DATEADD(day, 0 , DATEDIFF(day, 0, creationdate)), COUNT(*)

FROM tblUsers

GROUP BY DATEADD(day, 0 , DATEDIFF(day, 0, creationdate))

ORDER BY DATEDIFF(day, 0, creationdate)

It generates another error (below)

Server: Msg 144, Level 15, State 1, Line 4

Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY clause.

jpipes SSChampion Points: 12230 More actions · Answer 9

I'm an idiot. Alias the SELECT into the ORDER BY:



SELECT DATEADD(day, 0 , DATEDIFF(day, 0, creationdate)) as "Date", COUNT(*)

FROM tblUser

GROUP BY DATEADD(day, 0 , DATEDIFF(day, 0, creationdate))

ORDER BY "Date" DESC

sorry

rehook SSC Enthusiast Points: 121 More actions · Answer 10

I have those days too

I did get it to work like this

SELECT DATEADD(day, 0 , DATEDIFF(day, 0, creationdate)), COUNT(*)

FROM tblUsers

GROUP BY DATEADD(day, 0 , DATEDIFF(day, 0, creationdate))

ORDER BY DATEADD(day, 0 , DATEDIFF(day, 0, creationdate)) DESC

And I will try it the other way also

Thank you for all of your help

Rick

Antares686 SSC Guru Points: 125444 More actions · Answer 11

Antares686

SSC Guru

Points: 125444

July 29, 2003 at 4:54 pm

#467627

I thought it was

DATEADD(d,DATEDIFF(d,0,dtCol),0)

gbn SSCertifiable Points: 6429 More actions · Answer 12

For stripping times off, I always prefer this:

CAST(FLOOR(CAST (DateTimeCol AS real)) AS smalldatetime

Drop the fraction from the 2 part number that is a datetime.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 13

Hi gbn,

quote:
For stripping times off, I always prefer this:
CAST(FLOOR(CAST (DateTimeCol AS real)) AS smalldatetime

try this one



DECLARE @testdate datetime

SET @testdate = '30.07.2003 23:59:00'



SELECT CAST(CAST (@testdate AS INT)AS smalldatetime)



SELECT CAST(FLOOR(CAST (@testdate AS real)) AS smalldatetime)



SELECT DATEADD(d,DATEDIFF(d,0,@testdate),0)

See what happens?

The first SELECT is what I use, when results don't have to be precise.

In case you have to rely on accuracy, I would always use something like Antares has mentioned

Cheers,

Frank

Edited by - a5xo3z1 on 07/30/2003 01:47:35 AM

Edited by - a5xo3z1 on 07/30/2003 01:51:55 AM

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

gbn SSCertifiable Points: 6429 More actions · Answer 14

Hi Frank

Good point.

Using float (any of float(1), float(24) or float(53)) gives the correct result

Using real works upto 23:57 (smalldatetime)

Casting to int will round it, rather than truncate won't it?

Cheers

Shawn

Edited by - gbn on 07/30/2003 01:56:50 AM