Extract date from a text field

Question

Post reply

Extract date from a text field

Viewing 4 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply

ScottPletcher SSC Guru Points: 101101 More actions · Answer 1

I still don't get your insistence on the guid being so always great as the clustering key for a table.

As a stand-alone table, maybe, IF all the rows inserted were relatively the same width (which is not typical).

For a parent table, no way, since the child table(s) would then not work well at all for INSERTs.

Overall, it seems only in very isolated cases would this approach work.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

ashatimjohn SSC Veteran Points: 247 More actions · Answer 2

Here is the answer I came up with. It's works for me. Thanks 🙂

select

*,

LTRIM(RTRIM(REPLACE(REPLACE(IIF(LTRIM(RTRIM(RIGHT(SUBSTRING([Time Client Called], CHARINDEX('on',[Time Client Called]), 15),13))) is null, null, LTRIM(RTRIM(RIGHT(SUBSTRING([Time Client Called], CHARINDEX('on',[Time Client Called]), 15),13)))), 'a', ''),'t',''))) as "Date",

IIF(LTRIM(RTRIM(RIGHT(SUBSTRING([Time Client Called], CHARINDEX('at',[Time Client Called]), 11),8))) is null, null, LTRIM(RTRIM(RIGHT(SUBSTRING([Time Client Called], CHARINDEX('at',[Time Client Called]), 11),8)))) as "Time"

into #tmpCalls

FROM #tmpPattern

--select * from #tmpPattern

select *,

CASE WHEN

ISDATE(Date + ' ' + Time) > 0 THEN CONVERT(datetime, (Date + ' ' + Time))

ELSE null

END

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 3

ScottPletcher wrote:

I still don't get your insistence on the guid being so always great as the clustering key for a table.
As a stand-alone table, maybe, IF all the rows inserted were relatively the same width (which is not typical).
For a parent table, no way, since the child table(s) would then not work well at all for INSERTs.
Overall, it seems only in very isolated cases would this approach work.

It's not that I'm saying it's good. I'm saying that fragmentation isn't the problem even for INSERTs and certainly not for UPDATEs. It's actually the very epitome of what and how people think and index should operate as. In a test that I've done, I inserted 100,000 rows for 58 simulated days with < 1% fragmentation an no index maintenance. In the process, I also lay waste to what people have mistakenly calling the "Best Practice" for Index Maintenance.

You've obviously not seen my presentation on that.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ScottPletcher SSC Guru Points: 101101 More actions · Answer 4

Jeff Moden wrote:

ScottPletcher wrote:
I still don't get your insistence on the guid being so always great as the clustering key for a table.
As a stand-alone table, maybe, IF all the rows inserted were relatively the same width (which is not typical).
For a parent table, no way, since the child table(s) would then not work well at all for INSERTs.
Overall, it seems only in very isolated cases would this approach work.
It's not that I'm saying it's good. I'm saying that fragmentation isn't the problem even for INSERTs and certainly not for UPDATEs. It's actually the very epitome of what and how people think and index should operate as. In a test that I've done, I inserted 100,000 rows for 58 simulated days with < 1% fragmentation an no index maintenance. In the process, I also lay waste to what people have mistakenly calling the "Best Practice" for Index Maintenance.
You've obviously not seen my presentation on that.

Page splits is the issue. You'd have page splits throughout the entire table.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".