Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

What will happen? Expand / Collapse
Author
Message
Posted Wednesday, September 1, 2010 7:07 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, August 29, 2014 12:18 PM
Points: 2,818, Visits: 2,561
Good question. More than anything, QoTD seems to reinforce.....be explicit.
Post #978719
Posted Wednesday, September 1, 2010 7:17 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:28 PM
Points: 5,984, Visits: 8,242
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
Post #978728
Posted Wednesday, September 1, 2010 7:45 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 8:06 AM
Points: 2,055, Visits: 2,046
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));

Post #978768
Posted Wednesday, September 1, 2010 8:06 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, November 16, 2012 10:40 AM
Points: 12, Visits: 39
? 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]
Post #978795
Posted Wednesday, September 1, 2010 8:21 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 8:36 AM
Points: 2,361, Visits: 2,702
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)


-------------------
"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
Post #978811
Posted Wednesday, September 1, 2010 8:24 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:28 PM
Points: 5,984, Visits: 8,242
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
Post #978815
Posted Wednesday, September 1, 2010 8:26 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:28 PM
Points: 5,984, Visits: 8,242
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
Post #978820
Posted Wednesday, September 1, 2010 8:34 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 8:06 AM
Points: 2,055, Visits: 2,046
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.
Post #978837
Posted Wednesday, September 1, 2010 9:07 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 9:12 AM
Points: 1,676, Visits: 1,755
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
Post #978883
Posted Wednesday, September 1, 2010 9:26 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 8:36 AM
Points: 2,361, Visits: 2,702
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?

Thanks,
webrunner


-------------------
"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
Post #978907
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse