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: 100262 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) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

ashatimjohn SSC Veteran Points: 219 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: 1000539 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)
Intro to Tally Tables and Functions

ScottPletcher SSC Guru Points: 100262 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) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!