SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by VM / char to datetime! / Latest Posts

RE: char to datetime!

kalyani.k478 — Fri, 02 Nov 2012 04:20:48 GMT

[quote][b]Toreador (8/19/2009)[/b][hr][quote][b]Cliff Jones (8/19/2009)[/b][hr][quote][b]Mark Horninger (8/19/2009)[/b][hr]Actually, you could do an implicit or explicit conversion.... [/quote]I agree. That was my first thought as well.[/quote]Me too. Since there was no 'either implicit or explicit' option, I got it right by a 50% guess :-)[/quote]me 2:)

RE: char to datetime!

rja.carnegie — Thu, 29 Oct 2009 07:46:44 GMT

Incidentally I recently created a view around OPENQUERY() - data coming from a linked server. Which may have been not the best way to do what I was doing. But anyway, I thought it [i]could[/i] be useful to name and CAST to type the columns of the OPENQUERY results - that is, to express explicitly outside the OPENQUERY term the expected columns. But is that worthwhile, or does it perhaps even create a problem?I haven't been doing the problems lately because I realised if I pass on one that's too hard it doesn't count in the game, and my score looks better than it is. Like [u]Who Wants to Be a Millionaire[/u], I can look at the question and decide to ignore it and keep what I've got. Maybe the score system should keep count of that. I mean, I also don't want to guess, particularly on a point I don't currently understand but may need to at a later date.

RE: char to datetime!

L' Eomot Inversé — Wed, 28 Oct 2009 14:09:28 GMT

[quote][b]Christopher Stobbs (8/24/2009)[/b][hr]I agree with the last two posts. I got it wrong cause I can't mind read.I can think of many examples where the cast is not implicit...[/quote]Well, I disagree with those two posts and with you. Is the conversion from tinyint to int implicit, explicit, or forbidden? It's implicit. But as far as I can see you are telling me that because it is perfectly valid to write declare @integer int declare @octet tinyint select @octet = 0 select @integer = cast (@octet as int)the conversion from tinyint to int is explicit. Well, I think that's a very strange point of view.The descriptions in BOL seem to me quite clear. In fact there's even a nice matrix showing which conversions are implicit, which explicit, and which forbidden. It's been there since SQL 2000. It's still there in SQL 2008 R2. [url=http://msdn.microsoft.com/en-us/library/ms187928%28SQL.105%29.aspx][/url]

RE: char to datetime!

Christopher Stobbs — Mon, 24 Aug 2009 09:14:15 GMT

I agree with the last two posts. I got it wrong cause I can't mind read.I can think of many examples where the cast is not implicit...

RE: char to datetime!

Rob Goddard — Thu, 20 Aug 2009 06:52:31 GMT

[quote][b]GSquared (8/19/2009)[/b][hr]The question doesn't actually ask what the author thinks.I got it right, but only by trying to outthink the author.None of the answers given were actually sequitur to the question.[/quote]Exactly what I thought when I was staring at the answers, frustrated that I had to take a wild stab in the dark before being able to post... Unfortunately I wasn't successful in my attempt to outthink the author.

RE: char to datetime!

GSquared — Wed, 19 Aug 2009 13:32:27 GMT

The question doesn't actually ask what the author thinks.I got it right, but only by trying to outthink the author.None of the answers given were actually sequitur to the question.

RE: char to datetime!

Toreador — Wed, 19 Aug 2009 10:11:56 GMT

[quote][b]Cliff Jones (8/19/2009)[/b][hr][quote][b]Mark Horninger (8/19/2009)[/b][hr]Actually, you could do an implicit or explicit conversion.... [/quote]I agree. That was my first thought as well.[/quote]Me too. Since there was no 'either implicit or explicit' option, I got it right by a 50% guess :-)

RE: char to datetime!

Cliff Jones — Wed, 19 Aug 2009 08:34:17 GMT

Implicit type conversions have been shown to work differently from one SQL Server version to another. We had a question recently that exposed this behavior. We witnessed it as well when we ported our application from SQL 2000 to SQL 2005. I don't recall the exact circumstances so this is anecdotal.

RE: char to datetime!

rja.carnegie — Wed, 19 Aug 2009 08:16:46 GMT

[quote][b]Cliff Jones (8/19/2009)[/b][hr]If you rely on implicit conversion, is it going to convert the CHAR to DATETIME or DATETIME to CHAR? You are never really sure so you explicitly convert the side of the equation that has the least impact.[/quote]You're not sure if you haven't memorised the precedence order of data types. :-) If you suspect colleagues haven't memorised them, you do explicit CAST or CONVERT (I think CAST is ANSI SQL)...In an expression, including an equality or inequality test, one data type is elevated to match the other. So IF ( 'abc' < N'def' ) is tested in Unicode characters - which usually doesn't make a difference. Collation may, but don't look at me there :-) I hope datetime ranks above char because I've been using the likes of " WHERE (date > '2009-04-15') " for ages. But there isn't one char expression of a date, anyway (I just said). The one you're mainly liable to try that won't work is such as 'A' + 1, where presumably you want 'A1'. There I use STR(), sometimes REPLACE(STR(...), ' ', '0').In an assignment (SET x = y), of course conversion is from the type of expression y to the type of column/variable/whatever, x.

RE: char to datetime!

rja.carnegie — Wed, 19 Aug 2009 07:42:48 GMT

Is there a way to specify a constant date/time that [i]isn't[/i]" SET @date = '2009-08-19' " ?(Or "CONVERT" from the date encoding of your choice, I suppose. Or "SET @date = 'Aug 19, 09' is fine.)DATEADD(day, (19-1), DATEADD(month, (8-1), DATEADD(year, (2009-1900), 0))) gives the same result, but you wouldn't, would you?

RE: char to datetime!

Cliff Jones — Wed, 19 Aug 2009 07:34:58 GMT

If you rely on implicit conversion, is it going to convert the CHAR to DATETIME or DATETIME to CHAR? You are never really sure so you explicitly convert the side of the equation that has the least impact.

RE: char to datetime!

BrainDonor — Wed, 19 Aug 2009 07:30:11 GMT

Yes, but my understanding (recently!) is that if you use CAST/CONVERT or any other functions on a date column that is indexed, the index is not utilised, making for a slower query.There's a lot of code here that does that and I believe it isn't needed.

RE: char to datetime!

Cliff Jones — Wed, 19 Aug 2009 07:30:09 GMT

[quote][b]Mark Horninger (8/19/2009)[/b][hr]Actually, you could do an implicit or explicit conversion.... [/quote]I agree. That was my first thought as well.

RE: char to datetime!

SuperDBA-207096 — Wed, 19 Aug 2009 07:23:11 GMT

Actually, you could do an implicit or explicit conversion.... 'implicit'SET @DateTimeVariable = @CharVariableContainingDateStringvs.'explicit'SET @DateTimeVariable = convert(datetime,@CharVariableContainingDateString)

RE: char to datetime!

BrainDonor — Wed, 19 Aug 2009 02:30:07 GMT

This actually answers a question that has been bugging me for a while. Where I work now they always use CAST/CONVERT on dates going to/from Char, but at my previous job they never bothered with it and it all seemed to work fine.Thank you - a simple question, but I've learned something.

RE: char to datetime!

Joy Smith San — Tue, 18 Aug 2009 23:22:17 GMT

Good question. Though it's simple, was not sure if it's implicit or explicit.

char to datetime!

VM-723206 — Tue, 18 Aug 2009 20:36:31 GMT

Comments posted to this topic are about the item [B]char to datetime![/B]