|
Author |
Message |
guddu
|
Posted: Thu Dec 22 03:03:21 CST 2005 |
Top |
SQL Server Developer >> COnvert
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
SQL Server190
|
|
|
|
|
Tibor
|
Posted: Thu Dec 22 03:03:21 CST 2005 |
Top |
SQL Server Developer >> COnvert
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/
> 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
|
|
|
|
|
ML
|
Posted: Thu Dec 22 03:21:02 CST 2005 |
Top |
SQL Server Developer >> COnvert
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/
|
|
|
|
|
Phil
|
Posted: Thu Dec 22 03:30:02 CST 2005 |
Top |
SQL Server Developer >> COnvert
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
> 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/
|
|
|
|
|
Tibor
|
Posted: Thu Dec 22 15:03:54 CST 2005 |
Top |
|
|
|
|
|