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!!

Convert image to hexadecimal

I want to export data from my tables by generating insert statements,
including data of type image. To avoid having to use textcopy.exe or
textptr, I want to have the image data part of the insert-statements by
converting the binary image data to hexadecimal strings. Also, the
image data is larger then 8000 so a simple convert won't work. How do I
do the conversion from image to hex?As part of the DB Ghost evaluation there is a free scipter component for
scripting databases including data into insert statements. It handles image
and binary data by converting to hexidecimal and has a COM interface which
you can use and distribute freely.
http://www.dbghost.com
"Jacques Roumimper" wrote:

> I want to export data from my tables by generating insert statements,
> including data of type image. To avoid having to use textcopy.exe or
> textptr, I want to have the image data part of the insert-statements by
> converting the binary image data to hexadecimal strings. Also, the
> image data is larger then 8000 so a simple convert won't work. How do I
> do the conversion from image to hex?
>|||If you can use c# do this:
public static string BinToString(Byte[] binValue)
{
char[] hexCode =
{'0','1','2','3','4','5','6','7','8','9'
,'A','B','C','D','E','F'};
StringBuilder sb = new StringBuilder();
foreach (byte b in binValue)
{
sb.Append(Convert.ToString(hexCode[b >> 4]));
sb.Append(Convert.ToString(hexCode[b & 0xF]));
}
return "0x" + sb.ToString();
}
I am not 100% sure if web data administrator
http://www.microsoft.com/downloads/...&displaylang=en
can script image fields. Worth a try!
Mathias|||I need to do this in SQL, what would be the Transact SQL equivalent of
your C# code?