Post reply

date time field

  • December 9, 2004 at 1:36 pm

    #61494

    how do i remove the tome portion of a date/time field whilst preserving the data type (i.e not converting to varchar)

    thanks

     

  • December 9, 2004 at 2:11 pm

    #533251

    You can't actually remove it, but for presentation purposes, see "cast and convert" in Books Online.

    Steve

  • December 9, 2004 at 2:19 pm

    #533253

    have done, cant see how to maintain its datetime format, if there is a way can you help

    Craig

  • December 9, 2004 at 3:11 pm

    #533257

    Please provide specific information about what you are trying to do.

    Steve

  • December 10, 2004 at 1:11 am

    #533289

    Hi Craig,

    since the datetime format is stored as number of time elements elapsed from a certain date, and defined as two 4-byte integers, there is no way to "remove" the time portion altogether while preserving the format. Of course, you can set the time portion to 00:00:00.000, but the second 4 bytes will still be there. Please explain what is your problem, and why do you want to remove the time... maybe there is another way how to achieve what you need.

  • December 10, 2004 at 2:40 am

    #533294

    This maybe?

    DECLARE @dtmDateTime dateTime

    SET @dtmDateTime = getdate()

    --DATE WITH NO TIME using conversions

    SELECT @dtmDateTime as [Current Date Time],

    CONVERT(DATETIME,CONVERT(INT, CONVERT(FLOAT,@dtmDateTime)))

    This 2004-12-10 09:32:10.500                               

    to this 2004-12-10 00:00:00.000

    ?

  • December 10, 2004 at 7:33 am

    #533326

    DECLARE @dtmDateTime dateTime

    SET @dtmDateTime = getdate()

    SELECT @dtmDateTime as [Current Date Time],

    CONVERT(DATETIME,CONVERT(varchar(11), @dtmDateTime), 112)

  • December 10, 2004 at 7:40 am

    #533328

    Seems to be a common problem.  See:

    http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=139040#bm139268

    We switched all our triggers and stored procedures to use the DATEADD(d,DATEDIFF(d,0,[DateVar]),0) function.  Others in this forum have said it is faster. 

  • December 10, 2004 at 8:13 am

    #533343

    Out of interest can you tell me how they worked out that it is faster? And faster than what exactly?

  • December 10, 2004 at 12:32 pm

    #533393

    DATEADD(d,DATEDIFF(d,0,[DateVar]),0)

    is faster than

    CONVERT(DATETIME,CONVERT(varchar(11), [DatVar]), 112)

    Both simply set the time component to 12:00:00 AM.

    We have a database with patient appointments and the ApptDate column is the date & time of the appointment.  We got so tired of coding WHERE (ApptDate >= @SomeDate AND ApptDate < DateAdd(d,1,@SomeDate) to get everything on a specific day that we added another field that is set to the "Date Only", i.e. the time is set to midnight, in the Insert and Update triggers. 

    How much faster:

    DECLARE @Begin datetime

    DECLARE @End datetime

    DECLARE @Elapsed int

    SET @Begin = getdate()

    SELECT DATEADD(d,DATEDIFF(d,0,Apptdate),0) FROM Appointment

    SET @End = getdate()

    SET @Elapsed = DateDiff(s, @Begin, @End)

    PRINT @Elapsed

    SET @Begin = getdate()

    SELECT CONVERT(DATETIME,CONVERT(varchar(11), Apptdate), 112) FROM Appointment

    SET @End = getdate()

    SET @Elapsed = DateDiff(s, @Begin, @End)

    PRINT @Elapsed

    Results (num seconds to convert that many rows):

    (2,096,939 row(s) affected)

    12

    (2,096,939 row(s) affected)

    70

     

  • December 10, 2004 at 12:39 pm

    #533395

    Try this:

    select cast(convert(varchar(10),[field_name],101) as smalldatetime)

     

  • December 13, 2004 at 5:18 am

    #533485

    Well, this is a very subjective measurement, and it's not quite accurate for the purpose either.

    What you are measuring here is just the time it takes for the client to render n rows on your client. This number can be very different depending on all kinds of other factors than the two ways of converting datetimes. The main differences lies in network bandwith, client cpu and memory resources and such.

    As an example, I ran four comparative tests on my own workstation - two as per the example (which also contains an unnecessary extra three bytes returned for each row - this adds to the overall time), and two using a more optimized char(8) instead of varchar(11) thereby reducing the number of bits transferred over the wire. One of each also in reverse order - so that eventual caching/paging at the client may be ruled out as an influenting factor. (the latter resultset may suffer more from paging on the client that the first)

    Anyway, here's what happened;

    -- 1st run (varchar(11))

    (1265928 row(s) affected)

    132

    (1265928 row(s) affected)

    149

    -- 2nd run (char(8))

    (1265928 row(s) affected)

    111

    (1265928 row(s) affected)

    117

    -- 3rd run (varchar(11) - switched places)

    (1265928 row(s) affected)

    121

    (1265928 row(s) affected)

    112

    -- 4th run (char(8) - switched places)

    (1265928 row(s) affected)

    115

    (1265928 row(s) affected)

    111

    It seems like the DATEADD/DATEDIFF combo indeed is faster than the CONVERT, however by looking at the differences in seconds, it seems more like an academic issue. I could not repeat the great delta of 12 to 70 as the first example showed - my own tests showed consistently that the two were close to identical, with a slight bias in DATEADD/DATEDIFF favor.

    In any case, trying to measure the speed/effiency of functions by returning rows to a client is a blunt tool of measurement at best - too many outstanding factors bias the results.

    just my .02, though

    /Kenneth

Viewing 12 posts - 1 through 11 (of 11 total)

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