Convert/Cast

Hi,
I am trying to convert a nvarchar type date of 01112005 to a datetime type,
I have tired various cast and convert statements and the results come back
but do not let me run any analysis, something as simple as ordering the date
s
would be a start but I cant seem to get that to work. Anyone any ideas or a
m
I doing something really daft which will probably come to me in time.
Thanks!!Try putting in the '/' between month, day and year.
"Phil" <Phil@.discussions.microsoft.com> wrote in message
news:50B539C5-2096-4A6A-AB76-AC91B0E14523@.microsoft.com...
> Hi,
> I am trying to convert a nvarchar type date of 01112005 to a datetime
type,
> I have tired various cast and convert statements and the results come back
> but do not let me run any analysis, something as simple as ordering the
dates
> would be a start but I cant seem to get that to work. Anyone any ideas or
am
> I doing something really daft which will probably come to me in time.
> Thanks!!|||"Phil" <Phil@.discussions.microsoft.com> wrote in message
news:50B539C5-2096-4A6A-AB76-AC91B0E14523@.microsoft.com...
> Hi,
> I am trying to convert a nvarchar type date of 01112005 to a datetime
> type,
> I have tired various cast and convert statements and the results come back
> but do not let me run any analysis, something as simple as ordering the
> dates
> would be a start but I cant seem to get that to work. Anyone any ideas or
> am
> I doing something really daft which will probably come to me in time.
> Thanks!!
First of all, is this November 1st or January 11th?
If it's November 1st... Convert it to a yyyymmdd format and Cast to Date.
Cast(right('01112005', 4) + mid('01112005', 3,2) + left('01112005', 2) as
smalldatetime)|||When converting from a date string in character format to the datetime data
type, the string has to be in the right format. The format 01112005 does not
work, but
20050111 will.
Assuming that the column that had the string you wanted to convert named
date_col, the following select would do the trick:
SELECT CAST(SUBSTRING(date_col,5,8) + SUBSTRING(date_col,1,2) +
SUBSTRING(date_col,3,2) AS datetime)
Other formats, such as 01/11/2005 will work as well.
"Phil" wrote:

> Hi,
> I am trying to convert a nvarchar type date of 01112005 to a datetime type
,
> I have tired various cast and convert statements and the results come back
> but do not let me run any analysis, something as simple as ordering the da
tes
> would be a start but I cant seem to get that to work. Anyone any ideas or
am
> I doing something really daft which will probably come to me in time.
> Thanks!!|||
Mark Williams wrote:
> When converting from a date string in character format to the datetime dat
a
> type, the string has to be in the right format. The format 01112005 does n
ot
> work, but
> 20050111 will.
> Assuming that the column that had the string you wanted to convert named
> date_col, the following select would do the trick:
> SELECT CAST(SUBSTRING(date_col,5,8) + SUBSTRING(date_col,1,2) +
> SUBSTRING(date_col,3,2) AS datetime)
> Other formats, such as 01/11/2005 will work as well.
... if you don't mind confusing November 1, 2005 with January 11, 2005,
or having 01/20/2006 rejected as invalid, even though you think it means
January 20, 2006. ;)
When convering a string to datetime in T-SQL, do one of the following:
1. Use the 'YYYYMMDD' or 'YYYYMMDD HH:MM:SS.fff' format, which is
interpreted consistently.
2. Use the 'YYYY-MM-DDTHH:MM:SS.fff' format, which is also interpreted
consistently, so long as the T is in the string. (To express a date-
only in this format, you must supply a time of midnight.)
3. Use CONVERT with the appropriate format code.
Otherwise, you risk misinterpretation or rejection of your data.
Steve Kass
Drew University
> "Phil" wrote:
>|||In 2005, at least, you can do:
declare @.dts nvarchar(50)
declare @.dt datetime
set @.dts = '01/12/2005'
set @.dt = '01/11/2005'
select @.dt
set @.dt = @.dts
select @.dt
William Stacey [MVP]
"Phil" <Phil@.discussions.microsoft.com> wrote in message
news:50B539C5-2096-4A6A-AB76-AC91B0E14523@.microsoft.com...
> Hi,
> I am trying to convert a nvarchar type date of 01112005 to a datetime
> type,
> I have tired various cast and convert statements and the results come back
> but do not let me run any analysis, something as simple as ordering the
> dates
> would be a start but I cant seem to get that to work. Anyone any ideas or
> am
> I doing something really daft which will probably come to me in time.
> Thanks!!|||Thanks for all the comments, sorry I should of said what order the date was
in, but just incase anyone was curious which I am sure you wouidn't be :-) i
r
was the 1st of November 2005, thanks againe to one and all!
Phil
"Phil" wrote:

> Hi,
> I am trying to convert a nvarchar type date of 01112005 to a datetime type
,
> I have tired various cast and convert statements and the results come back
> but do not let me run any analysis, something as simple as ordering the da
tes
> would be a start but I cant seem to get that to work. Anyone any ideas or
am
> I doing something really daft which will probably come to me in time.
> Thanks!!