COnvert

22 Dec 2005 8:47 AM

Phil

Hi All,

I posted this query a coupe of days ago and had a few repiies but I have
only just got around to trying them out, I was trying to convert a nvarchar
values of 01112005 which this time I will explain is meant to be the !st of
November 2005, this is something that I forgot to mention last time, I am
trying to convert this to a datetime type so that I can perform some checks
and deletes based on specific dates, I was advised to convert the date to a
yyyymmdd type, but no matter how I right the convert statement the date stays
the same, I was using a statement like

convert(nvarchar(10),column_A,112)

This is really starting to drive me nuts as I have read lots of articles
about conversions and nothing I seem to do alters the data in anyway, am I
doing something wrong.

Thanks for any help anyone can yet again offer.

Phil

22 Dec 2005 9:03 AM

Tibor Karaszi

ddmmyy is not a valid datetime format in SQL Server. So, do some string parsing to build a valid
datetime format. Something like:

CAST(SUBSTRING(column_A, 4, 5) + SUBSTRING(column_A, 2, 3) + SUBSTRING(column_A, 2, 1) AS datetime)

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/

Show quote

"Phil" <P***@discussions.microsoft.com> wrote in message
news:CD78C5BE-FCE2-4BD5-B5F7-9280C20D440C@microsoft.com...
> Hi All,
>
> I posted this query a coupe of days ago and had a few repiies but I have
> only just got around to trying them out, I was trying to convert a nvarchar
> values of 01112005 which this time I will explain is meant to be the !st of
> November 2005, this is something that I forgot to mention last time, I am
> trying to convert this to a datetime type so that I can perform some checks
> and deletes based on specific dates, I was advised to convert the date to a
> yyyymmdd type, but no matter how I right the convert statement the date stays
> the same, I was using a statement like
>
> convert(nvarchar(10),column_A,112)
>
> This is really starting to drive me nuts as I have read lots of articles
> about conversions and nothing I seem to do alters the data in anyway, am I
> doing something wrong.
>
> Thanks for any help anyone can yet again offer.
>
> Phil

22 Dec 2005 9:21 AM

ML

Tibor, I believe there's a flaw in your substrings. The proper syntax is:

SUBSTRING ( expression, start, length )

And you seem to have:

SUBSTRING ( expression, length, start )

ML

---
http://milambda.blogspot.com/

22 Dec 2005 9:30 AM

Phil

Hi All,

Thanks again for the very quick replies, on your advice I have finally got
it sorted and I have checked that it returns the results that I wanted before
posting back on here.

Thanks again to one and all, Phil

Show quote

"ML" wrote:

> Tibor, I believe there's a flaw in your substrings. The proper syntax is:
>
> SUBSTRING ( expression, start, length )
>
> And you seem to have:
>
> SUBSTRING ( expression, length, start )
>
>
> ML
>
> ---
> http://milambda.blogspot.com/

22 Dec 2005 9:03 PM

Tibor Karaszi

There I have it from not running my code before posting. Thanks :-)

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/

Show quote

"ML" <M*@discussions.microsoft.com> wrote in message
news:6CB5C492-C087-4C65-92EB-6D381F877D61@microsoft.com...
> Tibor, I believe there's a flaw in your substrings. The proper syntax is:
>
> SUBSTRING ( expression, start, length )
>
> And you seem to have:
>
> SUBSTRING ( expression, length, start )
>
>
> ML
>
> ---
> http://milambda.blogspot.com/