Post reply

DATETIME datatype

  • 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

  • July 29, 2003 at 12:14 pm

    #467560

    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

  • July 29, 2003 at 12:25 pm

    #467563

    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-

  • July 29, 2003 at 12:30 pm

    #467565

    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.

  • July 29, 2003 at 1:30 pm

    #467576

    Thanks Jay - my response was only a partial answer.

    Guarddata-

  • July 29, 2003 at 1:34 pm

    #467579

    Thank you kindly for all the help

    It worked perfectly!

    Rick

  • July 29, 2003 at 2:49 pm

    #467601

    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.

  • July 29, 2003 at 2:53 pm

    #467603

    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.

  • July 29, 2003 at 2:57 pm

    #467606

    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.

  • July 29, 2003 at 3:03 pm

    #467607

    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

  • July 29, 2003 at 3:15 pm

    #467610

    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

  • July 29, 2003 at 4:54 pm

    #467627

    I thought it was

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

  • July 30, 2003 at 1:28 am

    #467657

    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.

  • July 30, 2003 at 1:45 am

    #467663

    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]

  • July 30, 2003 at 1:53 am

    #467664

    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

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

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