Converting datetime to integer and back

Hi all,

I have a problem converting datetime to integer (and than back to
datetime).
Depending whether the time is AM or PM, same date is converted to two
different integer representations, which holds as true on reversal
back to datetime.

AM Example:

declare @.DI integer; declare @.DD datetime
set @.DI = cast(cast('3/12/2003 11:34:02 AM' as datetime) as integer)
set @.DD = cast (@.DI as datetime)
print @.DI; print @.DD

Result:
37690
Mar 12 2003 12:00AM

PM Example:

declare @.DI integer; declare @.DD datetime
set @.DI = cast(cast('3/12/2003 11:34:02 PM' as datetime) as integer)
set @.DD = cast (@.DI as datetime)
print @.DI; print @.DD

Result:
37691
Mar 13 2003 12:00AM

Now, this is not a big problem if I knew that this is how it is
supposed to work. Is this how SQL Server is supposed to work?Nikola (nigel35@.hotmail.com) writes:
> AM Example:
> declare @.DI integer; declare @.DD datetime
> set @.DI = cast(cast('3/12/2003 11:34:02 AM' as datetime) as integer)
> set @.DD = cast (@.DI as datetime)
> print @.DI; print @.DD
> Result:
> 37690
> Mar 12 2003 12:00AM
> PM Example:
> declare @.DI integer; declare @.DD datetime
> set @.DI = cast(cast('3/12/2003 11:34:02 PM' as datetime) as integer)
> set @.DD = cast (@.DI as datetime)
> print @.DI; print @.DD
> Result:
> 37691
> Mar 13 2003 12:00AM
> Now, this is not a big problem if I knew that this is how it is
> supposed to work. Is this how SQL Server is supposed to work?

Apparently, SQL Server rounds to the nearest wholest int. I wouldn't
say this makes much sense to me.

Then again, I have to admit that I don't really see the point with
converting datetime values to integer.

In any case, the workaround should be simple, first chop of the
time portion.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Erland Sommarskog" <sommar@.algonet.se> wrote:
> Nikola (nigel35@.hotmail.com) writes:
> > AM Example:
> > declare @.DI integer; declare @.DD datetime
> > set @.DI = cast(cast('3/12/2003 11:34:02 AM' as datetime) as integer)
> > set @.DD = cast (@.DI as datetime)
> > print @.DI; print @.DD
> > Result:
> > 37690
> > Mar 12 2003 12:00AM
> > PM Example:
> > declare @.DI integer; declare @.DD datetime
> > set @.DI = cast(cast('3/12/2003 11:34:02 PM' as datetime) as integer)
> > set @.DD = cast (@.DI as datetime)
> > print @.DI; print @.DD
> > Result:
> > 37691
> > Mar 13 2003 12:00AM
> > Now, this is not a big problem if I knew that this is how it is
> > supposed to work. Is this how SQL Server is supposed to work?
> Apparently, SQL Server rounds to the nearest wholest int. I wouldn't
> say this makes much sense to me.
> Then again, I have to admit that I don't really see the point with
> converting datetime values to integer.
> In any case, the workaround should be simple, first chop of the
> time portion.

VB6 will allow a similar translation and it has the same problem: a real
number is returned where the fractional (i.e. right of the decimal point)
part represents the time. So, converting from datetime to an int carries a
hidden conversion that rounds to get the integer. Check this out (I used
money, although I assume float or real would suffice).

declare @.d datetime
declare @.n money

set @.d = '3/12/2003'
set @.n = convert(money, @.d)
print convert(varchar, @.n) + ' - ' + convert(varchar, @.d)

set @.d = '3/12/2003 11:34 AM'
set @.n = convert(money, @.d)
print convert(varchar, @.n) + ' - ' + convert(varchar, @.d)

set @.d = '3/12/2003 11:34 PM'
set @.n = convert(money, @.d)
print convert(varchar, @.n) + ' - ' + convert(varchar, @.d)

set @.d = '3/13/2003'
set @.n = convert(money, @.d)
print convert(varchar, @.n) + ' - ' + convert(varchar, @.d)

Craig|||Erland Sommarskog (sommar@.algonet.se) writes:
> Apparently, SQL Server rounds to the nearest wholest int. I wouldn't
> say this makes much sense to me.
> Then again, I have to admit that I don't really see the point with
> converting datetime values to integer.
> In any case, the workaround should be simple, first chop of the
> time portion.

Actually there is an even simpler workaround:

declare @.d datetime
declare @.i int

SELECT @.d = '20020202 11:59:00'
SELECT @.i = convert(float, @.d)
SELECT @.i

SELECT @.d = '20020202 12:01:00'
SELECT @.i = convert(float, @.d)
SELECT @.i

This works, because when convering from float to int, truncation occurs...

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsqlsql

Converting calculated field to real

I have a view with 3 integer fields: FailureCount, QuestionCount and
AuditCount. I want to get the following calculated result in a SELECT
statement:
SELECT FailureCount / (QuestionCount * AuditCount)
Problem is, this returns an integer result (as one would expect from 3
integers). I need the real number value, and CONVERT isn't working. I've
tried various syntaxes and I either get errors or no effect at all.
Any ideas how to properly do this?
Thanks,
Randall ArnoldOn Thu, 01 Dec 2005 23:50:07 GMT, Randall Arnold wrote:
>I have a view with 3 integer fields: FailureCount, QuestionCount and
>AuditCount. I want to get the following calculated result in a SELECT
>statement:
>SELECT FailureCount / (QuestionCount * AuditCount)
>Problem is, this returns an integer result (as one would expect from 3
>integers). I need the real number value, and CONVERT isn't working. I've
>tried various syntaxes and I either get errors or no effect at all.
>Any ideas how to properly do this?
>Thanks,
>Randall Arnold
>
Hi Randall,
One of many possibilities:
SELECT CAST(FailureCount AS float) / (QuestionCount * AuditCount)
Note that you have to convert at least one of the inputs to the division
instead of the result. Otherwise, integer logic is used for the division
and the result is then converted to real - but the fraction will already
be lost by then.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks, Hugo! That's what I was looking for.
It turns out I was able to use a different method. In their original
queries, these are all counts in the Group By column of the designer, and I
just changed the select statement as follows:
SELECT CONVERT(real, COUNT(dbo.InternalAudit.DateInput)) AS AuditCount
I did the same for the other two fields, and everything worked. But I'll
hold onto your solution for cases where I'm stuck with integers I can't
change.
Randall Arnold
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:hl3vo1l5vbthugdgqqbef0q7c954njo4hk@.4ax.com...
> On Thu, 01 Dec 2005 23:50:07 GMT, Randall Arnold wrote:
>>I have a view with 3 integer fields: FailureCount, QuestionCount and
>>AuditCount. I want to get the following calculated result in a SELECT
>>statement:
>>SELECT FailureCount / (QuestionCount * AuditCount)
>>Problem is, this returns an integer result (as one would expect from 3
>>integers). I need the real number value, and CONVERT isn't working. I've
>>tried various syntaxes and I either get errors or no effect at all.
>>Any ideas how to properly do this?
>>Thanks,
>>Randall Arnold
> Hi Randall,
> One of many possibilities:
> SELECT CAST(FailureCount AS float) / (QuestionCount * AuditCount)
>
> Note that you have to convert at least one of the inputs to the division
> instead of the result. Otherwise, integer logic is used for the division
> and the result is then converted to real - but the fraction will already
> be lost by then.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)sqlsql

Converting calculated field to real

I have a view with 3 integer fields: FailureCount, QuestionCount and
AuditCount. I want to get the following calculated result in a SELECT
statement:
SELECT FailureCount / (QuestionCount * AuditCount)
Problem is, this returns an integer result (as one would expect from 3
integers). I need the real number value, and CONVERT isn't working. I've
tried various syntaxes and I either get errors or no effect at all.
Any ideas how to properly do this?
Thanks,
Randall ArnoldOn Thu, 01 Dec 2005 23:50:07 GMT, Randall Arnold wrote:

>I have a view with 3 integer fields: FailureCount, QuestionCount and
>AuditCount. I want to get the following calculated result in a SELECT
>statement:
>SELECT FailureCount / (QuestionCount * AuditCount)
>Problem is, this returns an integer result (as one would expect from 3
>integers). I need the real number value, and CONVERT isn't working. I've
>tried various syntaxes and I either get errors or no effect at all.
>Any ideas how to properly do this?
>Thanks,
>Randall Arnold
>
Hi Randall,
One of many possibilities:
SELECT CAST(FailureCount AS float) / (QuestionCount * AuditCount)
Note that you have to convert at least one of the inputs to the division
instead of the result. Otherwise, integer logic is used for the division
and the result is then converted to real - but the fraction will already
be lost by then.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks, Hugo! That's what I was looking for.
It turns out I was able to use a different method. In their original
queries, these are all counts in the Group By column of the designer, and I
just changed the select statement as follows:
SELECT CONVERT(real, COUNT(dbo.InternalAudit.DateInput)) AS AuditCount
I did the same for the other two fields, and everything worked. But I'll
hold onto your solution for cases where I'm stuck with integers I can't
change.
Randall Arnold
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:hl3vo1l5vbthugdgqqbef0q7c954njo4hk@.
4ax.com...
> On Thu, 01 Dec 2005 23:50:07 GMT, Randall Arnold wrote:
>
> Hi Randall,
> One of many possibilities:
> SELECT CAST(FailureCount AS float) / (QuestionCount * AuditCount)
>
> Note that you have to convert at least one of the inputs to the division
> instead of the result. Otherwise, integer logic is used for the division
> and the result is then converted to real - but the fraction will already
> be lost by then.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)

Converting calculated field to real

I have a view with 3 integer fields: FailureCount, QuestionCount and
AuditCount. I want to get the following calculated result in a SELECT
statement:
SELECT FailureCount / (QuestionCount * AuditCount)
Problem is, this returns an integer result (as one would expect from 3
integers). I need the real number value, and CONVERT isn't working. I've
tried various syntaxes and I either get errors or no effect at all.
Any ideas how to properly do this?
Thanks,
Randall Arnold
On Thu, 01 Dec 2005 23:50:07 GMT, Randall Arnold wrote:

>I have a view with 3 integer fields: FailureCount, QuestionCount and
>AuditCount. I want to get the following calculated result in a SELECT
>statement:
>SELECT FailureCount / (QuestionCount * AuditCount)
>Problem is, this returns an integer result (as one would expect from 3
>integers). I need the real number value, and CONVERT isn't working. I've
>tried various syntaxes and I either get errors or no effect at all.
>Any ideas how to properly do this?
>Thanks,
>Randall Arnold
>
Hi Randall,
One of many possibilities:
SELECT CAST(FailureCount AS float) / (QuestionCount * AuditCount)
Note that you have to convert at least one of the inputs to the division
instead of the result. Otherwise, integer logic is used for the division
and the result is then converted to real - but the fraction will already
be lost by then.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Thanks, Hugo! That's what I was looking for.
It turns out I was able to use a different method. In their original
queries, these are all counts in the Group By column of the designer, and I
just changed the select statement as follows:
SELECT CONVERT(real, COUNT(dbo.InternalAudit.DateInput)) AS AuditCount
I did the same for the other two fields, and everything worked. But I'll
hold onto your solution for cases where I'm stuck with integers I can't
change.
Randall Arnold
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:hl3vo1l5vbthugdgqqbef0q7c954njo4hk@.4ax.com...
> On Thu, 01 Dec 2005 23:50:07 GMT, Randall Arnold wrote:
>
> Hi Randall,
> One of many possibilities:
> SELECT CAST(FailureCount AS float) / (QuestionCount * AuditCount)
>
> Note that you have to convert at least one of the inputs to the division
> instead of the result. Otherwise, integer logic is used for the division
> and the result is then converted to real - but the fraction will already
> be lost by then.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)

Converting an integer field into an Identity field

I have a table with an integer field (contains test values like 2, 7,8,9,12,..) that I want to convert to an Identity field. How can this be done in t-sql?

TIA,

Barkingdog

There is no TSQL statement to change a non-identity field to an identity field, even the designer will do strange things behind the scenes, like creating a new table copying the data to the new one, renaming the new and dropping the old table. SO you either do the same in your TSQL statements or use the gUI which does all the steps for you behind the scenes.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

converting a float to a varchar _without_E syntax?

I have to output a column in a very specific format:
single quote integer number single quote
like this:
'100000'
But the data is in a float. When I do:
'''' + CAST(quantity as varchar) + ''''
I get:
'2e+007'
which is useless. I can't figure out the trick to telling SQL Server not to
use e notation. Is there a way?
MauryCAST(CAST(quantity as integer) as varchar)
Providing it casts to integer without overflowing.
RLF
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:6EAD6EF9-8ED7-44D3-897C-EFB3B995589C@.microsoft.com...
>I have to output a column in a very specific format:
> single quote integer number single quote
> like this:
> '100000'
> But the data is in a float. When I do:
> '''' + CAST(quantity as varchar) + ''''
> I get:
> '2e+007'
> which is useless. I can't figure out the trick to telling SQL Server not
> to
> use e notation. Is there a way?
> Maury|||"Russell Fields" wrote:
> CAST(CAST(quantity as integer) as varchar)
> Providing it casts to integer without overflowing.
Yikes!
Is it just me or would we all be a lot better off if MS put some time into
painfully obvious basic functionality like CONVERT instead of gee-wiz
features none of us actually use?
Maury|||Maury,
Maybe, but in cases like this the question is always: What do you expect as
your answer?
You wanted an integer this time, but at other times you might have wanted
the E notation. The code snippet made your intention explicit by saying 1)
make the float into an integer, then 2) make the integer into a string.
Interestingly, the CONVERT function offers 'styles', but I don't think any
absolutely matched your need to be an integer. You could try:
SELECT CONVERT(VARCHAR(15),floatquantity, 0)
But the definition of style 0 is "A maximum of 6 digits. Use in scientific
notation, when appropriate", so you might get 1234 or 123.4 or 1.234E9 all
depending on the value.
It seems that, in this case, you are really asking for some more flexible
styles for conversion. A good place for making such suggestions is at:
https://connect.microsoft.com/SQLServer. I found one suggestion like this
posted by Adam Machanic back in 2005 and "closed by design" by Microsoft.
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=126338
But you could raise it again.
RLF
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:25B21A42-7E78-423C-A51A-0E79294BF6CF@.microsoft.com...
> "Russell Fields" wrote:
>> CAST(CAST(quantity as integer) as varchar)
>> Providing it casts to integer without overflowing.
> Yikes!
> Is it just me or would we all be a lot better off if MS put some time into
> painfully obvious basic functionality like CONVERT instead of gee-wiz
> features none of us actually use?
> Maury

converting a column from varchar to int

I need to copy the data from varchar column into an integer column,
obviously only copying integer values. How can I do this? I am not even
sure how to select data from a column so that the results are only the
numeric ones. Thanks for your help.Et,
ALTER TABLE should allow you to change the datatype of the table assuming
all values are integers.
You could run something like this:
UPDATE tablename
SET col2 = CAST(col1 AS int) --would require the values to be integers not
characters
Check out the ISNUMERIC function for differentiating between integers and
characters. Also see:
http://www.aspfaq.com/show.asp?id=2390
HTH
Jerry
"et" <eagletender2001@.yahoo.com> wrote in message
news:%23nc3kkdxFHA.1252@.TK2MSFTNGP09.phx.gbl...
>I need to copy the data from varchar column into an integer column,
>obviously only copying integer values. How can I do this? I am not even
>sure how to select data from a column so that the results are only the
>numeric ones. Thanks for your help.
>|||Yes, the isnumeric function worked, the cast of course gave me an error
because not all values are integers. This is what I did:
update tbl set newcol=originalcol
where isnumeric(originalcol)=1
Now I have to populate the remaining fields with an integer, any idea an
easy way to do that? I'd like to do it starting with the next available
number, but it doesn't really matter what the number ends up being. This
column will eventually be an identity column.
Thanks!
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:eMYJevdxFHA.2232@.TK2MSFTNGP11.phx.gbl...
> Et,
> ALTER TABLE should allow you to change the datatype of the table assuming
> all values are integers.
> You could run something like this:
> UPDATE tablename
> SET col2 = CAST(col1 AS int) --would require the values to be integers
> not characters
> Check out the ISNUMERIC function for differentiating between integers and
> characters. Also see:
> http://www.aspfaq.com/show.asp?id=2390
> HTH
> Jerry
>
> "et" <eagletender2001@.yahoo.com> wrote in message
> news:%23nc3kkdxFHA.1252@.TK2MSFTNGP09.phx.gbl...
>|||ok...clarify please...IDENTITY is system generated so you don't populate it
with any values. "any number"?
"et" <eagletender2001@.yahoo.com> wrote in message
news:O3CsMRhxFHA.464@.TK2MSFTNGP15.phx.gbl...
> Yes, the isnumeric function worked, the cast of course gave me an error
> because not all values are integers. This is what I did:
> update tbl set newcol=originalcol
> where isnumeric(originalcol)=1
> Now I have to populate the remaining fields with an integer, any idea an
> easy way to do that? I'd like to do it starting with the next available
> number, but it doesn't really matter what the number ends up being. This
> column will eventually be an identity column.
> Thanks!
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:eMYJevdxFHA.2232@.TK2MSFTNGP11.phx.gbl...
>|||That's correct. In other words if this database had been done correctly in
the first place, it would have been an identity column. So now I need to
create this identity column based on numbers that already exist, plus
populate those records with new numbers for those records that don't use an
integer value. Once this is done, then I can make the column an identity
column so that future new records are automatically numbered correctly.
Does that make sense?
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:eBZ%23xThxFHA.1412@.TK2MSFTNGP09.phx.gbl...
> ok...clarify please...IDENTITY is system generated so you don't populate
> it with any values. "any number"?
> "et" <eagletender2001@.yahoo.com> wrote in message
> news:O3CsMRhxFHA.464@.TK2MSFTNGP15.phx.gbl...
>|||You cannot alter an existing column to be an identity column. You'll have to
create a new table with the identity column and then copy the rows from the
old table into the new one using SET IDENTITY_INSERT ON.
Why are you doing this? Are you planning on using the identity column as the
primary key? Analyze your data more thoroughly - there may be a better
primary key candidate.
To clean up those values try this:
http://milambda.blogspot.com/2005/0...to-integer.html
ML|||No, my primary key is a guid. This column used to be the primary key in an
access database, and was also used for other reasons. Now it's being merged
with the company's sql database. I'm only using the identity feature so it
will automatically provide a number each time a new record is created, which
of course I can do within the program it's being used with also.
"ML" <ML@.discussions.microsoft.com> wrote in message
news:53200E31-72FB-4C15-AF6B-CF6858E3296E@.microsoft.com...
> You cannot alter an existing column to be an identity column. You'll have
> to
> create a new table with the identity column and then copy the rows from
> the
> old table into the new one using SET IDENTITY_INSERT ON.
> Why are you doing this? Are you planning on using the identity column as
> the
> primary key? Analyze your data more thoroughly - there may be a better
> primary key candidate.
> To clean up those values try this:
> http://milambda.blogspot.com/2005/0...to-integer.html
>
> ML|||If the values still need to be unique, then I'd suggest letting the server
assign them.
ML

Convert Year to Date

I need to take an integer field that is supposed to be a year, and convert
it to be a date field, with the date being 1/1/Year for whatever the year
is. So 1990 would update to be 1/1/1990 -- How can I do that?
Thanks for your help.SELECT CONVERT(SMALLDATETIME,
RTRIM(Column_Year_With_Incorrect_DataTyp
e)+'0101')
FROM table
WHERE Column_Year_With_Incorrect_DataType BETWEEN 1900 AND 2029;
"KatMagic" <SSKatMagic@.yahoo.com> wrote in message
news:e1bAHcZXGHA.3848@.TK2MSFTNGP05.phx.gbl...
>I need to take an integer field that is supposed to be a year, and convert
>it to be a date field, with the date being 1/1/Year for whatever the year
>is. So 1990 would update to be 1/1/1990 -- How can I do that?
> Thanks for your help.
>|||KatMagic,
Check function "convert" in BOL, Style 112.
declare @.i int
set @.i = 2006
select cast(ltrim(@.i) + '0101' as datetime)
go
AMB
"KatMagic" wrote:

> I need to take an integer field that is supposed to be a year, and convert
> it to be a date field, with the date being 1/1/Year for whatever the year
> is. So 1990 would update to be 1/1/1990 -- How can I do that?
> Thanks for your help.
>
>|||Or you could do something like the following:
DROP TABLE #years
CREATE TABLE #years ( year_int INT )
SET NOCOUNT ON
INSERT INTO #years VALUES ( 1990 )
INSERT INTO #years VALUES ( 1991 )
INSERT INTO #years VALUES ( 1992 )
INSERT INTO #years VALUES ( 2000 )
SET NOCOUNT OFF
SELECT year_int, DATEADD( year, year_int-1900, 0 )
FROM #years
DATEADD will always return a DATETIME. Let me know how you get on.
Damien
"KatMagic" wrote:

> I need to take an integer field that is supposed to be a year, and convert
> it to be a date field, with the date being 1/1/Year for whatever the year
> is. So 1990 would update to be 1/1/1990 -- How can I do that?
> Thanks for your help.
>
>

convert seconds into format hh:mm:ss

Is there a simple way to convert seconds (integer) number into the format hh:mm:ss format in SQL2000 query analyzer?try this...

declare @.h int, @.m int, @.s int
set @.s = 9003
set @.h = @.s/360
set @.s = @.s - (@.h * 360)
set @.m = @.s / 60
set @.s = @.s - (@.m * 60)
select right("00" + cast(@.h as varchar),2) + ":" + right("00" + cast(@.m as varchar),2) + ":" + right("00" + cast(@.s as varchar),2)|||Originally posted by vkaramched
Is there a simple way to convert seconds (integer) number into the format hh:mm:ss format in SQL2000 query analyzer?

Hi vkaramched, Try This.

Declare @.pvsSeconds INT

Set @.pvsSeconds = 4754395
CASE WHEN (@.pvsSeconds/3600) <=9 THEN '0' ELSE '' END + CONVERT(VARCHAR, (@.pvsSeconds/3600)) + ':' + RIGHT('100' + CONVERT(VARCHAR, (@.pvsSeconds%3600)/60),2) + ':' + RIGHT('100' + CONVERT(VARCHAR, @.pvsSeconds%60),2)

Cheers,
Gola Munjal

Convert returned value to Integer

I have the following stored procedure for creating a transaction record and after inserting the record, the transaction id is returned.

----------------
DECLARE @.TransactionID int
<-- INSERT statement (after inserting the record, select the identity ID) --->
Select @.TransactionID = @.@.Identity
RETURN
----------------

.
.
.
Dim transactionID As Integer
connection.Open()
Dim reader As SqlDataReader
reader = command.ExecuteReader(CommandBehavior.CloseConnection)
Dim p3 As New SqlParameter("@.TransactionID", SqlDbType.Int)
p3.Direction = ParameterDirection.Output
transactionID = Convert.ToInt16(p3.Value)
connection.Close()
.
.
.

I wanna retrieve the transactionID of the newly inserted record so that I can use it in my next step of inserting a new record to another table which has reference to the transactionID as a foreign key. However, I encountered error and suspect that it is due to the conversion of the output to Integer as it worked when I tested using dummy Integers.

I tried many different ways of conversion but couldn't resolve the error. Could anyone help?

Are you returning the TransactionIf through an OUTPUt parameter in the stored proc?

You need to use ExecuteReader if your stored proc/TSQL is returning a result set and you are trying to retrieve it through .NET. If you are retrieving values through OUTPUT parameters you would use ExecueNonQuery since ExecuteReader returns a reader which you are not using anyway. So why do you want to the overhead of the result set being passed around.

check ifthis articlehelps.

|||

ndinakar:

Are you returning the TransactionIf through an OUTPUt parameter in the stored proc?

You need to use ExecuteReader if your stored proc/TSQL is returning a result set and you are trying to retrieve it through .NET. If you are retrieving values through OUTPUT parameters you would use ExecueNonQuery since ExecuteReader returns a reader which you are not using anyway. So why do you want to the overhead of the result set being passed around.

check ifthis articlehelps.

hmm.. pardon me as this is my first time with asp.net

In the article:

myCommand.Parameters.Add(New SqlParameter("@.ProductId",SqlDbType.Int))

myCommand.Parameters.Direction = ParameterDirection.Output

The line in red doesn't work. I guess I'm using asp.net 2.0, that's why... so I changed to something like

command.Parameters("@.ProductID").Direction = ParameterDirection.Output

Yupz.. I'm retrieving through output parameters, I tried ExecuteReader but still couldn't work.

Actually I have these two tables for a shopping cart project - OrderTransaction and OrderDetails

OrderTransaction stores general information of TransactionID, Status, ShippingAddress etc of an order. OrderDetails stores details of a particular transaction which has foreign keys references to the Ids of OrderTransaction, Customers and Products table.
Hence, the reason for retrieving the TransactionID is so that I can create records for the items in the shopping cart in the OrderDetails for the particular transaction.

Code:

Dim transactionID As Integer
Dim connection As SqlConnection = New SqlConnection(System.Configuration.ConfigurationManager.AppSettings("ConnectionString"))
Dim command As SqlCommand = New SqlCommand("AddOrders", connection)
command.CommandType = CommandType.StoredProcedure
command.Parameters.AddWithValue("@.CustomerID", Context.Session("myID"))
command.Parameters.AddWithValue("@.DateCreated", DateTime.Now.ToString())
command.Parameters.AddWithValue("@.Verified", 0)
command.Parameters.AddWithValue("@.Cancelled", 0)
command.Parameters.AddWithValue("@.CustomerName", nameTB.Text)
command.Parameters.AddWithValue("@.ShippingAddress", shippingaddressTB.Text)
command.Parameters.AddWithValue("@.CustomerEmail", emailTB.Text)
command.Parameters.Add("@.TransactionID", SqlDbType.SmallInt)
command.Parameters("@.TransactionID").Direction = ParameterDirection.Output
connection.Open()
command.ExecuteNonQuery()
transactionID = Convert.ToInt16(command.Parameters("@.TransactionID").Value)
connection.Close()

Stored Procedure:

ALTER PROCEDURE AddOrders
(
@.CustomerID int,
@.DateCreated smalldatetime,
@.Verified bit,
@.Cancelled bit,
@.CustomerName varchar(50),
@.ShippingAddress varchar(200),
@.CustomerEmail varchar(50)
)

AS

DECLARE @.TransactionID int

INSERT INTO OrderTransaction (CustomerID, DateCreated, Verified, Cancelled, CustomerName, ShippingAddress, CustomerEmail)
VALUES (@.CustomerID, Convert(smalldatetime,@.DateCreated), @.Verified, @.Cancelled, @.CustomerName, @.ShippingAddress, @.CustomerEmail)

Select @.TransactionID = @.@.Identity

RETURN

The error is
"Procedure or function AddOrders has too many arguments specified."
Line 161: command.ExecuteNonQuery()

I think that's because I did something wrong in the lines ingreen.Appreciate if anyone helps. Thanks in advanced.
|||(1) The error is exactly what it says. You are trying to add @.TransactionId as a parameter when you havent declared it as a parameter in your stored proc. modify your stored proc as follows:
(2) SCOPE_IDENTITY() is more efficient than @.@.IDENTITY. Check out books on line for more explanation.
(3) Since the value you are returning is through OUTPUT parameters, use ExecuteNonQuery.

ALTER PROCEDURE AddOrders
(
@.CustomerID int,
@.DateCreated smalldatetime,
@.Verified bit,
@.Cancelled bit,
@.CustomerName varchar(50),
@.ShippingAddress varchar(200),
@.CustomerEmail varchar(50),
@.TransactionId INT OUTPUT
)

AS

BEGIN
SET NOCOUNT ON

INSERT INTO OrderTransaction (CustomerID, DateCreated, Verified, Cancelled, CustomerName, ShippingAddress, CustomerEmail)
VALUES (@.CustomerID, Convert(smalldatetime,@.DateCreated),@.Verified, @.Cancelled, @.CustomerName, @.ShippingAddress, @.CustomerEmail)
SELECT @.TransactionID= SCOPE_IDENTITY()

SET NOCOUNT OFF
END

Convert problem

I have a 'datetime' column and I want to select the count
of today's dates and insert into another table column with
integer type (Only the number as a count). I have the
following query:
Select count(*)from dbo.stats
where convert(varchar(12),accessdate, 112) = convert
(varchar(12),GETDATE(),112) AND accessdate <> ''
I get the following error:
Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value 'select count(*)
from dbo.stats
where convert(varchar(12),started, 112) = convert(varchar
(12),GETDATE(),112)
AND accessdate <> '' to a column of data type int.
How do I fix this problem.
Thanks.> Select count(*)from dbo.stats
--^
Missing space here?
> where convert(varchar(12),accessdate, 112) = convert
> (varchar(12),GETDATE(),112) AND accessdate <> ''
Did you mean
AND accessdate IS NOT NULL
? If it is a datetime column, it can't be equal to '' (empty string) since
it is *not* a string at all.
Also, if you want to make use of an index on the column, it would probably
be more efficient to say this:
WHERE accessdate >= {fn CURDATE()}
AND accessdate < DATEADD(DAY, 1, {fn CURDATE()})
AND accessdate IS NOT NULL
A|||The space is a typo error only in the post. Query works
even with '' alone (Without inserting) and the column is
not indexed column since there will be more than 1 value
for the same day............
>--Original Message--
>> Select count(*)from dbo.stats
>--^
>Missing space here?
>> where convert(varchar(12),accessdate, 112) = convert
>> (varchar(12),GETDATE(),112) AND accessdate <> ''
>Did you mean
>AND accessdate IS NOT NULL
>? If it is a datetime column, it can't be equal to ''
(empty string) since
>it is *not* a string at all.
>Also, if you want to make use of an index on the column,
it would probably
>be more efficient to say this:
>WHERE accessdate >= {fn CURDATE()}
> AND accessdate < DATEADD(DAY, 1, {fn CURDATE()})
> AND accessdate IS NOT NULL
>A
>
>.
>|||> The space is a typo error only in the post.
Then can you show your actual table structure, some sample data, and the
actual query you use (rather than transposing!!!!), then someone can try and
reproduce your error? The error actually doesn't fit with the query you've
shown; I'm guessing you are trying to execute this from EXEC or
sp_executeSQL.
> not indexed column since there will be more than 1 value
> for the same day...
Not sure what that has to do with indexing. Unique index, maybe. But you
can certainly index columns that contain duplicates, and you certainly
*should* consider indexing columns that will be used frequently in exact or
range queries, such as the one you are writing...|||Mike,
Maybe you are having problems with quotes. The error message suggests
that SQL-Server is interpreting your entire query as a varchar value.
Maybe you are building the query in a front-end tool and using single
quotes to delimited the SQL statement. In that case, you need to escape
the quotes of the statement so they end up in the final string.
Hope this helps,
Gert-Jan
Mike wrote:
> I have a 'datetime' column and I want to select the count
> of today's dates and insert into another table column with
> integer type (Only the number as a count). I have the
> following query:
> Select count(*)from dbo.stats
> where convert(varchar(12),accessdate, 112) = convert
> (varchar(12),GETDATE(),112) AND accessdate <> ''
> I get the following error:
> Server: Msg 245, Level 16, State 1, Line 1
> Syntax error converting the varchar value 'select count(*)
> from dbo.stats
> where convert(varchar(12),started, 112) = convert(varchar
> (12),GETDATE(),112)
> AND accessdate <> '' to a column of data type int.
> How do I fix this problem.
> Thanks.

Convert nvarchar values to integer

I have imported a text file with various data into sql table. all these values have been imported as nvarchar. I need to convert these into Integer. the format of the values is 10length i.e. 0000000.00.

example of data:
0001028.99 - needs to be shown as 1028.99
222.00 - needs to be shown as 222.00
0000190.89 - needs to be shown as 190.89
2708.99 - needs to be shown as 2708.99
00000-50.99 - needs to be shown as -50.99
-109.79 - needs to be shown as -109.70

as you can see some of the values have leading zeros and some don't.
i have tried converting from nvarchar to int and i get the error cannot convert nvarchar to int, i believe it may be because the data contains negative values as well as positive values.

Is there a split function or position function which i can use to extract the data? or any other methods which i can use would be really helpful.

Thanks

Cast the values as decimal, i.e.: (You don't want integers, that would lose the portion after the decimal.)

SET NOCOUNT ON

DECLARE @.MyTable table
( RowID int IDENTITY,
MyValue varchar(20)
)

INSERT INTO @.MyTable VALUES ( 0001028.99 )
INSERT INTO @.MyTable VALUES ( 222.00 )
INSERT INTO @.MyTable VALUES ( 0000190.89 )
INSERT INTO @.MyTable VALUES ( 2708.99 )
INSERT INTO @.MyTable VALUES ( 00000-50.99 )
INSERT INTO @.MyTable VALUES ( -109.79 )

SELECT MyValues = cast( MyValue AS decimal(10,2))
FROM @.MyTable

MyValues

1028.99
222.00
190.89
2708.99
-50.99
-109.79

Convert NULL value to INTEGER

hi all,
i have a problem with converting NULL value INTEGER.
i need to convert NULL value to 0 (zero)
this is my sample query :
SELECT SUM( qty ) AS qty FROM products WHERE id = 12121
i've tried these to
SELECT CONVERT( INT, SUM( qty ) ) AS qty FROM products WHERE id = 12121
SELECT CAST( SUM( qty ) AS INTEGER ) AS qty FROM products WHERE id = 12121
the problem came up when querying unexisting data ( 12121 is not
exists ).
can you help me.
thx,
aCeSELECT COALESCE(SUM( qty ),0) AS qty FROM products WHERE id = 12121
SELECT ISNULL(SUM( qty ),0) AS qty FROM products WHERE id = 12121
COALESCE is preferred, as it is standard SQL. It is also more
flexible than ISNULL as it can except more than two parameters; it
returns the first non-NULL parameter.
Roy Harvey
Beacon Falls, CT
On Wed, 24 Oct 2007 11:12:26 -0700, aCe <acerahmat@.gmail.com> wrote:
>hi all,
>i have a problem with converting NULL value INTEGER.
>i need to convert NULL value to 0 (zero)
>this is my sample query :
>SELECT SUM( qty ) AS qty FROM products WHERE id = 12121
>i've tried these to
>SELECT CONVERT( INT, SUM( qty ) ) AS qty FROM products WHERE id =>12121
>SELECT CAST( SUM( qty ) AS INTEGER ) AS qty FROM products WHERE id =>12121
>the problem came up when querying unexisting data ( 12121 is not
>exists ).
>can you help me.
>thx,
>aCe

convert millisecond to "hh:mm:ss" format

Hello guys,

I have a column of integer data type that contains a millisecond data(for example 54013). I want to convert this value to the corresponding "hh:mm:ss" format. Can anybody help me with this issue?

Sincerely,

amde

How about this

declare @.SomeMilliSecondsNumber bigint
select @.SomeMilliSecondsNumber =54013

select convert(varchar,dateadd(ms,@.SomeMilliSecondsNumber,0),114)

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||

Thanks a lot Denis!

Amde

|||

Glad I could help

Denis the SQL Menace

http://sqlservercode.blogspot.com/

convert millisecond to "hh:mm:ss" format

Hello guys,

I have a column of integer data type that contains a millisecond data(for example 54013). I want to convert this value to the corresponding "hh:mm:ss" format. Can anybody help me with this issue?

Sincerely,

amde

How about this

declare @.SomeMilliSecondsNumber bigint
select @.SomeMilliSecondsNumber =54013

select convert(varchar,dateadd(ms,@.SomeMilliSecondsNumber,0),114)

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||

Thanks a lot Denis!

Amde

|||

Glad I could help

Denis the SQL Menace

http://sqlservercode.blogspot.com/

Convert letter to integer based on order in alphabet

Is there any way I can convert a letter of the alphabet to its numerical
position in the alphabet.
So if I select a field with 'A' I want to return 1, 'B' = 2, ... 'Z' = 26
I'd prefer not to join to a seperate reference table if possible.Terri wrote:
> Is there any way I can convert a letter of the alphabet to its numerical
> position in the alphabet.
> So if I select a field with 'A' I want to return 1, 'B' = 2, ... 'Z' = 26
> I'd prefer not to join to a seperate reference table if possible.
How about this?
SELECT ASCII(YourOneCharColumn) - ASCII('A') + 1 FROM YourTable|||CREATE TABLE alpha (alpha_char CHAR(1) NOT NULL PRIMARY KEY, alpha_num
INTEGER NOT NULL) ;
SELECT alpha_num
FROM some_table AS T
JOIN alpha AS A
ON T.col = A.alpha_char ;
David Portas
SQL Server MVP
--
"Terri" <terri@.cybernets.com> wrote in message
news:di6qca$357$1@.reader2.nmix.net...
> Is there any way I can convert a letter of the alphabet to its numerical
> position in the alphabet.
> So if I select a field with 'A' I want to return 1, 'B' = 2, ... 'Z' = 26
> I'd prefer not to join to a seperate reference table if possible.
>

Convert Integer To Time Only In SELECT

I have stored, as seconds, a duration in a table. I would like to use a SELECT statement to retrieve the contents of the column but display them as a time. For example:

45 = 0:00:45
241 = 0:04:01
575 = 0:09:35

and so on...

I have tried using the built-in CONVERT() function but always end up with StartDate + Integer and a time of 0:00:00.

Can anyone help please?

Cheers,

Roy

You can do:

declare @.seconds int
set @.seconds = 241
select convert(char(8), dateadd(second, @.seconds, ''), 114)

|||Thanks for this.

I took your example and turned it in to this which works:

SELECT CONVERT(char(8), DATEADD(second, Duration, ''), 114) AS Duration ...

Cheers,

Roy

convert integer to string

How can I convert an integer to string?

When I use convert function to convert an integer to varchar as below, I get incorrect value of @.EmployeeID as '1'. In the Employee table, EmployeeID is of type int.

I want to pass @.EmployeeID and @.NewID as string to another stored proc which accepts @.EmployeeID and @.NewID as TEXT parameters.

Declare @.RowID INT
DECLARE @.EmployeeID VARCHAR
DECLARE @.NewID VARCHAR

SELECT @.EmployeeID = CONVERT(varchar, EmployeeID)
FROM dbo.Employee
WHERE Row = @.RowID

...

...

EXEC calculateSalary @.EmployeeID, @.NewID

My guess would be that your EmployeeID starts with a "1", see you're declaring EmployeeID to be a varchar of length 1, so it's only storing the first character of the EmployeeID, for example:

declare @.employeeid varchar
select @.employeeid = convert(varchar, 54321)
select @.employeeid

returns "5" where:

declare @.employeeid varchar(5)
select @.employeeid = convert(varchar, 54321)
select @.employeeid

returns the whole integer.|||

You might wish to add a size parameter to a varchar() datatype. Using just [ varchar ] defaults to a single character -truncating the rest if the number is greater than 9.

Your SELECT statement, using convert() 'should' be functioning ok. I would prefer using CAST( EmployeeID as varchar(5) ).

|||Note that varchar defaults to one character in a declare statement, but 30 in a cast/convert:

declare @.i int
set @.i = 1234567890
select cast(@.i as varchar)

And

select cast('12345678901234567890123456789012345678901234567890' as varchar)

Horrible, horrible thing |||

Converting from NUMERIC data type to string never truncate the value it will produce * symbol on your output.

Code Snippet

Select Convert(varchar(2),99) A, cast(99as varchar(2)) B

A B

- -

99 99

Select Convert(varchar(2),100) A, cast(100 as varchar(2)) B
A B
- -
* *

|||

Manivannan.D.Sekaran wrote:

Converting from NUMERIC data type to string never truncate the value it will produce * symbol on your output.

Very true, however that is not the problem here. try running the code:

Code Snippet

DECLARE @.ShortChar as varchar

DECLARE @.LongChar as varchar(10)

SET @.ShortChar = CONVERT(varchar, 200)

SET @.LongChar = CONVERT(varchar, 200)

SELECT @.ShortChar, @.LongChar

The result is: 2 200

The problem is not that the convert is truncating the value but that the assignment is silently truncating the value at the length of the variable (1 character). It is trying to put 3 characters in but can only fit the first one and so "loses" the rest.

Convert integer to date

Hi,
I have a couple of csv files with a date column like '20061231' or
'20050521'. What is the easiest way to convert this column into a date in th
e
SSIS pipeline?
The connection manager doesn't accept just defining the column as a date.
Also, converting the integer into a date using the Convert transformation
does not work well.
Can anyone help me out'
Kind regards,
Michel MolsHi Michel,
I can propose you a way to solve that problem. I've already get the
same problem in a project and I used to create a dervied column with
that expression to get my date in that format DD/MM/YYYY:
(DT_DBTIMESTAMP)(SUBSTRING(((DT_WSTR,8)D
ATE),7,2) + "/" +
SUBSTRING(((DT_WSTR,8)DATE),5,2) + "/" +
SUBSTRING(((DT_WSTR,8)DATE),1,4))
YYYY/MM/DD: (DT_DBTIMESTAMP)(SUBSTRING(((DT_WSTR,8)D
ATE),1,4) + "/" +
SUBSTRING(((DT_WSTR,8)DATE),5,2) + "/" +
SUBSTRING(((DT_WSTR,8)DATE),7,2))
I am not sure that is the best way to do that but it is at least one.
Michel a =E9crit :

> Hi,
> I have a couple of csv files with a date column like '20061231' or
> '20050521'. What is the easiest way to convert this column into a date in=
the
> SSIS pipeline?
> The connection manager doesn't accept just defining the column as a date.
> Also, converting the integer into a date using the Convert transformation
> does not work well.
>=20
> Can anyone help me out'
>=20
> Kind regards,
>=20
> Michel Mols

Convert integer to date

Hi,
I have a couple of csv files with a date column like '20061231' or
'20050521'. What is the easiest way to convert this column into a date in the
SSIS pipeline?
The connection manager doesn't accept just defining the column as a date.
Also, converting the integer into a date using the Convert transformation
does not work well.
Can anyone help me out?
Kind regards,
Michel Mols
Hi Michel,
I can propose you a way to solve that problem. I've already get the
same problem in a project and I used to create a dervied column with
that expression to get my date in that format DD/MM/YYYY:
(DT_DBTIMESTAMP)(SUBSTRING(((DT_WSTR,8)DATE),7,2) + "/" +
SUBSTRING(((DT_WSTR,8)DATE),5,2) + "/" +
SUBSTRING(((DT_WSTR,8)DATE),1,4))
YYYY/MM/DD: (DT_DBTIMESTAMP)(SUBSTRING(((DT_WSTR,8)DATE),1,4) + "/" +
SUBSTRING(((DT_WSTR,8)DATE),5,2) + "/" +
SUBSTRING(((DT_WSTR,8)DATE),7,2))
I am not sure that is the best way to do that but it is at least one.
Michel a crit :

> Hi,
> I have a couple of csv files with a date column like '20061231' or
> '20050521'. What is the easiest way to convert this column into a date inthe
> SSIS pipeline?
> The connection manager doesn't accept just defining the column as a date.
> Also, converting the integer into a date using the Convert transformation
> does not work well.
> Can anyone help me out?
> Kind regards,
> Michel Mols