If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Microsoft SQL Server > how to convert timestamp to date only ?

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
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.
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #3 (permalink)  
Old
World Class Flame Warrior
 
Join Date: Jun 2003
Location: Ohio
Posts: 12,561
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
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
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.
Reply With Quote
  #5 (permalink)  
Old
World Class Flame Warrior
 
Join Date: Jun 2003
Location: Ohio
Posts: 12,561
@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
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
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.
Reply With Quote
  #8 (permalink)  
Old
World Class Flame Warrior
 
Join Date: Jun 2003
Location: Ohio
Posts: 12,561
@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
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On