August 24, 2015 at 2:51 pm
Here's my substring script:
I want dob to be replaced with value of Date.
So, how can I use replace and substring together?
select DOB,substring(DOB,5,4)+SUBSTRING(DOB,1,2)+SUBSTRING(DOB,3,2)as Date,ID
from TblNAme
WHERE ISDATE([DOB]) != 1
and DOB is not null
and DOB <>''
Result:
dOB Date ID
0301198119810301503
August 24, 2015 at 3:28 pm
UPATE TblNAme
SET DOB = substring(DOB,5,4)+SUBSTRING(DOB,1,2)+SUBSTRING(DOB,3,2)
WHERE ISDATE([DOB]) != 1
and DOB is not null
and DOB <>''
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
August 24, 2015 at 7:34 pm
Just an added tip...
This...
and DOB is not null
and DOB <>''
... is the same as this...
and DOB > ''
... and can sometimes be a fair bit faster. It works because NULL cannot be compared and will, therefor, fail the single test.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 25, 2015 at 7:44 am
Also note that
SUBSTRING(DOB,1,2)+SUBSTRING(DOB,3,2)
is the same as
SUBSTRING(DOB,1,4)
I don't know if it will make a noticeable difference in performance, and I don't have the time to test it right now.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 25, 2015 at 8:02 am
Thank you all.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply