cleaning different formats

I have a column "currentDate" with approx 4 million rows.
The rows are composed of dates , but with a variety of formats.
For example:
"14-05-2005 14:56:32"
"20/07/2005 10:26:43"
"2006-03-26 11:21:42"
I've decided to convert all the rows into a standard format i.e dd-mm-yyyy
hh:mm:ss, and then convert the column into a datetime
The problem I have is that I am using DATEPART , which is fine , except that
I am losing the leading zero.
Taking "02/03/2005 10:26:43" as an example ,
when I do the following:
CAST(DATEPART(dd,myday) AS VARCHAR(2)) the result = "2" as opposed to "02"
,
How can I retain the leading "0" , for any DATEPART.?>I have a column "currentDate" with approx 4 million rows.
> The rows are composed of dates , but with a variety of formats.
> For example:
> "14-05-2005 14:56:32"
> "20/07/2005 10:26:43"
> "2006-03-26 11:21:42"
> I've decided to convert all the rows into a standard format i.e dd-mm-yyyy
Bravo! But that is very far from a standard format!
Why wasn't this a datetime in the first place? What application is storing
all these different formats? Are you allowing end users to just enter
whatever format they want?
Assuming these are the only three formats present, this might give you some
ideas:
SELECT CONVERT(CHAR(10), d, 120)+'T'+CONVERT(CHAR(8), d, 108)
FROM
(
SELECT d = CASE WHEN ISDate(d)=1 THEN CONVERT(DATETIME, d)
ELSE CONVERT(DATETIME, d, 103) END
FROM
(
SELECT d = '14-05-2005 14:56:32'
UNION ALL SELECT '20/07/2005 10:26:43'
UNION ALL SELECT '2006-03-26 11:21:42'
) a
) b
However, I am betting there are twenty other formats you're not mentioning.
A|||You may try this:
select right('0' + CAST(DATEPART(dd, myday) AS VARCHAR(2)), 2)
Perayu
"Jack Vamvas" <delete_this_bit_jack@.ciquery.com_delete> wrote in message
news:dtv3e8$kdf$1@.nwrdmz03.dmz.ncs.ea.ibs-infra.bt.com...
>I have a column "currentDate" with approx 4 million rows.
> The rows are composed of dates , but with a variety of formats.
> For example:
> "14-05-2005 14:56:32"
> "20/07/2005 10:26:43"
> "2006-03-26 11:21:42"
> I've decided to convert all the rows into a standard format i.e dd-mm-yyyy
> hh:mm:ss, and then convert the column into a datetime
> The problem I have is that I am using DATEPART , which is fine , except
> that
> I am losing the leading zero.
> Taking "02/03/2005 10:26:43" as an example ,
> when I do the following:
> CAST(DATEPART(dd,myday) AS VARCHAR(2)) the result = "2" as opposed to
> "02"
> ,
> How can I retain the leading "0" , for any DATEPART.?
>|||Jack Vamvas (delete_this_bit_jack@.ciquery.com_delete) writes:
> I have a column "currentDate" with approx 4 million rows.
> The rows are composed of dates , but with a variety of formats.
> For example:
> "14-05-2005 14:56:32"
> "20/07/2005 10:26:43"
> "2006-03-26 11:21:42"
> I've decided to convert all the rows into a standard format i.e
> dd-mm-yyyy hh:mm:ss, and then convert the column into a datetime The
> problem I have is that I am using DATEPART , which is fine , except that
> I am losing the leading zero.
> Taking "02/03/2005 10:26:43" as an example ,
> when I do the following:
> CAST(DATEPART(dd,myday) AS VARCHAR(2)) the result = "2" as opposed to
> "02" ,
> How can I retain the leading "0" , for any DATEPART.?
There are a couple of variations on that theme, but it does not seem to
address your real issue anyway.
Obviously you have dateformat of dmy, in which case the format 2006-03-26
is not likely to convert to datetime.
I would suggest that you add a new column to the table, nullable. Then
you would do something like:
UPDATE tbl
SET newcol = convert(datetime, oldcol)
WHERE isdate(oldcol) = 1
go
SET DATEFORMAT ymd
go
UPDATE tbl
SET newcol = convert(datetime, oldcol)
WHERE isdate(oldcol) = 1
AND newcol IS NULL
go
SELECT * FROM tbl WHERE newcol IS NULL
-- Manuallly fix the rest?
go
ALTER TABLE tbl DROP oldcol
go
-- If column should not permit NULL.
ALTER TABLE col ALTER newcol datetime NOT NULL
One thing you would have to make an extra check for are completely far-out
date formats like MM/DD/YY (yes, there are odd corners of the world where
they use this). Not talking of a string like 05/03/02 that has a number of
interpretations.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||The Convert function will do the conversion to DateTime for you. However,
depending on the format, you will need to specify the Style parameter.
Lookup the Convert function in SQL Server Books Online. For example:
-- Italian dd-mm-yy
print convert(datetime,'14-05-2005 14:56:32',105)
May 14 2005 2:56PM
print convert(datetime,'14/05/2005 14:56:32',105)
May 14 2005 2:56PM
-- ANSI
print convert(datetime,'2005-05-14 14:56:32',102)
May 14 2005 2:56PM
Also, going forward, you will need to constrain user input into the
application to a consistent format.
"Jack Vamvas" <delete_this_bit_jack@.ciquery.com_delete> wrote in message
news:dtv3e8$kdf$1@.nwrdmz03.dmz.ncs.ea.ibs-infra.bt.com...
>I have a column "currentDate" with approx 4 million rows.
> The rows are composed of dates , but with a variety of formats.
> For example:
> "14-05-2005 14:56:32"
> "20/07/2005 10:26:43"
> "2006-03-26 11:21:42"
> I've decided to convert all the rows into a standard format i.e dd-mm-yyyy
> hh:mm:ss, and then convert the column into a datetime
> The problem I have is that I am using DATEPART , which is fine , except
> that
> I am losing the leading zero.
> Taking "02/03/2005 10:26:43" as an example ,
> when I do the following:
> CAST(DATEPART(dd,myday) AS VARCHAR(2)) the result = "2" as opposed to
> "02"
> ,
> How can I retain the leading "0" , for any DATEPART.?
>|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:#yCoM86OGHA.2912@.tk2msftngp13.phx.gbl...
dd-mm-yyyy
> Bravo! But that is very far from a standard format!
> Why wasn't this a datetime in the first place? What application is
storing
> all these different formats? Are you allowing end users to just enter
> whatever format they want?
> Assuming these are the only three formats present, this might give you
some
> ideas:
> SELECT CONVERT(CHAR(10), d, 120)+'T'+CONVERT(CHAR(8), d, 108)
> FROM
> (
> SELECT d = CASE WHEN ISDate(d)=1 THEN CONVERT(DATETIME, d)
> ELSE CONVERT(DATETIME, d, 103) END
> FROM
> (
> SELECT d = '14-05-2005 14:56:32'
> UNION ALL SELECT '20/07/2005 10:26:43'
> UNION ALL SELECT '2006-03-26 11:21:42'
> ) a
> ) b
> However, I am betting there are twenty other formats you're not
mentioning.
> A
>
>
>
Thanks for the response.
Just to clarify , this data is inherited from a client . It is historical
data , and the different formats represent different pahases of their data
recording.
They dumped everything into a varchar column and now are requesting
different queries based on the dates.Hence the need to sort this problem
out.
The 3 formats I've presented are the only formats. The code you've presented
has givem me some ideas.|||> I've decided to convert all the rows into a standard format i.e dd-mm-yyyy
> hh:mm:ss, and then convert the column into a datetime
I think you can eliminate this intermediate step. Just convert from the
existing values into DateTime.|||Thanks
That sorted the problem out
"Perayu" <yu.he@.state.mn.us.Remove4Replay> wrote in message
news:uZ#VY86OGHA.1132@.TK2MSFTNGP10.phx.gbl...
> You may try this:
> select right('0' + CAST(DATEPART(dd, myday) AS VARCHAR(2)), 2)
> Perayu
> "Jack Vamvas" <delete_this_bit_jack@.ciquery.com_delete> wrote in message
> news:dtv3e8$kdf$1@.nwrdmz03.dmz.ncs.ea.ibs-infra.bt.com...
dd-mm-yyyy
>|||I was having a similar issue and this fixed my problem as well! Good tip!

Checkpoint causes need for better IO subsystem?

Using Profiler and PerfMon, when there is a checkpoint, the durations of
INSERTS and SELECTS increase to approx 5000ms ... up from 15-30M...which
causes distress for clients and needs to be fixed.
Note that not ALL of the INSERTS and SELECTS are afffected..perhaps during
the 10 seconds that the checkpoint takes place..10% have a duration increase.
It's during pereids of batch inserts that this happens which occurs many
times during the day at odd intervals.. I've written about this before and
someone suggested that the batch inserts take place off-peak. Can't be done.
The nature of the business dictates otherwise.
It's also been suggested that a better IO subsystem be installed. We're
using a 168bit/sec controller card and using PerfMon and tracking data
transfered over all of the hard drives, that during these batch inserts, the
total IO bits/sec is not even half of the 168bit/sec capacity, i.e., the
controller is able to handle the data.
To answer your other question..The MDF, LDF and C: drive are all on their
own physical separate disk drives and have been defragmented. These are huge
130GB drives. There is 4GB of Ram on each server. Dual CPUs at 2396MHZ.
Any Help appreciated.
Don
SQL 2000 SP4
Checkpoints tend to be semi-random writes across the entire database file
footprint. Batch inserts can be sequential or not, depending on whether
your clustered index based on a monotonically increasing column. As such,
the random write capability of the drives comes into play, not the data
throughput limit. Besides, the theoretical limits stated by the
manufacturers are under very narrowly defined conditions. If you believe
manufacturer specs match up to SQL Server usage, I have a bridge I would
like to offer for sale.
Given that you are on a bus architecture disk subsystem, high write activity
can block read activity, this causing your slow response. Five to ten
seconds typically matches the duration of a normal checkpoint. A high-end
disk subsystem with one or more gigabytes of cache and a full-duplex
connection path can help. That translates to a Fibre-Channel connected SAN.
I would also check on the Page Life Expectency performance counter. If it
is low, you may benefit from more physical RAM in the server. This will
allow more data to stay in cache longer, thus eliminating the need to
constantly reload the data from the disks.
Finally, you can change the clustered indexes to use a monotonically
increasing key, thus making the data loads sequential and reducing the
number of page splits, random IO operations, and overall server load during
a data load.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
news:614A712D-3C95-44F7-9D6A-719788FAAC83@.microsoft.com...
> Using Profiler and PerfMon, when there is a checkpoint, the durations of
> INSERTS and SELECTS increase to approx 5000ms ... up from 15-30M...which
> causes distress for clients and needs to be fixed.
> Note that not ALL of the INSERTS and SELECTS are afffected..perhaps during
> the 10 seconds that the checkpoint takes place..10% have a duration
> increase.
> It's during pereids of batch inserts that this happens which occurs many
> times during the day at odd intervals.. I've written about this before and
> someone suggested that the batch inserts take place off-peak. Can't be
> done.
> The nature of the business dictates otherwise.
> It's also been suggested that a better IO subsystem be installed. We're
> using a 168bit/sec controller card and using PerfMon and tracking data
> transfered over all of the hard drives, that during these batch inserts,
> the
> total IO bits/sec is not even half of the 168bit/sec capacity, i.e., the
> controller is able to handle the data.
> To answer your other question..The MDF, LDF and C: drive are all on their
> own physical separate disk drives and have been defragmented. These are
> huge
> 130GB drives. There is 4GB of Ram on each server. Dual CPUs at 2396MHZ.
> Any Help appreciated.
> Don
> SQL 2000 SP4
>
>
|||The Page Life Expectency performance counter hoovers around 850..not sure if
that's good or bad.
There's still 700M of RAM available and SQL's set dynamically to use all 4GB
of RAM if needed. I'm thinking if SQL needed more RAM, it's there for the
taking.
Not sure about how to setup a monotonically increasing key.
Currently, the Clustered index is on multiple cols (2)... (Name, Date)
Would a monotonically increasing key include a new column with an
incrementing sequential value?
such as this?
(newvalue, Name, Date)
don
"Geoff N. Hiten" wrote:

> Checkpoints tend to be semi-random writes across the entire database file
> footprint. Batch inserts can be sequential or not, depending on whether
> your clustered index based on a monotonically increasing column. As such,
> the random write capability of the drives comes into play, not the data
> throughput limit. Besides, the theoretical limits stated by the
> manufacturers are under very narrowly defined conditions. If you believe
> manufacturer specs match up to SQL Server usage, I have a bridge I would
> like to offer for sale.
> Given that you are on a bus architecture disk subsystem, high write activity
> can block read activity, this causing your slow response. Five to ten
> seconds typically matches the duration of a normal checkpoint. A high-end
> disk subsystem with one or more gigabytes of cache and a full-duplex
> connection path can help. That translates to a Fibre-Channel connected SAN.
> I would also check on the Page Life Expectency performance counter. If it
> is low, you may benefit from more physical RAM in the server. This will
> allow more data to stay in cache longer, thus eliminating the need to
> constantly reload the data from the disks.
> Finally, you can change the clustered indexes to use a monotonically
> increasing key, thus making the data loads sequential and reducing the
> number of page splits, random IO operations, and overall server load during
> a data load.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
> news:614A712D-3C95-44F7-9D6A-719788FAAC83@.microsoft.com...
>
>
|||850 is a bit on the low side. 4000-10000 or higher is considered good. As
it is, you are rewriting memory every 14 minutes. Not great.
Identity columns provide monotonically increasing keys. SQL creates a
clustered index out of your primary key by default, but that is not a
requirement. You can separate the two.
Narrow clustered indexes work better. Google the following string for some
excellent articles on clustered index selection and its impact on
performance:
clustered index sql kimberly tripp
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
news:3F381A8B-84FB-4131-917E-24416EA5FE7E@.microsoft.com...[vbcol=seagreen]
> The Page Life Expectency performance counter hoovers around 850..not sure
> if
> that's good or bad.
> There's still 700M of RAM available and SQL's set dynamically to use all
> 4GB
> of RAM if needed. I'm thinking if SQL needed more RAM, it's there for the
> taking.
> Not sure about how to setup a monotonically increasing key.
> Currently, the Clustered index is on multiple cols (2)... (Name, Date)
> Would a monotonically increasing key include a new column with an
> incrementing sequential value?
> such as this?
> (newvalue, Name, Date)
> don
> "Geoff N. Hiten" wrote:
|||donsql22222 (donsql22222@.discussions.microsoft.com) writes:
> There's still 700M of RAM available and SQL's set dynamically to use all
> 4GB of RAM if needed. I'm thinking if SQL needed more RAM, it's there
> for the taking.
Just a check: you have Enterprise Edition? Standard only handles 2GB of
memory.

> Not sure about how to setup a monotonically increasing key.
> Currently, the Clustered index is on multiple cols (2)... (Name, Date)
> Would a monotonically increasing key include a new column with an
> incrementing sequential value?
> such as this?
Name does not sound like it would grow monotonically. :-) Furthermore it
sounds like something I would avoid in a clustred index. Since the
clustered key is also the row-locator in a non-clustered index, a wide
clustered index also make the NC indexes wide and less effecient.
What about the date, is always today's date, or could it be far in
the past? Dates are often good for monotonically clustered indexes.
Of course, there may be other parts of the application that would
perform less well, if there is no clustered index on name.
One alternative is to create the clustered index with a low fill
factor, say 50%. That would create gaps that newly inserted data
can be filled into, and you would thus avoid page splits. This
strategy would require you to routinely rebuild the index, to create
new gaps. I learned this idea from SQL Server MVP Greg Linwood. He
used GUIDs for this, and they are truely random. Nmaes may be less
random and the strategy may work less well for names.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
|||My strategy (stolen from Kimberly) is to create a clustered key from an
identity column. Lest I provoke the Wrath of Celko(tm), I don't actually
use that column anywhere in the application. It is simply to (a) force
insert order at the end of the table, and (b) provide for a very narrow
clustered key for index lookups and index intersection. It is a physical
characteristic only and has no place in my logical data model. Thus, the
Primary Key is materialized by a non-clustered index.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9780EDE041297Yazorman@.127.0.0.1...
> donsql22222 (donsql22222@.discussions.microsoft.com) writes:
> Just a check: you have Enterprise Edition? Standard only handles 2GB of
> memory.
>
> Name does not sound like it would grow monotonically. :-) Furthermore it
> sounds like something I would avoid in a clustred index. Since the
> clustered key is also the row-locator in a non-clustered index, a wide
> clustered index also make the NC indexes wide and less effecient.
> What about the date, is always today's date, or could it be far in
> the past? Dates are often good for monotonically clustered indexes.
> Of course, there may be other parts of the application that would
> perform less well, if there is no clustered index on name.
> One alternative is to create the clustered index with a low fill
> factor, say 50%. That would create gaps that newly inserted data
> can be filled into, and you would thus avoid page splits. This
> strategy would require you to routinely rebuild the index, to create
> new gaps. I learned this idea from SQL Server MVP Greg Linwood. He
> used GUIDs for this, and they are truely random. Nmaes may be less
> random and the strategy may work less well for names.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pro...ads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinf...ons/books.mspx
|||The Kimberly webcast of indexing was great. Thanks.
Now, is this a monotonically increasing indexing scheme that I've created?
Dropped all indexes.
I added a new field of type Indentity, decimal.
I then created a clustered unique index on this field.
I then created a nonclustered index on Name, date.
I'm still showing the problem indicated earlier...during checkpoints, some
large increases in duration of some INSERTS and SELECTS.
If this monotonically increasing that I've created looks correct, I might
just leave it in as it sounds like it has some performance benefits.
Thanks,
Don
"Geoff N. Hiten" wrote:

> My strategy (stolen from Kimberly) is to create a clustered key from an
> identity column. Lest I provoke the Wrath of Celko(tm), I don't actually
> use that column anywhere in the application. It is simply to (a) force
> insert order at the end of the table, and (b) provide for a very narrow
> clustered key for index lookups and index intersection. It is a physical
> characteristic only and has no place in my logical data model. Thus, the
> Primary Key is materialized by a non-clustered index.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
> news:Xns9780EDE041297Yazorman@.127.0.0.1...
>
>
|||Kimberly is an excellent speaker. She is consistantly one of the top if not
the top rated speaker at any conference where she presents.
I usually use int or bigint for identity columns but decimal should be OK.
I like int and bigint for index intersection tuning. The new index
structure should help with caching and table fragmentation. You still may
have an inadequate IO subsystem, but at least your load isn't artifically
increased by a bad indexing scheme. I have had problems with checkpoints
slowing down regular IO before on SCSI disk arrays. RAID level choice will
have a drastic affect on how rapidly the subsystem can absorb data. See if
you can estimate the size of the checkpoint using performance monitor. If
it is over 300 MB or so, you probably will have to go to a SAN to completely
remove the performance hit.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
news:7008FA05-F0A5-4736-9C4D-C95FA19F3BA2@.microsoft.com...[vbcol=seagreen]
> The Kimberly webcast of indexing was great. Thanks.
> Now, is this a monotonically increasing indexing scheme that I've created?
> Dropped all indexes.
> I added a new field of type Indentity, decimal.
> I then created a clustered unique index on this field.
> I then created a nonclustered index on Name, date.
> I'm still showing the problem indicated earlier...during checkpoints, some
> large increases in duration of some INSERTS and SELECTS.
> If this monotonically increasing that I've created looks correct, I might
> just leave it in as it sounds like it has some performance benefits.
> Thanks,
> Don
>
> "Geoff N. Hiten" wrote:
|||Kimberly really is an outstanding presenter! I can't say enough positive
things about her indexing webcast. I'm a believer. I listened to it again,
and will again this afternoon as there's things I pickup each time through.
I'm feeling optimistic. I've put monotonically increasing indexes with
bigint on the identify col on all the tables in the DB..even the small ones
that were just heaps. I've got the LDF and MDF on their own defragged
physical drives. And preliminary tests show that now the highest duration is
approx 300ms during the checkpoint where it was 4000-5000ms for "some" of
the INSERTS before this. So i'm hoping!
There's only 9M records in the testDB so I'll not sure if the behavior will
change with the production size of approx 1.5B records in each of 3 tables.
Will be testing it in the next few days.
btw, the size of the checkpoint is only around 150MG...that led me to think
maybe it's not the IO and that maybe it's an indexing performance issue.
Don
"Geoff N. Hiten" wrote:

> Kimberly is an excellent speaker. She is consistantly one of the top if not
> the top rated speaker at any conference where she presents.
> I usually use int or bigint for identity columns but decimal should be OK.
> I like int and bigint for index intersection tuning. The new index
> structure should help with caching and table fragmentation. You still may
> have an inadequate IO subsystem, but at least your load isn't artifically
> increased by a bad indexing scheme. I have had problems with checkpoints
> slowing down regular IO before on SCSI disk arrays. RAID level choice will
> have a drastic affect on how rapidly the subsystem can absorb data. See if
> you can estimate the size of the checkpoint using performance monitor. If
> it is over 300 MB or so, you probably will have to go to a SAN to completely
> remove the performance hit.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
> news:7008FA05-F0A5-4736-9C4D-C95FA19F3BA2@.microsoft.com...
>
>