SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Kaifi / What will happen? / Latest Posts

RE: What will happen?

zymos — Mon, 19 Mar 2012 10:09:21 GMT

Good and straight forward question. Thanks

RE: What will happen?

L' Eomot Inversé — Tue, 07 Sep 2010 10:50:06 GMT

good question. some good discussion too. but shifting from explicit conversion of datetime to int (fast and efficient) to explicit conversion of datetime to a very local varchar date format seemed a little bizarre.

RE: What will happen?

Hugo Kornelis — Thu, 02 Sep 2010 04:01:50 GMT

[quote][b]Robert Dennyson (9/2/2010)[/b][hr]It should work[code="sql"]SELECT COVERT(VARCHAR,GETDATE(),111);[/code][/quote]If you add the missing letter N (CO[b]N[/b]VERT), it does work. It is an explicit conversion from datetime to varchar format, using the yyyy/mm/dd format (a format that is not locale-neutral and hence not recommended in various places, but if you need the yyyy/mm/dd format for a report, this is the function to use).However, I fail to see the relation with the question discussed here, which is about implicit conversion from datetime to int, not about explicit conversion from datetime to varchar.

RE: What will happen?

Robert Dennyson — Thu, 02 Sep 2010 02:43:13 GMT

It should work[code="sql"]SELECT COVERT(VARCHAR,GETDATE(),111);[/code]

RE: What will happen?

UMG Developer — Thu, 02 Sep 2010 00:02:42 GMT

Thanks for the question!

RE: What will happen?

rjv_rnjn — Wed, 01 Sep 2010 12:58:42 GMT

[quote][b]Hugo Kornelis (9/1/2010)[/b][hr][quote][b]Oleg Netchaev (9/1/2010)[/b][hr]...[code="sql"]select dateadd(day, datediff(day, 0, current_timestamp), 0);[/code]Why faster is because how datetime is stored internally (4 bytes for number of days from zero date and 4 bytes for number of ticks from midnight of today).[/quote]The reason I use a date in the 'yyyymmdd' format instead of an integer value is because it's easier for the eye (assuming an eye that is somewhat used to this format) to interpret as a date. As a matter of principle, I always use 'yyyymmdd' for date constants in my code. I would never dream of using the integer constant 40421 to represent Sep 1st, 2010 (and I hope noone would even consider that), even though it does indeed convert to datetime faster than the string constant '20100901'. And I don't like to make an exception for representing the date Jan 1st, 1900.But I do agree that it's quite common to use 0 in this particular construction, and that it does not really hurt.:-)[/quote]Thank you Oleg & Hugo. I was not surprised by the question (not to imply anything about the question itself) as I have faced these conversion issues earlier but was looking at the discussion anyways and found this interesting piece of information.

RE: What will happen?

Hugo Kornelis — Wed, 01 Sep 2010 09:32:57 GMT

[quote][b]Oleg Netchaev (9/1/2010)[/b][hr]Hugo's script looks somewhat unusual because it uses some varchar value which is guaranteed to be dateformat independent valid datetime value (YYYYMMDD is always translated correctly regardless of local format). What is usually used instead is 0, because it is simply zero date, shorter to type and much faster to convert (rather than rely on engine's ability to convert varchar to datetime), i.e.[code="sql"]select dateadd(day, datediff(day, 0, current_timestamp), 0);[/code]Why faster is because how datetime is stored internally (4 bytes for number of days from zero date and 4 bytes for number of ticks from midnight of today).[/quote]Correct. Thanks, Oleg!The reason I use a date in the 'yyyymmdd' format instead of an integer value is because it's easier for the eye (assuming an eye that is somewhat used to this format) to interpret as a date. As a matter of principle, I always use 'yyyymmdd' for date constants in my code. I would never dream of using the integer constant 40421 to represent Sep 1st, 2010 (and I hope noone would even consider that), even though it does indeed convert to datetime faster than the string constant '20100901'. And I don't like to make an exception for representing the date Jan 1st, 1900.But I do agree that it's quite common to use 0 in this particular construction, and that it does not really hurt.:-)

RE: What will happen?

webrunner — Wed, 01 Sep 2010 09:26:46 GMT

[quote][b]Hugo Kornelis (9/1/2010)[/b][hr][quote][b]webrunner (9/1/2010)[/b][hr][code]DECLARE @a INTDECLARE @b DATETIMESET @a = @b[/code]The above code executed successfully for me in SQL 2005 but gave the error listed in the answer when I ran it in SQL 2000**. So I think it is dependent on the SQL version, at least in the form expressed above.[/quote]Which version of SQL Server 2005? When I run it, I get "Msg 257, Level 16, State 3, Line 3Implicit conversion from data type datetime to int is not allowed. Use the CONVERT function to run this query."(I am using Microsoft SQL Server 2005 - 9.00.4053.00)[/quote]Sorry, my mistake. (BTW, My SQL 2005 is version 9.0.3080.)This code runs OK in both versions:[code]DECLARE @a INTDECLARE @b DATETIMESET @b = @a[/code]This code throws an error in both versions:[code]DECLARE @a INTDECLARE @b DATETIMESET @a = @b[/code]I mixed up the "SET @a = @b" version with the "SET @b= @a" version in my earlier post. I seem to have run one version in SQL 2000 and the other in SQL 2005.Attention to detail, anyone? :hehe:Thanks,webrunner

RE: What will happen?

Oleg Netchaev — Wed, 01 Sep 2010 09:07:57 GMT

[quote][b]Dave62 (9/1/2010)[/b][hr][code="sql"]SELECT DATEADD(day, DATEDIFF(day, '20100101', CURRENT_TIMESTAMP), '20100101');[/code]The only thing I don't like about this method is the hard-coded string. I suppose a dynamic expression could replace it but it would start to become a little tedious.I don't mind having 2 converts in there because mbova407's original example had 2. This method below will return the same result and datatype as mbova407's without the need for the integer addition.[code="sql"]Select Convert(Datetime, CONVERT(varChar(10), getDate(), 101));[/code][/quote]The latter method is considerably more expensive though. The [url=http://www.sqlservercentral.com/blogs/lynnpettis/archive/2009/03/25/some-common-date-routines.aspx]SQL Musings from the Desert [/url]article by Lynn Pettis has been dissected by quite few posts and from what I remember, the verdict is pretty clear: dateadd and datediff combination is far cheaper than conversion functions. Hugo's script looks somewhat unusual because it uses some varchar value which is guaranteed to be dateformat independent valid datetime value (YYYYMMDD is always translated correctly regardless of local format). What is usually used instead is 0, because it is simply zero date, shorter to type and much faster to convert (rather than rely on engine's ability to convert varchar to datetime), i.e.[code="sql"]select dateadd(day, datediff(day, 0, current_timestamp), 0);[/code]Why faster is because how datetime is stored internally (4 bytes for number of days from zero date and 4 bytes for number of ticks from midnight of today).  Oleg

RE: What will happen?

Dave62 — Wed, 01 Sep 2010 08:34:20 GMT

[quote][b]Hugo Kornelis (9/1/2010)[/b][hr]...I don't really see the hard-coded string as a problem. The only requirement is that you use the same date twice, it doesn't matter what date you choose. There is no risk of overflow, as there are way more integers in the int domain, then days in the datetime domain.[/quote]You're right Hugo. I guess we have 2 ways to get the same results without the integer addition.But one way is a little more concise with a little less hard-coding. ;-)

RE: What will happen?

Hugo Kornelis — Wed, 01 Sep 2010 08:26:19 GMT

[quote][b]webrunner (9/1/2010)[/b][hr][code]DECLARE @a INTDECLARE @b DATETIMESET @a = @b[/code]The above code executed successfully for me in SQL 2005 but gave the error listed in the answer when I ran it in SQL 2000**. So I think it is dependent on the SQL version, at least in the form expressed above.[/quote]Which version of SQL Server 2005? When I run it, I get "Msg 257, Level 16, State 3, Line 3Implicit conversion from data type datetime to int is not allowed. Use the CONVERT function to run this query."(I am using Microsoft SQL Server 2005 - 9.00.4053.00)

RE: What will happen?

Hugo Kornelis — Wed, 01 Sep 2010 08:24:05 GMT

RE: What will happen?

webrunner — Wed, 01 Sep 2010 08:21:27 GMT

[code]DECLARE @a INTDECLARE @b DATETIMESET @a = @b[/code]The above code executed successfully for me in SQL 2005 but gave the error listed in the answer when I ran it in SQL 2000**. So I think it is dependent on the SQL version, at least in the form expressed above.- webrunner** Version: Microsoft SQL Server 2000 - 8.00.194 (Intel X86) Aug 6 2000 00:57:48 Copyright (c) 1988-2000 Microsoft Corporation Personal Edition on Windows NT 5.1 (Build 2600: Service Pack 3)

RE: What will happen?

jmccoy-1028380 — Wed, 01 Sep 2010 08:06:14 GMT

? What is a use for this? Just getting the date for use at another time? If you try to convert the value back to type of datetime you lose the time portion. Set @b = '08/31/2010 08:08:08 AM'set @a = convert(int, @b)print @a [result = 40419]set @b = convert(datetime, @a)print @b [result = Aug 31 2010 12:00 AM]

RE: What will happen?

Dave62 — Wed, 01 Sep 2010 07:45:15 GMT

[code="sql"]SELECT DATEADD(day, DATEDIFF(day, '20100101', CURRENT_TIMESTAMP), '20100101');[/code]The only thing I don't like about this method is the hard-coded string. I suppose a dynamic expression could replace it but it would start to become a little tedious.I don't mind having 2 converts in there because mbova407's original example had 2. This method below will return the same result and datatype as mbova407's without the need for the integer addition.[code="sql"]Select Convert(Datetime, CONVERT(varChar(10), getDate(), 101));[/code]

RE: What will happen?

Hugo Kornelis — Wed, 01 Sep 2010 07:17:31 GMT

[quote][b]tushkieeeee (9/1/2010)[/b][hr]please try the code below. I think this is what the auhtor wanted to convey.[/quote]I don't think so. I think the author wanted to convey that implicit conversion from datetime to int is not allowed. In this discussion, the examples somehow switched to implicit conversion from int to datetime, which is fine.[quote][b]Saurabh Dwivedy (9/1/2010)[/b]Is this dependent on the sql server version?[/quote]I think the above also answers this - as far as I know, the behaviour is the same in all SQL Server versions (though not when using the new date and time datatypes instead of datetime!): implicit conversion is always allowed from int to datetime, and never from datetime to int.[quote][b]mbova407 (9/1/2010)[/b][hr]This is how we get the date for the current day without time element(...)the -.5 is half a day because CONVERT(INT,@b) rounds[/quote]I don't really like this method - the addition of an integer value to a datetime, though documented, is wacky, and it doesn't work anymore when switching to the new date or time datatypes; the addition of a non-integer to a datetime is (as far as I know) not even documented.[quote][b]Dave62 (9/1/2010)[/b][hr]Looks like the time is still there but just 0.Here's another way to get the current date without the time:[code="sql"]Select CONVERT(varChar(10), getDate(), 101);[/code]This returns: 09/01/2010[/quote]As long as you keep the datetime datatype, you'll always have a time part. But it can be very useful to ensure the time part is 0, if you need only the day part - it makes comparisons a lot easier. When running SQL2008, you can of course use [i]date[/i] instead, but many people are still running SQL2005.Your alternative is fine if you need the date in character format for presentation purposes. When using it for calculations, you'd have to convert back to datetime. That would at least require you to use a locale-neutrall format (09/01/2010 is the ninth of january in most of the world!), but then you'd still be taking the performance hit of two expensive converstions (datetime to char and char to datetime). Here is my favorite way to force the time part of a datetime to midnight:[code="sql"]SELECT DATEADD(day, DATEDIFF(day, '20100101', CURRENT_TIMESTAMP), '20100101');[/code]For much more information, see [url=http://www.karaszi.com/SQLServer/info_datetime.asp]http://www.karaszi.com/SQLServer/info_datetime.asp[/url]

RE: What will happen?

Daniel Bowlin — Wed, 01 Sep 2010 07:07:37 GMT

Good question. More than anything, QoTD seems to reinforce.....be explicit.

RE: What will happen?

Dave62 — Wed, 01 Sep 2010 07:00:10 GMT

[quote][b]mbova407 (9/1/2010)[/b][hr]This is how we get the date for the current day without time elementDECLARE @a INTDECLARE @b DATETIMEset @b = GETDATE()-.5SET @a = CONVERT(INT,@b)SELECT CONVERT(DATETIME, @a), @bthe -.5 is half a day because CONVERT(INT,@b) rounds[/quote]If I run the code above I get:(No column name) | (No column name)2010-09-01 00:00:00.000 | 2010-08-31 20:54:59.743Looks like the time is still there but just 0.Here's another way to get the current date without the time:[code="sql"]Select CONVERT(varChar(10), getDate(), 101);[/code]This returns: 09/01/2010

RE: What will happen?

mbova407 — Wed, 01 Sep 2010 06:47:29 GMT

This is how we get the date for the current day without time elementDECLARE @a INTDECLARE @b DATETIMEset @b = GETDATE()-.5SET @a = CONVERT(INT,@b)SELECT CONVERT(DATETIME, @a), @bthe -.5 is half a day because CONVERT(INT,@b) rounds

RE: What will happen?

Saurabh Dwivedy — Wed, 01 Sep 2010 02:58:17 GMT

[quote][b]ziangij (8/31/2010)[/b][hr][code="sql"]DECLARE @a INTDECLARE @b DATETIMESET @b = @a[/code]implicit conversion from int to datetime works fine :-)[/quote]Is this dependent on the sql server version?

RE: What will happen?

tushkieeeee — Wed, 01 Sep 2010 02:45:11 GMT

@ziangij please try the code below. I think this is what the auhtor wanted to convey. However one thing I would like to point out that Datetime is 8byte and Int is 4byte.DECLARE @a INT DECLARE @b DATETIME set @a= getdate()SET @b = @a Thanks,Tushar

RE: What will happen?

Michael Riemer — Wed, 01 Sep 2010 01:37:48 GMT

Thanks for the question!:-)

RE: What will happen?

SQLRNNR — Wed, 01 Sep 2010 00:50:09 GMT

thanks for the question

RE: What will happen?

Hardy21 — Wed, 01 Sep 2010 00:38:01 GMT

Good Question. Thanks

RE: What will happen?

tommyh — Wed, 01 Sep 2010 00:02:53 GMT

:Whistling:

RE: What will happen?

ziangij — Tue, 31 Aug 2010 22:53:35 GMT

[code="sql"]DECLARE @a INTDECLARE @b DATETIMESET @b = @a[/code]implicit conversion from int to datetime works fine :-)

What will happen?

Kaifi — Tue, 31 Aug 2010 22:50:52 GMT

Comments posted to this topic are about the item [B]What will happen?[/B]