Converting Dates as paramters

Hey guys,

Hoping i can get some help with this one..

Problem:

Data source has a datetime format as YYYYMMDD

I would like to have my users enter a date in the format of DD/MM/YYYY and in the background have it convert to the YYYYMMDD so i can filter my data according to the data

ANy ideas on how i will do this?

thanks

scotty

Use Format function and convert the date parameter to required format.

Format(Parameters!prmStartDate.Value, "yyyy-MM-dd")

Converting bytes to string

Hi guys,

I'm currently trying to insert image into my SQL db. I have tried a number of methods that were posted online, and so farwith no luck.

My current code reads:

Dim conn As New Data.SqlClient.SqlConnection()
conn.ConnectionString = ConfigurationManager.ConnectionStrings("MainDBConnection").ToString
conn.Open()

Dim cmd As New Data.SqlClient.SqlCommand("SP_SAVEImage", conn)

cmd.CommandType = Data.CommandType.StoredProcedure

Dim sImageName As New Data.SqlClient.SqlParameter("@.sImageName", Data.SqlDbType.VarChar, 50)
sImageName.Value = sImageName

Dim sImageType As New Data.SqlClient.SqlParameter("@.sImageType", Data.SqlDbType.VarChar, 50)
sImageType.Value = fileType

Dim sImageData As New Data.SqlClient.SqlParameter("@.sImageData", Data.SqlDbType.Image, uploadedFile.Length)
sImageData.Value = uploadedFile

cmd.Parameters.Add(sImageName)
cmd.Parameters.Add(sImageType)
cmd.Parameters.Add(sImageData)

Dim reader1 As Data.SqlClient.SqlDataReader

reader1 = cmd.ExecuteReader

Runningthrough debug, everything runs up until the last line, where an erroris caught saying : Failed to convert parameter value from aSqlParameter to a String

I reckon it's to do with the input sImageData being input as a byte array - but I can't seem to find a way around it.

Any help greatly appreciated!!

In http://www.codeproject.com/useritems/images_in_sql_server.asp
private void GuardarImagen(byte[] matriz)
{
this.cmd.CommandText = "insert into tabla(DESCRIPCION, IMAGEN) " +
"VALUES(@.DESCRIPCION, @.IMAGEN)";
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("Descripcion", this.NombreDeArchivoCorto).SqlDbType = SqlDbType.VarChar;
cmd.Parameters.AddWithValue("Imagen", matriz).SqlDbType = SqlDbType.Image;
cmd.ExecuteNonQuery();
}

suggests that yours should be
Dim sImageName As New Data.SqlClient.SqlParameter("@.sImageName", Data.SqlDbType.VarChar, 50)
sImageName.Value = sImageName
Dim sImageType As New Data.SqlClient.SqlParameter("@.sImageType", Data.SqlDbType.VarChar, 50)
sImageType.Value = fileType
Dim sImageData As New Data.SqlClient.SqlParameter("@.sImageData", Data.SqlDbType.Image)
sImageData.Value = uploadedFile

If you are using SQL 2005
cmd.Parameters.AddWithValue("sImageName", sImageName).SqlDbType = SqlDbType.VarChar;
cmd.Parameters.AddWithValue("sImageType", sImageType).SqlDbType = SqlDbType.VarChar;
cmd.Parameters.AddWithValue("sImageData", uploadedFile).SqlDbType = SqlDbType.Image;

sqlsql

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 to float value

Hi guys, the following is the part of my query that I am having an issue.

Select....

.....

,100 * count(c_id)/(select count(c_id) From tbl_comp c,tbl_empl e where c.c_type = 'b' and c.e_id = e.e_id ) AS [Perc]

,....

From.....

Where...

That part of the statement should have to give me FLOAT values, such as 60.99 etc. however, it is giving me only the integer part (i.e. 60). I tried to cast/convert the values to float value but I coudn't.

Any idea?

Try changing

,100 * count(c_id)/(select count(c_id) From tbl_comp c,tbl_empl e where c.c_type = 'b' and c.e_id = e.e_id ) AS [Perc]

to

,100 * cast(count(c_id) as float)/(select count(c_id) From tbl_comp c,tbl_empl e where c.c_type = 'b' and c.e_id = e.e_id ) AS [Perc]

|||

Perfect!

my bad, what I did befor is

CAST(100 * count(c_id)/(select count(c_id) From tbl_comp c,tbl_empl e where c.c_type = 'b' and c.e_id = e.e_id ) AS FLOAT) AS [Perc]

|||

You can also use a higher precedence data type in the formula.

100.00 * count(c_id)/(select count(c_id) From tbl_comp c,tbl_empl e where c.c_type = 'b' and c.e_id = e.e_id ) AS [Perc]

AMB

|||Cool! that is also working.

Convert text to float in SQL Statement

Hi,

Can you guys help me out?
I m trying to sum up some varchar-typed field. I need to convert it to float before doing the summing up so I m using "Cast".

I do get the answer but its not the correct figure. My SQL statement is as follow:

SELECT Sum((Cast(Qty1 as float)) + (Cast(Qty2 as float))) as intAnswer FROM TableName

Please help.Try avoiding using flaot, pretty inaccurate.

Use decimal or numeric data types instead.|||Originally posted by Crespo-n00b
Try avoiding using flaot, pretty inaccurate.

Use decimal or numeric data types instead.

I've tried it and it still gives me a wrong answer.
I should have 45299 + 7832.5 = 53131.5, but I kept getting
13310.

Any more ideas?|||A Miracle ??

use pubs

create table answerint
(
QTY1 varchar(20),
QTY2 varchar(20)
)

insert into answerint select '45299','7832.5'

SELECT Sum((Cast(Qty1 as float)) + (Cast(Qty2 as float))) as intAnswer FROM answerint

drop table answerint

Works for me though !!!|||Crespo and a mriacle in the same post...holy cow!

Where have you been hiding out?

And Yes float is quirky...

But I would add

USE Northwind

CREATE TABLE answerint
(
QTY1 varchar(20),
QTY2 varchar(20)
)

INSERT INTO answerint
SELECT '45299','7832.5' UNION ALL
SELECT 'BRETT', 'KAISER'

SELECT SUM(
(Cast(Qty1 as float))
+ (Cast(Qty2 as float))
) as intAnswer FROM answerint
WHERE ISNUMERIC(Qty1) = 1 AND ISNUMERIC(Qty2) = 1

DROP TABLE answerint|||Brett,

I have not been hiding anywhere. Just busy with work and life you know...

Amethystium.|||Originally posted by Crespo-n00b
Brett,

I have not been hiding anywhere. Just busy with work and life you know...

Amethystium.

Thanks for the replies.
I've tried it all out and they work for single select but not when a
"SUM" is used.

But I've found out that the answer came out the way it was because there were some NULL values in QTY2. When this happened, the result would be NULL even if QTY1 contained a figure.

Anymore ideas? I'm planning to manually grab these out evaluate/convert them using ASP before doing a calculation.

Thanks

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 INT to BIGINT

Hi guys

We have a table A which has an Id column of type INT. There are currently about 260705246 rows in the table and growing at 100,000,000 rows a month. We will hit the limits of INT pretty soon at this rate. So I was asked to study the different options to convert the INT to BIGINT for the table. Conditions: (1) The downtime should be the least (2) the method should be 100% reliable and no unknown factors going into the rollout. I did my research and came up with a couple of methods:

(1) ALTER table ( I know this will not work)

(2) create a new column in the same table. repopulate the column with existing data using a job. modify all the stored procs/business objects to update/insert the new column instead of the old one. Drop the old column. rename the new column back to what it was ( or leave it as is). Recreate the indexes/FK constraints with the new column instead of the old column.

(3) create a new table B with BIGINT datatype. BCP the data from A into B. create all the indexes. drop the old tables. rename the new table to the old one.

I am suggesting method 3 as it requires least number of operations => less changes (and less places to) of screw up and less complicated.

If anyone else who has done this before can provide some feedback/do's and dont's etc I would apprecaite it.

Use SQL Management studio to change the column type from int to bigint. Click save. Tell everyone you are working hard on it, and go to lunch.

This will essentially do #1 if it's possible. If it's not, it will do #3 automatically for you. The only downside is that with that many rows (depending on how big each row is), this could take some significant time. You may want to try this on a test system first to make sure it will complete in the time you have allotted.

|||

I should have mentioned. We have SQL 2000 on production and its a 64 bit processor.

We tried #1 on an evaluation machine and it failed.

|||Right click and register the 64bits version in Enterprise manager of SQL Server 2000 32 bits and it becomes local, we used Oracle 9i the same way so I just tried it with SQL Server and it worked like a charm. So you can use Enterprise manager to change the data type or run ANSI SQL ALTER TABLE to change INT to BIGINT|||We already tried the ALTER TABLE approach and it failed.Normally I wouldnt be posting this question if its a simple ALTER statement issue. There are millions of records in the table. The server just froze when we tried it. And we cannot afford any downtime on the server for trials like these unless its a solid solution. Any other ideas?|||

Is this an identity column?

Please send me a private message with a script to recreate your table structure including indexes, and let me know if you have any foreign keys to or from this table and I'll get you an answer within a couple days. I got some disk space, processor cycles and time to spare ;-)

Anything else you can tell me about your production server would help. How many CPUs, RAM, and what disk topology? (Logs on raid-1, data on raid 5?) An approximate table size would help as well.

|||Create a blank database then create a new table with Big INT and run an INSERT INTO statement against the table to move the data, then move the new table into your database. If it works just truncate or drop the original table. The last option is to use DTS to move the table back into your database. Run some tests with a copy of your production database.|||This operation needs to be done on a PROD server which is 64 bit version. I believe DTS does not work on 64bit processor. We have tried traditional methods of (1) Insert Into (2) create a new BIGINT column in the same table, update the new column with the old col, drop the old col but with 900 million rows, it blows up. One thing is clear to me. Any solution would involve "batch" process. We cannot do a simple update or drop on the entire table. we need to do for a few hundred thousand rows at a time. As of now the only solution I have ( havent tried that out yet) that seems to work is to bcp out the table into multiple data files, and bacp back into a new table (again in batch) with BigInt alreadt defined and drop the old table and rename the new table to the old one. I am currently working on a replication issue and should be done by the end of the week. Early next week I should start doing some prototypes of the bcp solution and see how it works.|||Not really SQL Server 2005 comes with 64 bits eval versions use it to do the DTS now integration services operation to move the table back to your production SQL Server 2000 64 bits. I was a beta one tester of the product in 2001 the only known issues are pointer arithemetic problems not data migration. XP 64 bits eval can also be downloaded from Microsoft site. BTW both 64bits are AMD products because Intel's Itanium is still for server products.|||

Our PROD server is windows 2000 Data Center Edition. 64GB RAM; 32 processors. 64 bit processor.

|||SQL Server 2000 64bits does not run on Windows 2000 it runs on Windows 2003 64bits Microsoft sent us a pre beta 1 build of the product to do the testing in 2001. If it does run on Windows 2000 I am not aware of it. That said operating system is not important to what you want to do, that is move a table to SQL Server 2005 64bits on XP 64bits and sending the table back to SQL Server 2000 64bits. Just focus on data transfer between SQL Server.|||Yes. Almost every option we tried failed because of the huge data transfer. 900 mi rows is not easy to do trial and error. Yes you are right. I found out we have Win2003 Data center edition.|||

Option 1 may work, but it will take a very long time to complete.

Option 2 won't work at all, because this method won't work with identity fields.

Option 3 might work, but you would probably be better off just INSERT INTO ... SELECTing the data from one table to the other rather than using BCP.

I've got an option 4 I'm working on, but it's coming pretty slow. I got hammered last week because of the MS/Eolas spat and had to do some emergency changes for someone. Nearing a billion rows is a lot of data, took me almost 4 hours just to load test data, and that's not including building indexes.

I'm curious how your SAN performs compared to my desktop at home. A good SAN should put it to shame, but there are lots of people out there that don't know how to configure one. Coupled with the fact that you said an similiar attempt was made years ago and failed, unless you've upgraded your SAN, it's getting pretty old (And probably relatively slow). In any case, I'll have a solution for you in a couple of days (Sorry, I only run tests during the day when I'm not actively using the machine, cause the tests literally kill the machine... Multithreaded disk I/O wasn't my highest priority when I built it). I'm pretty sure I can get it down to a couple hours (meaning 2) downtime, if your SAN runs atleast as fast as my desktop. I'm sure the extra 31 processors and 62GB of ram will help some too, but not a whole lot.

|||

Thanks for your efforts Motley. I am sure our SAN must be up to date (I dont know much about the storage area yet). Time is exactly the problem I have. We do have an alternate site with exactly similar configuration as our PROD ( win 2k3 DC edition, 32 processors, 64 GB ram, SQL x64 edition etc) and if I have a solid plan I can ask for a few hours of access to the ALT site for my testing. But they will only let me use it if I have atleast reasonable confidence the solution work and when they do give me access I need to use every minute of it.

I do have other ideas in mind but like I said, I am not sure how they work with such huge data.

Thanks again.

|||

Well, my idea is that since the data load takes such a extreme amount of time to do, I'm building a process to create a duplicate table (tmpWhatever) in the background.

Unfortunately because of the sheer size of your table, the only way I see of efficiently doing so takes a few steps. Creating triggers to log changes to an audit log (insert, update,delete). Then begin loading data in the background row by row (More accurately small batch by small batch), and then once we have all the rows, replay the audit log on the temporary table (small batches) until we catch up. This should allow you (if you have the disk space to spare) to bring down the system at any point, run a stored procedure to make sure you've caught the last of the changes, drop your FK's, rename old to tmp2, rename tmp to old, and apply the FK's to new table, and you are done. I need to find out if the sp_rename stored procedure will let the indexes follow the rename or not. If it will then you'll need about 5 minutes of down time. If it won't, and you need to rebuild the indexes, then it'll take a while longer, but shouldn't be more than a couple hours.

The other question I have relates to the FK relationship. Is it valid to have a bigint primary key, as a foreign key into another table that is integer? If it is (and I don't really see why it wouldn't be, but it's possible it's not), then you can do each table separately, if it isn't then you'll need to do both processes at the same time.

convert images to hexadecimal

Hello guys,

Have any one tried to convert an image to a hexadecimal string, and saving it to sql server?

Thanks..

You might find base64 strings a bit more compact. Try this for starters. It only takes TWO lines to do the work!

<%@.PageLanguage="C#" %>

<%@.ImportNamespace="System.IO" %>

<!DOCTYPEhtmlPUBLIC"-//W3C//DTD XHTML 1.0 Transitional//EN""http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<scriptrunat="server">

protectedvoid LinkButton1_Click(object sender,EventArgs e)

{

using (StreamReader sr =newStreamReader(MapPath("TestPicture.JPG")))

{

BinaryReader br =newBinaryReader(sr.BaseStream);

byte[] data = br.ReadBytes((int)br.BaseStream.Length);

string dataToSave =Convert.ToBase64String(data);

// show it

TextBox1.Text = dataToSave;

}

}

</script>

<htmlxmlns="http://www.w3.org/1999/xhtml">

<headrunat="server">

<title>Untitled Page</title>

</head>

<body>

<formid="form1"runat="server">

<div>

<asp:LinkButtonID="LinkButton1"runat="server"OnClick="LinkButton1_Click">GetPictureAsText</asp:LinkButton></div>

<asp:TextBoxID="TextBox1"runat="server"TextMode="MultiLine"Width="100%"Height="24em"></asp:TextBox>

</form>

</body>

</html>

|||

Thanks for the code. It worked fine. But I want to convert it to hexadecimal string. Its a requirement asked by the client...

Thanks for the help

|||

Try this

TextBox1.Text =BitConverter.ToString(data).Replace("-","");

convert datetime to UTC seconds

Hi guys,
Do you know how to convert a datetime to UTC seconds:
eg: nov 17, 2005 21:00:00
UTC in seconds: 1132261200
thank a lot
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200511/1http://www.aspfaq.com/2451
"fasttrack via webservertalk.com" <u15121@.uwe> wrote in message
news:5783c74d47a66@.uwe...
> Hi guys,
> Do you know how to convert a datetime to UTC seconds:
> eg: nov 17, 2005 21:00:00
> UTC in seconds: 1132261200
> thank a lot
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Forum...amming/200511/1|||current: select datediff(second, '19700101', GetUTCDate())
given a local date:
-- use dateadd(hour, datediff(hour, GetDate(), GetUTCDate())
-- to get the current hour difference between UTC and local date
declare @.GetDateVal datetime
select @.GetDateVal = '20051117 15:00:00'
select @.GetDateVal, datediff(second, '19700101', dateadd(hour,
datediff(hour, GetDate(), GetUTCDate()), @.GetDateVal ) )
fasttrack via webservertalk.com wrote:
> Hi guys,
> Do you know how to convert a datetime to UTC seconds:
> eg: nov 17, 2005 21:00:00
> UTC in seconds: 1132261200
> thank a lot
>