What will happen?

Question

Post reply

What will happen?

Kaifi

SSC Journeyman

Points: 79
More actions
August 31, 2010 at 10:50 pm

#387709

Comments posted to this topic are about the item What will happen?

Viewing 15 posts - 1 through 15 (of 26 total)

You must be logged in to reply to this topic. Login to reply

ziangij SSCrazy Eights Points: 8066 More actions · Answer 1

DECLARE @a INT

DECLARE @b-2 DATETIME

SET @b-2 = @a

implicit conversion from int to datetime works fine 🙂

tommyh SSCertifiable Points: 6302 More actions · Answer 2

tommyh

SSCertifiable

Points: 6302

September 1, 2010 at 12:02 am

#1214734

:Whistling:

Hardy21 SSCrazy Eights Points: 9708 More actions · Answer 3

Hardy21

SSCrazy Eights

Points: 9708

September 1, 2010 at 12:38 am

#1214743

Good Question. Thanks

Thanks

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

thanks for the question

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Michael Riemer SSCertifiable Points: 5190 More actions · Answer 5

Michael Riemer

SSCertifiable

Points: 5190

September 1, 2010 at 1:37 am

#1214776

Thanks for the question!:-)

Tushar Kanti Ten Centuries Points: 1145 More actions · Answer 6

@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-2 DATETIME

set @a= getdate()

SET @b-2 = @a

Thanks,

Tushar

Saurabh Dwivedy SSCommitted Points: 1887 More actions · Answer 7

ziangij (8/31/2010)
DECLARE @a INT DECLARE @b-2 DATETIME SET @b-2 = @a
implicit conversion from int to datetime works fine 🙂

Is this dependent on the sql server version?

Saurabh Dwivedy
___________________________________________________________

My Blog: http://tinyurl.com/dwivedys

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537

Be Happy!

Mike Is Here Hall of Fame Points: 3348 More actions · Answer 8

This is how we get the date for the current day without time element

DECLARE @a INT

DECLARE @b-2 DATETIME

set @b-2 = GETDATE()-.5

SET @a = CONVERT(INT,@b)

SELECT CONVERT(DATETIME, @a), @b-2

the -.5 is half a day because CONVERT(INT,@b) rounds

Dave62 SSCertifiable Points: 7459 More actions · Answer 9

mbova407 (9/1/2010)
This is how we get the date for the current day without time element
DECLARE @a INT
DECLARE @b-2 DATETIME
set @b-2 = GETDATE()-.5
SET @a = CONVERT(INT,@b)
SELECT CONVERT(DATETIME, @a), @b-2
the -.5 is half a day because CONVERT(INT,@b) rounds

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

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

Daniel Bowlin SSC-Dedicated Points: 34566 More actions · Answer 10

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

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 11

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/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Dave62 SSCertifiable Points: 7459 More actions · Answer 12

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 Old Hand Points: 380 More actions · Answer 13

? 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-2 = '08/31/2010 08:08:08 AM'

set @a = convert(int, @b-2)

print @a [result = 40419]

set @b-2 = convert(datetime, @a)

print @b-2 [result = Aug 31 2010 12:00 AM]

webrunner SSC-Dedicated Points: 31862 More actions · Answer 14

DECLARE @a INT

DECLARE @b-2 DATETIME

SET @a = @b-2

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)

Personal Edition on Windows NT 5.1 (Build 2600: Service Pack 3)

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