Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


What will happen?


What will happen?

Author
Message
Daniel Bowlin
Daniel Bowlin
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2954 Visits: 2629
Good question. More than anything, QoTD seems to reinforce.....be explicit.
Hugo Kornelis
Hugo Kornelis
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8321 Visits: 11554
tushkieeeee (9/1/2010)
please try the code below. I think this is what the auhtor wanted to convey.

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.

Saurabh Dwivedy (9/1/2010)Is this dependent on the sql server version?

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.

mbova407 (9/1/2010)
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

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.

Dave62 (9/1/2010)
Looks like the time is still there but just 0.

Here's another way to get the current date without the time:
Select CONVERT(varChar(10), getDate(), 101);



This returns: 09/01/2010

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 date 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:
SELECT DATEADD(day, DATEDIFF(day, '20100101', CURRENT_TIMESTAMP), '20100101');



For much more information, see http://www.karaszi.com/SQLServer/info_datetime.asp


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Dave62
Dave62
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3050 Visits: 2714
SELECT DATEADD(day, DATEDIFF(day, '20100101', CURRENT_TIMESTAMP), '20100101');


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.
Select Convert(Datetime, CONVERT(varChar(10), getDate(), 101));


jmccoy-1028380
jmccoy-1028380
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 41
? 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]
webrunner
webrunner
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3028 Visits: 3751

DECLARE @a INT
DECLARE @b DATETIME
SET @a = @b



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)

-------------------
"I love spending twice as long and working twice as hard to get half as much done!" – Nobody ever.
Ref.: http://www.adminarsenal.com/admin-arsenal-blog/powershell-how-to-write-your-first-powershell-script

"Operator! Give me the number for 911!" - Homer Simpson

"A SQL query walks into a bar and sees two tables. He walks up to them and says 'Can I join you?'"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
Hugo Kornelis
Hugo Kornelis
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8321 Visits: 11554
Dave62 (9/1/2010)
SELECT DATEADD(day, DATEDIFF(day, '20100101', CURRENT_TIMESTAMP), '20100101');


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 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.


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Hugo Kornelis
Hugo Kornelis
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8321 Visits: 11554
webrunner (9/1/2010)

DECLARE @a INT
DECLARE @b DATETIME
SET @a = @b



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.

Which version of SQL Server 2005? When I run it, I get "Msg 257, Level 16, State 3, Line 3
Implicit 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)


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Dave62
Dave62
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3050 Visits: 2714
Hugo Kornelis (9/1/2010)
...
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.

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. ;-)
Oleg Netchaev
Oleg Netchaev
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1693 Visits: 1808
Dave62 (9/1/2010)
SELECT DATEADD(day, DATEDIFF(day, '20100101', CURRENT_TIMESTAMP), '20100101');


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.
Select Convert(Datetime, CONVERT(varChar(10), getDate(), 101));


The latter method is considerably more expensive though. The SQL Musings from the Desert 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.

select dateadd(day, datediff(day, 0, current_timestamp), 0);



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).

<!-- Begin blatant self promotion

One of my answers on the ask side has in-depth explanation of datetime internals, and according to Matt Whitfield, it "sounds spot on". Here is the link: http://ask.sqlservercentral.com/questions/16420/php-with-mssql-strtotime-with-mssql-datetime-column

End blatant self promotion -->

Oleg
webrunner
webrunner
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3028 Visits: 3751
Hugo Kornelis (9/1/2010)
webrunner (9/1/2010)

DECLARE @a INT
DECLARE @b DATETIME
SET @a = @b



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.

Which version of SQL Server 2005? When I run it, I get "Msg 257, Level 16, State 3, Line 3
Implicit 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)


Sorry, my mistake. (BTW, My SQL 2005 is version 9.0.3080.)

This code runs OK in both versions:


DECLARE @a INT
DECLARE @b DATETIME

SET @b = @a



This code throws an error in both versions:


DECLARE @a INT
DECLARE @b DATETIME

SET @a = @b



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

-------------------
"I love spending twice as long and working twice as hard to get half as much done!" – Nobody ever.
Ref.: http://www.adminarsenal.com/admin-arsenal-blog/powershell-how-to-write-your-first-powershell-script

"Operator! Give me the number for 911!" - Homer Simpson

"A SQL query walks into a bar and sees two tables. He walks up to them and says 'Can I join you?'"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search