converting a string to a date

[Brian Barrett]

Hy folks

Has anyone got a 'quick' way of converting a string to a date.
I want to do this in an SQL statment rather than via a procedure.

There was no analysis on this field before now and basically went
unmonitored
I have tried using different variations of cast and convert, however there
are several users who have input various different date formats into the
varchar field and not all are valid.

Some were dd/mm/yy hh:mm:ss, some were mm/dd/yyyy hh:mm:ss AM, some were
dd-mm-yy etc. so it needs validation as well.

Thanks in advance
Brian

[BP Margolin]

Brian,

There is no "quick" way of converting strings to dates in the case you
present because what does 03/01/2001 represent? Is it March 1 2001 or is it
Jan 3 2001. If YOU can't tell, then SQL Server (which is dumber than you)
can't tell either.

Nevertheless, there is an IsDate built-in function that will "validate"
strings to see if they represent valid dates ... but beware that IsDate is
language setting dependent ... so the default SQL Server language of
us_english will reject a date of '30/01/2001' while a language setting of
Italiano (for example) will accept the date.

-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.

"Brian Barrett" <brian_m...@hotmail.com> wrote in message
news:d3Qg7.33227$Zv3.81832@NewsReader...

[Brian Barrett]

There are only a few users at present (depending on which site 3-5)
On further investigation, each one has a specific date format and there is a
field specifying which user has created the date
I can therefore tell what format each is in

e.g Barry = dd/yy/mm hh:mm:ss AM
Thomas = mm-dd-yy hh:mm:ss

But is there some way perhaps using a case statement to convert each
differently with SQL

Sorry if this sound elementary
Brian

I can identify the format each user has used
"BP Margolin" <bpm...@attglobal.net> wrote in message
news:3b844...@news3.prserv.net...

[BP Margolin]

Brian,

Thanks for the additional information.

create table Brian
(
c1 char(10) NOT NULL,
c2 char(3) NOT NULL
check (c2 in ('mdy', 'dmy'))
)
go

insert into Brian values ('01/03/2001', 'mdy')
insert into Brian values ('01/03/2001', 'dmy')

select case c2
when 'mdy' then convert(datetime, c1, 110)
when 'dmy' then convert(datetime, c1, 103)
end
from Brian

Check out the section "Writing International Transact-SQL Statements" for
additional information.

-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.

"Brian Barrett" <brian_m...@hotmail.com> wrote in message

news:ub3h7.34918$Zv3.90187@NewsReader...