Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ValterBorges
Master Smack Fu Yak Hacker
1429 Posts |
 Posted - 2003-01-30 : 15:01:04
|
| Assuming I have the following possibilities:GLDATE"" "01192003"Can anyone spot an improvement in the following statementCAST(STUFF(STUFF(REPLACE(GLDATE,'"',''),3,0,'/'),6,0,'/') AS DATETIME)RESULTNULL2003-01-19 00:00:00.000Edited by - ValterBorges on 01/30/2003 15:06:06 |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-01-30 : 15:26:12
|
| How about:Select Case When GLDATE In ('', or Null) Then Null When IsDate(Substring(GLDATE,1,2) + '/' + Substring(GLDATE,3,2) + '/' + Substring(GLDATE,5,4)) Then Convert(DateTime,Substring(GLDATE,1,2) + '/' + Substring(GLDATE,3,2) + '/' + Substring(GLDATE,5,4),121) ELSE 0 - SQL Server Default Data, since datatypes have to be the same or Null END |
 |
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2003-01-30 : 15:30:25
|
| CAST(STUFF(STUFF(REPLACE(GLDATE,'"',''),3,0,'/'),6,0,'/') AS DATETIME) Improve it? I've centered it and made it bold.</end of humor>Not sure what you're concerned about. It looks straightforward to me.Sam |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-01-30 : 15:42:07
|
| Wont you get an error if GLDATE Does not contain a valid dattime string representation? |
|
|
|
ValterBorges
Master Smack Fu Yak Hacker
1429 Posts |
Posted - 2003-01-30 : 16:23:41
|
| Well I really wanted the date column data in iso format so the cast is implicit and I'm working on getting it that way. I'm trying to improve the performance on the time it takes to import 300,000+ and process them there are lots of columns and 5 dates all in that format.Plus, it never hurts to learn something new.It converts non-dates to NULL. Also since this is comming from another database then converted to flat file and then emailed if there was a problem in the importing process it rolls back and I would be notified by the dts task and ask the sender to fix the file formatting on their end.I guess I should be happy after all I've reduced the process from 4 minutes to 5 seconds.Thanks for the comments.Edited by - ValterBorges on 01/30/2003 16:36:17 |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-01-30 : 17:10:45
|
| 4 minutes to 5 seconds....wow that's huge....Thanks for all the great stuffBrett8-) |
|
|
|
|
|
|
|
|
Date String Conversion