Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2004
    Posts
    74

    how to convert timestamp to date only ?

    hi, good day, i would like to convert timstamp value into date with dateformat (yyyy-MM-dd) , how to i do that ?

    i have try using convert method with code 20 and 21 , but it contain the time as well , i just need the date with no time and seconds include


    thank you
    Last edited by alvincks; 01-08-06 at 09:53.

  2. #2
    Join Date
    Dec 2005
    Posts
    39
    what is your target datatype - is it datetime? if yes, time part would come by default. you can assign this to a varchar which should work

    declare @dt varchar(10)
    select @dt = convert(varchar(10), getdate(), 20)

    that should give you only the date part. hope it helps

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,564
    More efficient, but less intuitive, is this method:

    declare @dt varchar(10)
    select @dt = dateadd(day, datediff(day, 0, @dt), 0)

    This is faster than CONVERT, because it involves only arithmetic operations instead of string manipulation.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Dec 2005
    Posts
    39
    I didn't understand this -

    declare @dt varchar(10)
    select @dt = dateadd(day, datediff(day, 0, @dt), 0)

    won't that give an error as datediff expects 2 date expressions and you've specified 0 & @dt which is declared as varchar?

    Regarding speed, arithmetic operations may be faster than string manipulations but in this case it involves local variables & not table columns so there shouldn't be much difference. If it were on a huge table maybe it could have some impact.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,564
    @dt should be declared as datetime, but the code will work anyway as SQL Server implicitly converts date strings.
    DateTime values are actually stored as numeric values, which allows you to pass 0 as a parameter to the datediff and dateadd functions.

    Whether the variable are local or not does affect the difference in performance between arthimetic and string operations. CONVERT may appear to be a single operation, but its logic contains a loop to process each character of the string, along with whatever logic is required to ensure the result has the required formatting. This is why it is slower than doing a single subtraction (datediff) and a single addition (dateadd).
    The difference in speed for single values or small datasets will not be noticable. Hence my statement that the datediff method is faster, but less intuitive. Use whatever method fits your circumstance.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Mar 2004
    Posts
    74
    sorry, i not very good in database , @dt is it means temporary column ? we can put any name as we like ?

    thank you

  7. #7
    Join Date
    Dec 2005
    Posts
    39
    No, @dt is not a temporary column, rather local variable. However it can also be used to store column values. And yes, you can use any name you like.

    Blindman,
    thanks for the explanation. learnt a new thing today @ the internals of convert working in a loop, was not aware of that, so will bear this in mind when i'm faced with a similar problem.

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,564
    @dt was simply used as an example, since we don't know the design of your schema. The same formula(s) will work for both variables and column values.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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