<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
Article Discussions
»
Article Discussions by Author
»
Discuss content posted by VM
»
char to datetime!
17 posts, Page 1 of 2
1
2
»»
char to datetime!
Rate Topic
Display Mode
Topic Options
Author
Message
VM-723206
VM-723206
Posted Tuesday, August 18, 2009 8:36 PM
SSChasing Mays
Group: General Forum Members
Last Login: Saturday, April 06, 2013 12:20 AM
Points: 649,
Visits: 263
Comments posted to this topic are about the item
char to datetime!
Post #773171
Joy Smith San
Joy Smith San
Posted Tuesday, August 18, 2009 11:22 PM
Ten Centuries
Group: General Forum Members
Last Login: Yesterday @ 6:42 AM
Points: 1,370,
Visits: 2,285
Good question. Though it's simple, was not sure if it's implicit or explicit.
Post #773200
BrainDonor
BrainDonor
Posted Wednesday, August 19, 2009 2:30 AM
Ten Centuries
Group: General Forum Members
Last Login: Yesterday @ 7:47 AM
Points: 1,400,
Visits: 6,886
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.
BrainDonor
Linkedin
Post #773283
SuperDBA-207096
SuperDBA-207096
Posted Wednesday, August 19, 2009 7:23 AM
UDP Broadcaster
Group: General Forum Members
Last Login: Wednesday, January 02, 2013 12:15 PM
Points: 1,443,
Visits: 711
Actually, you could do an implicit or explicit conversion....
'implicit'
SET @DateTimeVariable = @CharVariableContainingDateString
vs.
'explicit'
SET @DateTimeVariable = convert(datetime,@CharVariableContainingDateString)
Post #773475
Cliff Jones
Cliff Jones
Posted Wednesday, August 19, 2009 7:30 AM
Hall of Fame
Group: General Forum Members
Last Login: Tuesday, May 14, 2013 4:11 PM
Points: 3,390,
Visits: 3,403
Mark Horninger (8/19/2009)
Actually, you could do an implicit or explicit conversion....
I agree. That was my first thought as well.
Post #773483
BrainDonor
BrainDonor
Posted Wednesday, August 19, 2009 7:30 AM
Ten Centuries
Group: General Forum Members
Last Login: Yesterday @ 7:47 AM
Points: 1,400,
Visits: 6,886
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.
BrainDonor
Linkedin
Post #773484
Cliff Jones
Cliff Jones
Posted Wednesday, August 19, 2009 7:34 AM
Hall of Fame
Group: General Forum Members
Last Login: Tuesday, May 14, 2013 4:11 PM
Points: 3,390,
Visits: 3,403
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.
Post #773490
rja.carnegie
rja.carnegie
Posted Wednesday, August 19, 2009 7:42 AM
SSC Journeyman
Group: General Forum Members
Last Login: Monday, May 16, 2011 5:32 PM
Points: 76,
Visits: 151
Is there a way to specify a constant date/time that
isn't
" 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?
Post #773497
rja.carnegie
rja.carnegie
Posted Wednesday, August 19, 2009 8:16 AM
SSC Journeyman
Group: General Forum Members
Last Login: Monday, May 16, 2011 5:32 PM
Points: 76,
Visits: 151
Cliff Jones (8/19/2009)
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.
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.
Post #773554
Cliff Jones
Cliff Jones
Posted Wednesday, August 19, 2009 8:34 AM
Hall of Fame
Group: General Forum Members
Last Login: Tuesday, May 14, 2013 4:11 PM
Points: 3,390,
Visits: 3,403
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.
Post #773575
« Prev Topic
|
Next Topic »
17 posts, Page 1 of 2
1
2
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
