Converting DateTime to SqlDateTime format

Hi,

I have a function that generates a range of DateTimes, which I then cast to SqlDateTime to compare with SqlDateTime values in a database.

The problem is my converted DateTimes come out in this type of format "6/2/2006 12:00:00 AM"

wheras my SqlDateTimes in the database are in this format "2006-01-18T00:00:00.0000000-12:00"

Any ideas how I can convert the DateTime values to SqlDateTime correctly so that I can compare them? As I said I tried creating a new SqlDateTime object with the DateTime value ie

DateTime dt = new DateTime("");

SqlDateTime sdt = new SqlDateTime(dt);

But that doesn't work correctly, its still not in the format that is in the database.

Assuming you are using the datetime datatype, the format that it is in the database is not"2006-01-18T00:00:00.0000000-12:00". From Books Online, the database actually stores datetime values as two 4-byte integers. The first 4 bytes store the number of days before or after the base date: January 1, 1900. The other 4 bytes store the time of day represented as the number of milliseconds after midnight.

So, the"2006-01-18T00:00:00.0000000-12:00" is just an output representation of that value.

If you give us a little more information about what you are trying to do we should be better able to help you.

converting columns in an INSERT (was "SQL Query")

I'm trying to create an Insert query and I'm having difficulty in 2 areas:

First, I would like to CAST/CONVERT a single column of the several columns in the tables below. Is it possible to retain the asterisk identifying all columns and single out a particular column to be converted as opposed to writing out each individual column in both the INSERT and SELECT statements? I would like to CONVERT the column "MILL_COST" from VARCHAR(50) to Money.

INSERT INTO ITEM_MASTER
SELECT *
FROM ITEM_MASTER_TEMP

Second, I've tried the following"conversions" in the SELECT statement, to no avail:

CONVERT(Money, MILL_COST) As MILL_COST
CONVERT(Money, CONVERT(Varchar(50), MILL_COST)
CAST(MILL_COST AS Money)

Any pointers much appreciated...First of all, I'd strongly suggest that you list out the columns. That solves all kinds of problems before they get a chance to happen to you. If you are determined to do things the hard way, you don't have to enumerate the columns yourself, but I'd still recommend it.

You ought to be able to use any of those conversions if you like, but as long as the contents of the column can be converted to MONEY, the SQL Server engine ought to handle the conversion for you.

-PatP|||"SELECT *" is shorthand for "I'm a lazy programmer". I would never leave it in any finished code. Bad. Bad. Bad bad code.

Converting @myString to @myInt

Here's a fun one. BOL says that using CONVERT or CAST to change a string into a number won't work.

The following "works" and I'm assuming it is because the compiler can verify that the string value is in fact a number, even with the Unicode N:

declare @.col2 int

select @.col2 = convert (int, N'12345')

======================================

Here's what I need to work, or I need a creative work-around if someone has an interesting idea:

declare @.col2 int,
@.myString nvarchar(100)

select @.col2 = '12345'
select @.myString = convert(int, @.col2)

Msg 245, Level 16, State 1, Line 67
Conversion failed when converting the nvarchar value '12345' to data type int.

I'm assuming that since the compiler cannot pre-determine the value of @.col2 that it won't allow this.

I understand the responsibility to ensure that the converted string is actually a number. I can put something together as a pre-check for this.

Any suggestions or creative work-arounds would be helpful!

Thanks!

Doug B

> declare @.col2 int,

> @.myString nvarchar(100)

>

> select @.col2 = '12345'

> select @.myString = convert(int, @.col2)

>

> Msg 245, Level 16, State 1, Line 67

> Conversion failed when converting the nvarchar value '12345' to data

> type int.

This works fine for me on SQL Server 2000 (8.00.2151) and SQL Server 2005

(9.00.1399).

A

|||Thanks so much...my example was an attempt to simplify the question, so the code wasn't "exactly" what I was running. Once I realized it should have worked, I found a bug in a substring elsewhere.

Looks like you can convert from a string to a number after all, as long as it is a valid number.

Thanks!

DB|||

> Thanks so much...my example was an attempt to simplify the question,

Don't do that. The people here who are trying to help do not need it.

Whether your SQL is 4 lines or 40, we are still going to cut & paste into QA

or SSMS and run it, and then we will find the actual problem instead of

wasting time asking for more information...

converting (casting) from decimal(24,4) to decimal(21,4) data type problem

Hello!

I would like to cast (convert) data type decimal(24,4) to

decimal(21,4). I could not do this using standard casting function

CAST(@.variable as decimal(21,4)) or CONVERT(decimal(21,4),@.variable)

because of the following error: "Arithmetic overflow error converting

numeric to data type numeric." Is that because of possible loss of the

value?

Thanks for giving me any advice,

Ziga

What was the value? For a value that fits in both, you should have no issue:

declare @.value decimal(24,4)
set @.value = 10.12

select cast(@.value as decimal(21,4))
go

But if the non-fractional value is too large for the 21,4 datatype, it will go boom:

declare @.value decimal(24,4)
set @.value = 12345678901234567890.1234

select @.value

select cast(@.value as decimal(21,4))

12345678901234567890.1234

Msg 8115, Level 16, State 8, Line 6
Arithmetic overflow error converting numeric to data type numeric.

If this isn't the case, then post the code that fails, the value, and the results of:

select @.@.version

I tried this on the following versions:

Microsoft SQL Server 2000 - 8.00.679 (Intel X86)
Aug 26 2002 15:09:48
Copyright (c) 1988-2000 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

Microsoft SQL Server 2005 - 9.00.2153.00 (Intel X86)
May 8 2006 22:41:28
Copyright (c) 1988-2005 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

|||Yes the non fraction value is to large... So there is no way to solve this problem?|||What is the result that you expect when you try to cast a larger value? CAST will throw error for overflow and that is the only expected behavior. If you want to fit larger values into smaller data type then you need to truncate the value yourself or perform other logic. You could use CASE expression to check for the larger values and conditionally perform cast. Also, take a look at ROUND function. You could use it instead of CAST for the larger values or for the whole conversion.|||

Thanks!

Actually I am performing some mappings between two systems. Interface for the destination system has specification of the field as decimal(21,4). In source system the (calculated) value is larger - decimal(24,4). For those large values where the cast (to decimal(21,4)) is not possible (arithemtic overflow) performing correct mapping is just not possible...

Thakns a lot,

Ziga

|||You still haven't answered how you would like to handle the larger values. Do you simply throw those away? What would it mean to store a truncated result in the database? And if you use it later then you are going to make wrong assumptions. It seems like your table schema is wrong and if you want to retain the higher precision values you need to modify the schema to match the source or vice versa. Otherwise, you will have to use round or truncate the value yourself before inserting and you cannot use CAST.

Convert/Cast from Varchar to decimal

guys, i've got a quick question on uploading a .txt or .csv file to a table
in sql server 2000.
i had been using a Bulk Insert to a dummy table where all columns are varcha
r.
then selecting that table and running it thru a for each loop in an asp.net
application and attempting the conversion there just using Cdbl(datarow.Item
(0))
or Cdec(datarow.Item(0)).
is there a better way to do this right on the database itself?
because it seems like somewhere an implicit rounding is occuring so i'll get
55.00 where 55.50 should be.
the problem i think is the inconsistant values, but, i thought i'd ask the r
eal experts.
as some of the values in the .csv (formerly .xls) file are 33.02, some are w
hole numbers 234 and even others are 55.5.
ive tried using Cast(Amount as Decimal(5,2)) in an insert statement and the
consistant error i get is "Arithmentic overflow converting numeric to data t
ype numeric"
if anyone has any suggestions, i'd really appreciate it.
thanks again
rik
****************************************
******************************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET
resources...Did you try altering that column in the dummy table to numeric(5, 2) instead
varchar?
AMB
"rik butcher" wrote:

> guys, i've got a quick question on uploading a .txt or .csv file to a tabl
e in sql server 2000.
> i had been using a Bulk Insert to a dummy table where all columns are varc
har.
> then selecting that table and running it thru a for each loop in an asp.ne
t
> application and attempting the conversion there just using Cdbl(datarow.It
em(0))
> or Cdec(datarow.Item(0)).
> is there a better way to do this right on the database itself?
> because it seems like somewhere an implicit rounding is occuring so i'll g
et 55.00 where 55.50 should be.
> the problem i think is the inconsistant values, but, i thought i'd ask the
real experts.
> as some of the values in the .csv (formerly .xls) file are 33.02, some ar
e whole numbers 234 and even others are 55.5.
> ive tried using Cast(Amount as Decimal(5,2)) in an insert statement and th
e consistant error i get is "Arithmentic overflow converting numeric to data
type numeric"
> if anyone has any suggestions, i'd really appreciate it.
> thanks again
> rik
> ****************************************
******************************
> Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
> Comprehensive, categorised, searchable collection of links to ASP & ASP.NE
T resources...
>|||Why are all the columns of your dummy table all varchar? Assuming that
the data is clean, you can create the target table with a column of the
desired decimal type and have nothing else to do about conversion.
As far as the error you're seeing with CAST, it indicates that there is
a value in the [Amount] column that represents a value too big for
decimal(5,2), in other words, greater than 999.99 or less than -999.99.
Again assuming that the data is clean, CAST should work if the target
type can hold the values. You'll get a different error if you have non-
numeric strings, like 'abc':
Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.
I don't know the specifications of the Cdbl and Cdec functions, so I won't
comment on why some values seem to be changed more than rounding
should cause.
Steve Kass
Drew University
rbutch@.coair.com wrote:

>guys, i've got a quick question on uploading a .txt or .csv file to a table
in sql server 2000.
>i had been using a Bulk Insert to a dummy table where all columns are varch
ar.
>then selecting that table and running it thru a for each loop in an asp.net
>application and attempting the conversion there just using Cdbl(datarow.Ite
m(0))
>or Cdec(datarow.Item(0)).
>is there a better way to do this right on the database itself?
>because it seems like somewhere an implicit rounding is occuring so i'll ge
t 55.00 where 55.50 should be.
>the problem i think is the inconsistant values, but, i thought i'd ask the
real experts.
> as some of the values in the .csv (formerly .xls) file are 33.02, some are
whole numbers 234 and even others are 55.5.
>ive tried using Cast(Amount as Decimal(5,2)) in an insert statement and the
consistant error i get is "Arithmentic overflow converting numeric to data
type numeric"
>if anyone has any suggestions, i'd really appreciate it.
>thanks again
>rik
> ****************************************
******************************
>Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
>Comprehensive, categorised, searchable collection of links to ASP & ASP.NET
resources...
>|||thanks guys. you both were right. using numeric(5,2) worked but i still had
to widen that column again [that's what the overflow message was referring t
o] - so, i dont have to use a dummy table. and bcp bulk insert is working li
ke a charm.
i just had to look thru the actual values and there it was.
sometimes its the simplest things - and i appreciate you guys setting me str
aight on this.
thanks again
rik
****************************************
******************************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET
resources...

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 statement - Invalid Column Name

Hi All,
After digging out some information on using the CONVERT and CAST
functions to convert a string to a date, I am still having a problem
that I do not understand?
In an attempt to convert a Legacy format for a date e.g. '19960905'
imported into MS SQL Server into a correctly sequenced and delimetered
data '05/09/1996' I am using the following statement for two columns in
my table. Both receive the following exception messages
Column Name = 'DATE_ORIGINAL'
SELECT CONVERT(char(10), CAST(CAST(DATE_ORIGINAL AS char(8)) AS
datetime), 103)
MS SQL Server throws the following exception:
'Invalid column name 'DATE_ORIGINAL'
Column Name = 'DATE_RESCHEDULED'
SELECT CONVERT(char(10), CAST(CAST(DATE_RESCHEDULED AS char(8)) AS
datetime), 103)
MS SQL Server throws the following exception:
'Invalid column name 'DATE_RESCHEDULED'
I have triple checked both column names and they are both correct. Does
anyone know why these errors occur when I try to run this statement?
Does the use of the word DATE have something to do with it perhaps?
Many Thanks
rohanIf these are the entire select statements, you are
forgetting to include the FROM table name - such as
SELECT CONVERT(char(10), CAST(CAST(DATE_ORIGINAL AS char(8))
AS datetime), 103)
FROM YourTable
-Sue
On 25 Sep 2006 02:18:14 -0700, "red vertigo"
<rohankinsella@.hotmail.com> wrote:
>Hi All,
>After digging out some information on using the CONVERT and CAST
>functions to convert a string to a date, I am still having a problem
>that I do not understand?
>In an attempt to convert a Legacy format for a date e.g. '19960905'
>imported into MS SQL Server into a correctly sequenced and delimetered
>data '05/09/1996' I am using the following statement for two columns in
>my table. Both receive the following exception messages
>Column Name = 'DATE_ORIGINAL'
>SELECT CONVERT(char(10), CAST(CAST(DATE_ORIGINAL AS char(8)) AS
>datetime), 103)
>MS SQL Server throws the following exception:
>'Invalid column name 'DATE_ORIGINAL'
>Column Name = 'DATE_RESCHEDULED'
>SELECT CONVERT(char(10), CAST(CAST(DATE_RESCHEDULED AS char(8)) AS
>datetime), 103)
>MS SQL Server throws the following exception:
>'Invalid column name 'DATE_RESCHEDULED'
>I have triple checked both column names and they are both correct. Does
>anyone know why these errors occur when I try to run this statement?
>Does the use of the word DATE have something to do with it perhaps?
>Many Thanks
>rohan|||Hi Sue,
Thankyou very much indeed for replying to my post.
Can't believe I left that part off my statement (lol).
This statement works correctly now.
Your help is greatly appreciated.
rohan
Sue Hoegemeier wrote:
> If these are the entire select statements, you are
> forgetting to include the FROM table name - such as
> SELECT CONVERT(char(10), CAST(CAST(DATE_ORIGINAL AS char(8))
> AS datetime), 103)
> FROM YourTable
> -Sue
> On 25 Sep 2006 02:18:14 -0700, "red vertigo"
> <rohankinsella@.hotmail.com> wrote:
> >Hi All,
> >After digging out some information on using the CONVERT and CAST
> >functions to convert a string to a date, I am still having a problem
> >that I do not understand?
> >
> >In an attempt to convert a Legacy format for a date e.g. '19960905'
> >imported into MS SQL Server into a correctly sequenced and delimetered
> >data '05/09/1996' I am using the following statement for two columns in
> >my table. Both receive the following exception messages
> >
> >Column Name = 'DATE_ORIGINAL'
> >SELECT CONVERT(char(10), CAST(CAST(DATE_ORIGINAL AS char(8)) AS
> >datetime), 103)
> >
> >MS SQL Server throws the following exception:
> >
> >'Invalid column name 'DATE_ORIGINAL'
> >
> >Column Name = 'DATE_RESCHEDULED'
> >SELECT CONVERT(char(10), CAST(CAST(DATE_RESCHEDULED AS char(8)) AS
> >datetime), 103)
> >
> >MS SQL Server throws the following exception:
> >
> >'Invalid column name 'DATE_RESCHEDULED'
> >
> >I have triple checked both column names and they are both correct. Does
> >anyone know why these errors occur when I try to run this statement?
> >
> >Does the use of the word DATE have something to do with it perhaps?
> >
> >Many Thanks
> >
> >rohan|||Hi Rohan
Please note that '19960905' is not in a legacy format. It is actually a
recommended format when working with date data in SQL Server because it is
completely unambiguous. Your desired format of '05/09/1996' could hardly be
called 'correctly sequenced and delimetered (')' for everyone, since
different people use different delimiters, like dashes or dots, and it is
ambiguous depending on the local. In the US, '05/09/1996' is interpreted as
May 9th. So if '05/09/1996' is what your users are hoping for, that is
fine and at some point in the client app you'll probably want to give it to
them. But just be careful.
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"red vertigo" <rohankinsella@.hotmail.com> wrote in message
news:1159175894.377984.228190@.h48g2000cwc.googlegroups.com...
> Hi All,
> After digging out some information on using the CONVERT and CAST
> functions to convert a string to a date, I am still having a problem
> that I do not understand?
> In an attempt to convert a Legacy format for a date e.g. '19960905'
> imported into MS SQL Server into a correctly sequenced and delimetered
> data '05/09/1996' I am using the following statement for two columns in
> my table. Both receive the following exception messages
> Column Name = 'DATE_ORIGINAL'
> SELECT CONVERT(char(10), CAST(CAST(DATE_ORIGINAL AS char(8)) AS
> datetime), 103)
> MS SQL Server throws the following exception:
> 'Invalid column name 'DATE_ORIGINAL'
> Column Name = 'DATE_RESCHEDULED'
> SELECT CONVERT(char(10), CAST(CAST(DATE_RESCHEDULED AS char(8)) AS
> datetime), 103)
> MS SQL Server throws the following exception:
> 'Invalid column name 'DATE_RESCHEDULED'
> I have triple checked both column names and they are both correct. Does
> anyone know why these errors occur when I try to run this statement?
> Does the use of the word DATE have something to do with it perhaps?
> Many Thanks
> rohan
>|||Hi Karen,
Interesting. Thanks for the information.
rohan
Kalen Delaney wrote:
> Hi Rohan
> Please note that '19960905' is not in a legacy format. It is actually a
> recommended format when working with date data in SQL Server because it is
> completely unambiguous. Your desired format of '05/09/1996' could hardly be
> called 'correctly sequenced and delimetered (')' for everyone, since
> different people use different delimiters, like dashes or dots, and it is
> ambiguous depending on the local. In the US, '05/09/1996' is interpreted as
> May 9th. So if '05/09/1996' is what your users are hoping for, that is
> fine and at some point in the client app you'll probably want to give it to
> them. But just be careful.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> http://sqlblog.com
>
> "red vertigo" <rohankinsella@.hotmail.com> wrote in message
> news:1159175894.377984.228190@.h48g2000cwc.googlegroups.com...
> > Hi All,
> > After digging out some information on using the CONVERT and CAST
> > functions to convert a string to a date, I am still having a problem
> > that I do not understand?
> >
> > In an attempt to convert a Legacy format for a date e.g. '19960905'
> > imported into MS SQL Server into a correctly sequenced and delimetered
> > data '05/09/1996' I am using the following statement for two columns in
> > my table. Both receive the following exception messages
> >
> > Column Name = 'DATE_ORIGINAL'
> > SELECT CONVERT(char(10), CAST(CAST(DATE_ORIGINAL AS char(8)) AS
> > datetime), 103)
> >
> > MS SQL Server throws the following exception:
> >
> > 'Invalid column name 'DATE_ORIGINAL'
> >
> > Column Name = 'DATE_RESCHEDULED'
> > SELECT CONVERT(char(10), CAST(CAST(DATE_RESCHEDULED AS char(8)) AS
> > datetime), 103)
> >
> > MS SQL Server throws the following exception:
> >
> > 'Invalid column name 'DATE_RESCHEDULED'
> >
> > I have triple checked both column names and they are both correct. Does
> > anyone know why these errors occur when I try to run this statement?
> >
> > Does the use of the word DATE have something to do with it perhaps?
> >
> > Many Thanks
> >
> > rohan
> >

convert scientific notation to to decimal

I am importing a bunch of data which is all in varchar format which I cast into the relevant type on transfer to the production table. One of these columns contains decimals, but some of them are occasionally given in scientific notation (3.48E-02).
I am using

CAST (v1 AS decimal(18, 13))

to do the conversion, but that seems to be unable to handle the scientific notation. Does anyone know a way around this?Try converting to float first, like this:
CAST (CAST (v1 AS float(24)) AS decimal(18, 13))

Convert or cast HexaDecimal to Bigint

Hi ,

I have a hexadecimal string value. I want to convert it to Bigint in Sql Server 2005.

The Hexadecimal value is '0x000000000000000F'.

How is it possible to convert into Bigint.

Please help me

Thanks in advance

Srinivas

SELECT CONVERT(bigint, 0x000000000000000F)|||

There are many ways to convert a string with hexadecimal value to integer value. Below is one technique using a table of numbers:

declare @.t varchar (30)
select @.t = '000000000000000F'
select sum(
case lower( substring( reverse(@.t), number , 1 ) )
when '0' then 0
when '1' then 1
when '2' then 2
when '3' then 3
when '4' then 4
when '5' then 5
when '6' then 6
when '7' then 7
when '8' then 8
when '9' then 9
when 'a' then 10
when 'b' then 11
when 'c' then 12
when 'd' then 13
when 'e' then 14
when 'f' then 15
when 'A' then 10
when 'B' then 11
when 'C' then 12
when 'D' then 13
when 'E' then 14
when 'F' then 15
end * power( cast(16 as bigint), number - 1 )
)
from Numbers n
where number between 1 and len( @.t )
go

Note that it doesn't handle conversions into negative bigint values but that can be done easily.

|||

Hi ,

I think directly using Cast or Convert will not work.

The Bigint value corresponding to Hexadecimal value ('0x000000000000000F') is 1000000002.

I require a function which would take Hexadecimal value as input parameters and return me a big int value.

Thanks for the help.

Srinivas Govada

|||Then create a user-defined function with the code given by Umachandar and pass the hex string to that function and get the calculated bigint value as the return value from the function.|||

Hi

What does number refer to and what is there in table Numbers.

Please provide structure of table numbers and records in table.

Regards,

Srinivas Govada

Convert ntext to nvarchar for ORDER BY?

I need to sort by an ntext field, but it won't let me do it.

However, if I cast the field as nvarchar(100), I can use ORDER BY on that.

Is there any reason that this is a bad idea? In my testing, ordering by a converted ntext field was actually *faster* than ordering by an nvarchar (same data in the fields).

Joshntext is for huge chunks of data (up to 2 gigabytes)

if you can easily sort on the first 100 characters, i would suggest that you do so

i can't see that people are going to notice the difference in sort order beyond the 100th character if in fact two 2-gigabyte fields are identical in the first 100 characters

and why sort on 2 gigabytes if you don't have to?|||It doesn't inherently guarantee uniqueness but you might be able to live with that. You wouldn't be able make use of an index on this column.|||100 characters is plenty for my needs - I just need to sort the recordset for display to the user. Only the first 100 characters will be displayed in the list, anyway.

I'm just wondering if there's a performance penalty due to the conversion, or anything like that.

Thanks!
Josh|||ou will be sorting on a non-indexed column which will be a performance issue.|||Ok, thanks. These columns are user-customizable, so I don't think we will be using indexes on them. Are there any other issues that I need to worry about?

Josh

Convert int to float

Hi,
why does converting integer to float take so long? Its a column with about 5 Million rows.
I want to avoid cast(inumber1 as float) / cast(inmuber2 as float), thats why converting them. Queries should be a bit faster after that.. hope so :)
Thanks a lotWhat method are you using to convert?
How long is so long?

Your last sentence doesn't make a lot of sense - if you're converting datatypes during your select statements that's adding processing time to your query, so surely it will not make them faster.

In fact, please post the SQL statement in question as well :)|||First of all, converting anything to float is a "computationally noisy" operation... It means a lot of work for the CPU no matter how you do it, just because of how the FLOAT datatype is stored.

My preference for doing what you've described is peculiar, but it works well for me... I would try using:SELECT 1e0 * inumber1 / inmuber2-PatP|||Thanks a lot Pat, that's a nice trick!|||What method are you using to convert?
How long is so long?

It was Alter Table A Alter Column C float NOT NULL and took about 20 Minutes.

if you're converting datatypes during your select statements that's adding processing time to your query, so surely it will not make them faster.

That's why I did the Alter Command. I just wondered why its such a big thing from int to float|||for very large tables, it probably would be faster to export the table to a flat file using bcp, then reimport to a different table with the new datatype, again with bcp.

I tend to shy away from ALTER TABLE.|||As I see it, there is three main reasons for this operation to take take quite a bit of time:

First, creating the float value requires processing power. However, this does not explain how updating a table with 5M records takes so long.

Second, A float takes 8 bytes, whereas int takes 4 bytes. So, depending on the width of the table, you may experience page splits (There is no longer room for all the rows in the page, so some rows have to be moved to a new page). I'm pretty sure this will be especially bad if there is a clustered index on the table.

And last, if this is done in an active environment, keep in mind that you'll require exclusive schema lock on the table, so your command will have to wait until a point in time when there is no activity against the table.

So, finally... I don't think queries will run any faster. You'll probably end up having more data pages, and I'm pretty sure that the negative impact from this is of greater magnitude than the cast from int to float.|||Wow, that was a really detailed comment :-) thank you

As I already have converted, does it make any sense to reconvert to integer regarding the negative impact of greater magnitude?|||int to float

What the hell for?

There's no precision in int. Are you looking for precision? Then use decimal.

Let me ask...does anyone use float or real, and if so what for?

I gotta do a poll|||Have you ever divided two integer values?|||int to float

What the hell for?

There's no precision in int. Are you looking for precision? Then use decimal.

Let me ask...does anyone use float or real, and if so what for?

I gotta do a pollI think you mean "There's no precision in float". And a float is more precise when you do complex math (the sort of complex I don't really understand frankly). Michael Valentine Jones did a good proof on SQLTeam. I'm sure Pat could explain too. I've got a link to a page that goes into this in depth but have never had chance to read it yet.
EDIT - linky: http://docs.sun.com/source/806-3568/ncg_goldberg.html

Anyway - you are right though - for most purpses you would prefer decimal over floats.|||Have you ever divided two integer values?You get an integer return unless you explicitly cast or do what Pat did. Did you seriously get a performance improvement starting off with floats?|||Correct me, if I'm wrong, but float takes less space than decimal, doesn't it?|||You get an integer return unless you explicitly cast or do what Pat did. Did you seriously get a performance improvement starting off with floats?
Not really, but 5M rows is still pretty small. Frankly, I thought one of you could give me that answer ;-)|||BOL

float and real
Approximate number data types for use with floating point numeric data. Floating point data is approximate; not all values in the data type range can be precisely represented.

Decimal

Precision Storage bytes
1 - 9 5
10-19 9
20-28 13
29-38 17

bigint

Integer (whole number) data from -2^63 (-9223372036854775808) through 2^63-1 (9223372036854775807). Storage size is 8 bytes.

int

Integer (whole number) data from -2^31 (-2,147,483,648) through 2^31 - 1 (2,147,483,647). Storage size is 4 bytes. The SQL-92 synonym for int is integer.

smallint

Integer data from -2^15 (-32,768) through 2^15 - 1 (32,767). Storage size is 2 bytes.

tinyint

Integer data from 0 through 255. Storage size is 1 byte.

float [ ( n ) ]

Is a floating point number data from - 1.79E + 308 through 1.79E + 308. n is the number of bits used to store the mantissa of the float number in scientific notation and thus dictates the precision and storage size. n must be a value from 1 through 53.

So How many bits in a byte...I think it's 8...but bol doesn't say what the default for float alone is

Looks like in EM in table design that float defaults to 8 bytes, so that would be half of what int is|||Queries should be a bit faster after that..

Ok, so this is you stated goal

supply us with DDL and the query that's "slow"

data type conversions are not where I would start for Perf Tuning|||exsqueeze me - shows my naivite of the subject. I think rather than floating point data types I should have said floating point calculations.

But in any event, if you want an accurate representation of the division of two integers a float is probably the wrong route.|||excuse me if I am wrong but I think one of the major drawbacks of floats is that you are not always guaranteed reproduceable results. I might be wrong about this.|||Ok - I found the link.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=71391
It is really a display issue when you use a float. The problem is that float is internally a binary representation of the number, and the binary representation cannot always be converted directly to a decimal representation that is exactly equivalent. Float is intended for scientific applications where computational accuracy is the most important requirement.
My point was that Decimal is not always better (read more accurate) than Float. I probably should have made the point better & more succinctly though since in this case it is certainly better to use decimal.|||that's a great quote, pootle, thanks for following that up

better to use decimal? that depends on what kind of data you're storing

if it's money, yes :)

and pat will tell you it had better be DECIMAL(n,4), not 2 -- and then rounded by the front end if desired|||Have you ever divided two integer values?yup, and if i don't want a whole number answer, i use pat's trick, although mine is just slightly different:select 1.0 * col1 / col2:)

Convert hexadecimal value to real data type

Hi ,

I want to convert hexadecimal to numeric data type. Using directly Cast or Convert function is not working.

Please suggest an alternative how to retrieve the numeric value of binary data .

Thanks in advance

Regards

Srinivas Govada

Srinivas,

Please show us what does not work for you. The following is

fine:

declare @.n numeric(10,2)

set @.n = 1.23

select cast(@.n as varbinary(20))

-- returns 0x0A0200017B000000

select cast(0x0A0200017B000000 as numeric(10,2))

If you are trying to convert binary representations of

[float] values to [float], you can use this code:

declare @.b binary(8)

set @.b = 0xC094D954FB549F95

declare @.s bit

declare @.e smallint

declare @.m float

set @.s = case when substring(@.b,1,1) >= 0x80 then 1 else 0 end

set @.e = (substring(@.b,1,2)&32752)/16-1022

set @.m = cast(substring(@.b,6,3) as int)/2097152e0/536870912e0

+ (substring(@.b,2,4)&268435455)/536870912e0+0.5e0

select case when @.s = 1 then -1e0 else 1e0 end * @.m * power(2e0,@.e)

Steve Kass

Drew University

www.stevekass.com

Srinivas Govada@.discussions.microsoft.com wrote:

> Hi ,

>

> I want to convert hexadecimal to numeric data type. Using directly Cast

> or Convert function is not working.

>

> Please suggest an alternative how to retrieve the numeric value of

> binary data .

>

> Thanks in advance

>

> Regards

>

> Srinivas Govada

>

>

>

>

Convert float to char

Select Cast('100.1234' as float)
give me the result 100.1234
Now when I convert it back to char I want exactly 100.1234
Select Convert(char(100),Cast('100.1234' as float))
Gives me 100.123 (Here I was expecting 100.1234)

When I do
Select STR(Cast('100.1234' as float),25,4)
I get back the result as 100.1234

However here I am not sure how many digits do I have after the decimal
point. If I put some value like
Select STR(Cast('100.1234' as float),25,8)
I get 0's appended to it, which is again not desired.

Thanks in advance,
Jai
jaijai_kumar@.hotmail.com wrote:

> Select Cast('100.1234' as float)
> give me the result 100.1234
> Now when I convert it back to char I want exactly 100.1234
> Select Convert(char(100),Cast('100.1234' as float))
> Gives me 100.123 (Here I was expecting 100.1234)
> When I do
> Select STR(Cast('100.1234' as float),25,4)
> I get back the result as 100.1234
> However here I am not sure how many digits do I have after the decimal
> point. If I put some value like
> Select STR(Cast('100.1234' as float),25,8)
> I get 0's appended to it, which is again not desired.
>
> Thanks in advance,
> Jai

Hi Jai. I want to warn you about going down this path.
You will never get exactly what you want all the time,
because there is a base converson from base 10 to binary,
and there are lots of distressingly simple-seeming
decimal numbers that do not convert exactly to a binary
float. Then converting back will show the loss by giving
a different value. I suggest using a DECIMAL datatype
if you want to retain exactitude...

Joe Weinstein at BEA Systems|||(jaijai_kumar@.hotmail.com) writes:
> Select Cast('100.1234' as float)
> give me the result 100.1234
> Now when I convert it back to char I want exactly 100.1234
> Select Convert(char(100),Cast('100.1234' as float))
> Gives me 100.123 (Here I was expecting 100.1234)
> When I do
> Select STR(Cast('100.1234' as float),25,4)
> I get back the result as 100.1234
> However here I am not sure how many digits do I have after the decimal
> point. If I put some value like
> Select STR(Cast('100.1234' as float),25,8)
> I get 0's appended to it, which is again not desired.

Since a float is an approximate number, this is not any exact science.
Consider:

SELECT cast('100.1235' as float)

this gives in Query Analyzer:

100.12350000000001

So when you convert it to string, which value do you want?

Anyway, here is a horrible expression that achieves what you are looking
for. But note the caveate above, and be aware that you may not always
get what you want.

Select reverse(substring(x, patindex('%[^0]%', x), 25))
from (Select x = reverse(ltrim(str(Cast('100.1235' as float), 25, 8)))) y

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Here is another horrible expression, that does the same thing:

select replace(rtrim(replace(ltrim(str(Cast('100.1235' as float),
25, 8)),'0',' ')),' ','0')

Razvan|||Hello all,

Thanks a lot for all your messages.
Erland your work around gave me a good hint.
I have created a small function to do the same.

Jai
jaijai_kumar@.hotmail.com wrote:
> Select Cast('100.1234' as float)
> give me the result 100.1234
> Now when I convert it back to char I want exactly 100.1234
> Select Convert(char(100),Cast('100.1234' as float))
> Gives me 100.123 (Here I was expecting 100.1234)
> When I do
> Select STR(Cast('100.1234' as float),25,4)
> I get back the result as 100.1234
> However here I am not sure how many digits do I have after the decimal
> point. If I put some value like
> Select STR(Cast('100.1234' as float),25,8)
> I get 0's appended to it, which is again not desired.
>
> Thanks in advance,
> Jai

Convert Char Value to Datetime Value

I need to convert a char value to datetime value. Example from 71004 to
7/10/04; I already tried to use the convert function and the cast function
but none of them work.
Thanks
Datetime values are stored in a proprietary format in SQL Server.
I would suggest a two part scheme to convert that value... Step 1 is to
parse it and put the hyphens or slashes in to the string. Once that is
done, you can use the convert command to make it a datetime stamp.
Step 1: Using string manipulation, change 71004 to 07-10-04
Step 2: Convert '07-01-04' to datetime.
HTH
Rick Sawtell
MCT, MCSD, MCDBA
"sanvaces" <sanvaces@.discussions.microsoft.com> wrote in message
news:976BC321-FDB3-4F47-918F-7BD6DBD51CD2@.microsoft.com...
> I need to convert a char value to datetime value. Example from 71004 to
> 7/10/04; I already tried to use the convert function and the cast function
> but none of them work.
> Thanks
>
|||If you don't use 6-char representation of date, you can have problems with
those dates, where day<13 (21104 is 2.11.04 or 21.1.04?).
On MS SQL you can use convert(datetime,'02.11.04',104) to convert date
from/to different formats. For ddmmyy format I don't know it by head, it's
probably convert(datetime,'021104',112).
Vlastik
"sanvaces" <sanvaces@.discussions.microsoft.com> pe v diskusnm pspvku
news:976BC321-FDB3-4F47-918F-7BD6DBD51CD2@.microsoft.com...
> I need to convert a char value to datetime value. Example from 71004 to
> 7/10/04; I already tried to use the convert function and the cast function
> but none of them work.
> Thanks
>
|||Sorry, of course day<10.
When string representation is ddmmyy, you can use also
substring(yourdate,1,2)+'/'+substring(yourdate,3,2)+'/'+substring(yourdate,5
,2).
Vlastik
[vbcol=seagreen]
> If you don't use 6-char representation of date, you can have problems with
> those dates, where day<13 (21104 is 2.11.04 or 21.1.04?).
> On MS SQL you can use convert(datetime,'02.11.04',104) to convert date
> from/to different formats. For ddmmyy format I don't know it by head, it's
> probably convert(datetime,'021104',112).
> Vlastik
> "sanvaces" <sanvaces@.discussions.microsoft.com> pe v diskusnm pspvku
> news:976BC321-FDB3-4F47-918F-7BD6DBD51CD2@.microsoft.com...
function
>

Convert Char Value to Datetime Value

I need to convert a char value to datetime value. Example from 71004 to
7/10/04; I already tried to use the convert function and the cast function
but none of them work.
ThanksDatetime values are stored in a proprietary format in SQL Server.
I would suggest a two part scheme to convert that value... Step 1 is to
parse it and put the hyphens or slashes in to the string. Once that is
done, you can use the convert command to make it a datetime stamp.
Step 1: Using string manipulation, change 71004 to 07-10-04
Step 2: Convert '07-01-04' to datetime.
HTH
Rick Sawtell
MCT, MCSD, MCDBA
"sanvaces" <sanvaces@.discussions.microsoft.com> wrote in message
news:976BC321-FDB3-4F47-918F-7BD6DBD51CD2@.microsoft.com...
> I need to convert a char value to datetime value. Example from 71004 to
> 7/10/04; I already tried to use the convert function and the cast function
> but none of them work.
> Thanks
>|||If you don't use 6-char representation of date, you can have problems with
those dates, where day<13 (21104 is 2.11.04 or 21.1.04?).
On MS SQL you can use convert(datetime,'02.11.04',104) to convert date
from/to different formats. For ddmmyy format I don't know it by head, it's
probably convert(datetime,'021104',112).
Vlastik
"sanvaces" <sanvaces@.discussions.microsoft.com> pí¹e v diskusním pøíspìvku
news:976BC321-FDB3-4F47-918F-7BD6DBD51CD2@.microsoft.com...
> I need to convert a char value to datetime value. Example from 71004 to
> 7/10/04; I already tried to use the convert function and the cast function
> but none of them work.
> Thanks
>|||Sorry, of course day<10.
When string representation is ddmmyy, you can use also
substring(yourdate,1,2)+'/'+substring(yourdate,3,2)+'/'+substring(yourdate,5
,2).
Vlastik
> If you don't use 6-char representation of date, you can have problems with
> those dates, where day<13 (21104 is 2.11.04 or 21.1.04?).
> On MS SQL you can use convert(datetime,'02.11.04',104) to convert date
> from/to different formats. For ddmmyy format I don't know it by head, it's
> probably convert(datetime,'021104',112).
> Vlastik
> "sanvaces" <sanvaces@.discussions.microsoft.com> pí¹e v diskusním pøíspìvku
> news:976BC321-FDB3-4F47-918F-7BD6DBD51CD2@.microsoft.com...
> > I need to convert a char value to datetime value. Example from 71004 to
> > 7/10/04; I already tried to use the convert function and the cast
function
> > but none of them work.
> >
> > Thanks
> >
>