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 12»»

char to datetime! Expand / Collapse
Author
Message
Posted Tuesday, August 18, 2009 8:36 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Tuesday, July 23, 2013 6:34 AM
Points: 654, Visits: 265
Comments posted to this topic are about the item char to datetime!
Post #773171
Posted Tuesday, August 18, 2009 11:22 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, July 29, 2014 9:49 PM
Points: 1,507, Visits: 2,536
Good question. Though it's simple, was not sure if it's implicit or explicit.


Post #773200
Posted Wednesday, August 19, 2009 2:30 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 1:10 AM
Points: 1,539, Visits: 8,136
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
Blog Site
Post #773283
Posted Wednesday, August 19, 2009 7:23 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, January 2, 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
Posted Wednesday, August 19, 2009 7:30 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 7:45 AM
Points: 3,877, Visits: 3,622
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
Posted Wednesday, August 19, 2009 7:30 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 1:10 AM
Points: 1,539, Visits: 8,136
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
Blog Site
Post #773484
Posted Wednesday, August 19, 2009 7:34 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 7:45 AM
Points: 3,877, Visits: 3,622
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
Posted Wednesday, August 19, 2009 7:42 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, July 24, 2014 10:26 AM
Points: 77, Visits: 166
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
Posted Wednesday, August 19, 2009 8:16 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, July 24, 2014 10:26 AM
Points: 77, Visits: 166
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
Posted Wednesday, August 19, 2009 8:34 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 7:45 AM
Points: 3,877, Visits: 3,622
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 »

Add to briefcase 12»»

Permissions Expand / Collapse