Converting dates.

Kudos to y'all!!! I have this task of fixing a database table which contains dates but in a VARCHAR type column. Now I wanted to convert them to 103 format. But the problem is, some values were inserted into the database in either "dd/mm/yyyy hh:mm:ss AM/PM" or "mm/dd/yyyy hh:mm:ss AM/PM" formats since the column is VARCHAR. Is there an easy way of doing such task?no, there is no easy way

for example, is 04/05/2006 in dd/mm/yyyy format or in mm/dd/yyyy format?|||no, there is no easy way

for example, is 04/05/2006 in dd/mm/yyyy format or in mm/dd/yyyy format?

It's in either format. Some dates are in dd/mm/yyyy and some are in mm/dd/yyyy. It's an old table and I don't know for sure which date format was used for it.|||i think you missed the intent of my question :)

i was trying to point out that the answer to your question "Is there an easy way of doing such task?" is no, because there will always be these types of values that you just cannot decide|||What does this give you?

SELECT * FROM Table WHERE ISDATE(DateCol)=0

??

SELECT ISDATE('10/24/1960'), ISDATE('24/10/1960')|||hey brett, i got one for you in return

what do you get for this query --SELECT ISDATE('04/05/2006') as is1
, ISDATE('05/04/2006') as is2

mwua ha ha ha hahahaha !!! :) :) :) :) :)|||Good point, bottom line, you are hosed

unless you have a column that identifies the format|||eh, it's not so bad. worst case you'll convert wrong and be off by 9 months. no big deal right? :)|||eh, it's not so bad. worst case you'll convert wrong and be off by 9 months. no big deal right? :)sounds like the attitude of a certain large software company which shall remain nameless...

:)|||sounds like the attitude of a certain large software company which shall remain nameless...

yea, they drill it into you, it takes a while to feel clean again. :)

did I say 9? I meant 6. even better!|||either "dd/mm/yyyy hh:mm:ss AM/PM" or "mm/dd/yyyy hh:mm:ss AM/PM" formats since the column is VARCHAR.

How much rows are you having in your table..?

Second thing, If you query your table, how you identify dates..? (05/04/2006 - dd/mm/yyyy or 04/05/2006 - mm/dd/yyyy )

Consider the points given below, remember you didn't provide enough information...

1. You can update all rows which is having 'day' more than 12. (i.e. 13/01/2006 or 01/13/2006).

2. If you can not identify date (05/04/2006 or 04/05/2006), than date data does not make any difference to you, because in this situation you can not get correct date.

3. Inform your higher authority & update your table, this way your new data will not be wrong.|||How much rows are you having in your table..?

Second thing, If you query your table, how you identify dates..? (05/04/2006 - dd/mm/yyyy or 04/05/2006 - mm/dd/yyyy )

Consider the points given below, remember you didn't provide enough information...

1. You can update all rows which is having 'day' more than 12. (i.e. 13/01/2006 or 01/13/2006).

2. If you can not identify date (05/04/2006 or 04/05/2006), than date data does not make any difference to you, because in this situation you can not get correct date.

3. Inform your higher authority & update your table, this way your new data will not be wrong.

I have exactly 82,545 rows on this table and is expected to grow for a few more days since this table is still in use by one application. Currently, this application (which I made opf course) is following the dd/mm/yyyy format. This means that the SQL syntax used within the application follows this format. Therefore, the dates are inserted in dd/mm/yyyy format. As I said, this table is old and the old application that uses this table inserts date in mm/dd/yyyy format. The old application was stupid 'coz it formats date depending on th system setting and inserting it into the table as is. My only mistake is that I should've fixed the table before I started the application. For one year now, the old and the current application is inerting date values into the table as VARCHAR instead of DATETIME. Now that I'm updating the application ('coz I've managed to create it not to be dependent on the system settings), I want to start inserting date values as DATETIME so that it would work on BETWEEN statements properly as well as using SQL Server's built in functions such as DATEDIFF, DATEADD, etc. as I'll be using SQL Server Agent to execute T-SQL commands which involves dates.|||I have exactly 82,545 rows on this table and is expected to grow for a few more days since this table is still in use by one application. Currently, this application (which I made opf course) is following the dd/mm/yyyy format.
You have to take pain to replace the VARCHAR column to DATETIME column, choose the Server idle time and do it at single shot because you don't have any other option.

There are few ways to update your DATETIME columns...

1. You can create new table & copy all data from old table to new (using DTS).
2. Add new column in the existing table & update it (you can write query for it & after updating remove old column).
3. First update rows which is having 'day' more than 12, then update other rows.
4. Don't forget to check column references.

Note : You will get ambiguous / incorrect dates (which are below 12) because you will not identify dates between 1 to 12 (date or month).

By converting VARCHAR to DATETIME column you can eliminate future incorrect / ambiguous data. You have to take this risk, else I didn't find any other solution...|||Do you have a time stamp on your data that would indicate whether the date was entered under the old system or under the new system? If so, you can update the dates with two separate statements.

converting date/time to just date?

I have a table that's of type date/time (i.e. 01/01/1900 00:00:00).

What I want is to do the following:

Say you have these records:

person | date-time
---+--------
jim | 06/02/2004 00:05:52
jim | 06/02/2004 05:06:21
jim | 06/02/2004 05:46:21
jim | 06/15/2004 11:26:21
jim | 06/15/2004 11:35:21
dave | 06/04/2004 09:35:21
dave | 06/04/2004 11:05:21
dave | 06/06/2004 10:34:21
dave | 06/08/2004 11:37:21

I'd like the results to count how many days and return

person | days
---+---
jim | 2
dave | 3

How would I do this?

--
[ Sugapablo ]
[ http://www.sugapablo.com <--music ]
[ http://www.sugapablo.net <--personal ]
[ sugapablo@.12jabber.com <--jabber IM ]On Tue, 22 Jun 2004 15:27:52 -0000, Sugapablo wrote:

>I have a table that's of type date/time (i.e. 01/01/1900 00:00:00).
>What I want is to do the following:
>Say you have these records:
>person | date-time
>---+--------
>jim | 06/02/2004 00:05:52
>jim | 06/02/2004 05:06:21
>jim | 06/02/2004 05:46:21
>jim | 06/15/2004 11:26:21
>jim | 06/15/2004 11:35:21
>dave | 06/04/2004 09:35:21
>dave | 06/04/2004 11:05:21
>dave | 06/06/2004 10:34:21
>dave | 06/08/2004 11:37:21
>I'd like the results to count how many days and return
>person | days
>---+---
>jim | 2
>dave | 3
>How would I do this?

Hi Sugapablo,

SELECT person,
COUNT(DISTINCT CONVERT(CHAR(8), date-time, 114)) AS days
FROM YourTable
GROUP BY person
(untested)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)sqlsql

Converting Date Data type in stored procedure

HI Experts...

I am using SQL SERVER 2005 standard edition

I have encountered a problem regarding converting date data type in stored procedure

As i was having problem taking date as input parameter in my stored procedure, so, then I changed to varchar (16) i.e.

CREATE PROCEDURE sp_CalendarCreate
@.StDate VARCHAR(16) ,
@.EDate VARCHAR(16),

then I am converting varchar to date with following code

DECLARE @.STARTDATE DATETIME
DECLARE @.ENDDATE DATETIME

SELECT @.STARTDATE = CAST(@.STDATE AS DATETIME)
SELECT @.ENDDATE = CAST(@.EDATE AS DATETIME)

When I try to execute the procedure with following code

execute sp_CalendarCreate @.stdate='12-1-06',@.edate='20-1-06'

but it gives me following error

Msg 242, Level 16, State 3, Procedure sp_CalendarCreate, Line 45
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

Can any one tell me the solution of that problem (at ur earliest)

regards,

Anas

execute sp_CalendarCreate @.stdate='1-12-06',@.edate='1-20-06'

|||

Thanx for your reply....

yes i had sorted that out b4 u replied me... i.e. sql server uses date format mmddyy or yyyyddmm (american format)

and i was using UK/european standards

btw thanx very much for support

regards,

Anas

|||It is best to use ISO unseparated date format (YYYMMDD) or ISO 8601 (YYYY-MM-DDThh:mm:ss.nnn) datetime format for specifying values. This way you don't have to worry about DATEFORMAT or language settings of the server. Also, using varchar and converting to datetime is not a good and clean approach. Use the correct data type for the values so you can get the best benefit in terms of type checking, domain validations etc. Additionally, with your approach you will get into performance problems because parameter sniffing of the variables used in the SELECT statement will not work due to use of local variables and not parameters. See the whitepaper on compilation, caching in MSDN for more details.

Converting Data Types

I need to be able to convert a numeric data type to a text data type.

I've tried the TO_CHAR and the DBCONVERT functions but I am returned an error stating that they are not recognized functions.

I'm using the Query Analyzer on a Windows 2000 SQL Server.

Any help would be appreciated.

TechRickYou can use the cast or convert functions.|||Thanks,

I was just reading up on the CAST function. However, I've not been able to make it work yet.

In correction to my original post, I need to take a MONEY data type and convert it to a TEXT data type. I hope this possible.

Thanks again,

TechRick|||Try the following from the pubs database and titles table:

SELECT price, cast(price AS varchar(30)) FROM titles|||You can also do it this way:

select cast(cast(price as varchar(30)) as text) from titles

But since the conversion is implicit between varchar and text you don't need to do this.|||Thanks again for the help. Your suggestion works great but when I try to update the data I get this:

Server: Msg 260, Level 16, State 1, Line 31
Disallowed implicit conversion from data type varchar to data type money, table..., column 'PRICE'. Use the CONVERT function to run this query.
Server: Msg 257, Level 16, State 1, Line 31
Implicit conversion from data type money to varchar is not allowed. Use the CONVERT function to run this query.

I'll let you know once I get it worked out.

Best Regards,
TechRick|||The way the error is reading is that you are trying to put a varchar into a money column - is that correct ?|||Yes, I have a large list of items collected from various tables and there are prices associated with these items. A good majority of these items have no price (.0000) and I would like to exchange the .0000 price for a 'CALL' or something similar.

I could not update the records with a "text" substitute so I thought I would convert the data type to TEXT so I can plug in whatever I want.

Thanks again,
TechRick|||Why did you choose text over varchar ? Could you post your update statement as well as the definition of the table you are updating ? You have to explicitly convert varchar to money and vice-versa using either cast or convert. But I am a little confused about the first error you received - why are you trying to put a varchar into a money column ?|||You'll have to excuse me, I've only been working with SQL for less than 3 weeks. I've been learning as I go (with my Dummies and Sam's books and this forum). I've already written a few complex queries and as for this particular situation, this is the last obsticle I need to overcome to put this query to rest.

Based on the error I was getting I assumed that I was doing something wrong but I didn't have the time to completely research it. I'll pick it back up on Monday. I think I need to understand the convert and cast functions better before I can make use of them. I'll be working towards that end.

As for now, I'm away from work and don't have easy access to the code. I'll post it Monday after I tinker a little more.

Thanks for the help.

TechRick|||Ah, by "convert" you mean that you would like to change the data type of a column from money to varchar so that you can store a mix of data in a single column. The easy answer is to just change the data type of the column in Enterprise Manager. If SQL Server can find a reasonable way to preserve the data, it will. Thereafter you can store any character data in the column, e.g. "Call" or "Operators are standing by!".

The "correct" answer is rather different, and a valid subject for debate. If you do not have a price for an item, the correct representation in the database should be different from a free item. For example, you might use the value NULL to indicate "call for price" and $0.00 for a free item. Alternatively, it may make more sense in your application to have a separate means of flagging items for which you don't want to publish a price, qualify for free shipping, have quantity discounts, ... . More columns or tables may be needed.|||Agreed about the 'correct' answer. Fortunately and unfortunately, I didn't write the application so I'm working with what's there.

I managed to get it taken care of by adding the convert on my select statment.

SELECT UPPER(ITEM), DESCRIPT, Q_STK, QTY_RESERVE, CAST (SELLPRICE AS VARCHAR(15)),...

By doing it this way I am able to manipulate the data any way I want.
Add 'CALL', 'FREE', etc.

Thanks all for the help. One last item and this query is behind me.

Best Regards,
TechRick|||Originally posted by rnealejr
Try the following from the pubs database and titles table:

SELECT price, cast(price AS varchar(30)) FROM titles

rnealejr, I noticed as I was going back through all these posts that you were right on the money a long time ago! Thanks for the help. I think I was trying to perform an update on the column using the cast rather than taking the data in converted from the start. Anyhow, just wanted to thank you for your help. Too bad I had to learn the hard way.

Best Regards,
TechRick|||Thanks for the email and compliment as well as a good pun (right on the money) - I enjoyed that.

Good luck.|||Another handy tool for fudging return values within a query is throwing in a CASE, e.g.:

select Description, ServingSize, case when Price<>0.0 then Convert(VarChar,Price) else 'Call' end as 'AdvertisedPrice'
from PiecesParts where Fused=1

Note that there are two slightly different versions of CASE. One lets you test a single expression against multiple values, while the other lets you test multiple expressions.

You can swindle a lot of logic into a CASE or nested CASEs. For example, it could check for quantity price breaks or apply discounts based on data from other tables or variables. The result is just another (computed) column in the recordset returned from the query. (As such, it isn't writable.)

Converting CHAR/VARCHAR/TEXT into NCHAR/NVARCHAR/NTEXT!

Hi,
We are in process of converting all of the data type of the fields from CHAR/VARCHAR/TEXT into NCHAR/NVARCHAR/NTEXT (DBCS). Having more than 900 store procedure its look like real pain to make modification in all of the SPs.

After failed to find any help from GOOGLE, I am posting this request. I am basically looking for any automated tool which are convert data type in SP based on the field of the table used in the SP. Or at least which can provide me some sort of list which can helpful for doing manual reactoring.

PLEASE HELP ME!!!

Thanks,

Firoz AnsariIf you are, in batch, converting all types, you couls just script the database using Enterprise Manager, and then do a search and replace for each of the types. Be aware that converting from varchar to nvarchar could cause problems if rows already contain over 4000 characters.|||I have just created a small VB utility program which can take list of files (.sql files) and uses regular expression to replace data types in SPs files. I am still looking for some automated tool.

Regards

Converting bytes [] to an SQL CE 3 image type to store

Hi,

I was wondering if anyone knows how to convert an array of bytes to an SQL CE 3 image type and vice versa.

I am using the SDF Signature control and I would like to store the signature as an Image. It needs to be an image so it can be synced with a desktop access 2003 database.

Cheers

Simon

I have got around this by changing the Image type to nText then converted the byte array to a base64 string and this works a treat.

Cheers

Simon.

|||

Better way to do it would be to create memory stream from byte array from SQL and pass it to bitmap constructor:

Bitmap bmp = new Bitmap(new MemoryStream(byteArrayFromSqlCe));

Converting Blob fields to Text on a Report

On SQL Reporting Services, Blobs (type = image) do not even get exposed
to the report. Note: I am using blobs since I need virtually
unlimited text. So, to get the data out of blobs, I normally use ADO
methods of getchunk and actualsize. I tried creating a class with the
logic that I needed. I could pass it SQL and it would return the value
of the blob as a text string. Then I created the following Custom Code
in the report:
Public Function BlobText(strColumn$, strTaskID$) As String
dim t as object
t = createobject("MyReportClass.Functions")
BlobText = t.blob2text("MySQLServer","select " & strColumn & _
" from SQLDatabase..task where ID = '" & strTaskID & "'")
End Function
When I preview the report it works perfectly. When I deploy the
report, the textbox that calls the method puts "#Error" on the report.
There does not seem to be any meaningful log to help.
Anyone have any guesses?
Thanks,
SteveHi Steve,
Welcome to use MSDN Managed Newsgroup!
From your descriptions, I understood you would like to know how to write
embedded code in the reporting services. If I have misunderstood your
concern, please feel free to point it out.
Based on my knowledge, you are recommanded to read the article below
Writing Custom Code in SQL Server Reporting Services
http://blogs.sqlxml.org/bryantlikes/articles/824.aspx
Embedded Code In Reporting Services
http://odetocode.com/Articles/130.aspx
If this still does not resolve your issue, would you please generate a
sample rdl file with your function based on AdventureWorks database and
send it to me? my direct email address is v-mingqc@.online.microsoft.com
(remember remove "online" before you click SEND as "online" is only
prepared for SPAM), you may send the file to me directly and I will keep
secure.
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.|||Lost Customer, Close: Not Resolved

converting binary data to another data type

I have a client application written in C++ to takes an array of doubles and
stores it into a SQL Server 2000 database as an image data type.
We just upgraded to Visual Studio 2005 and SQL Server 2005.
Can the Reporting Services take this image data and convert it to an array
of doubles so that it can be displayed using Reporting Services?
Thanks,
GloriaGloria (Gloria@.discussions.microsoft.com) writes:
> I have a client application written in C++ to takes an array of doubles
> and stores it into a SQL Server 2000 database as an image data type.
> We just upgraded to Visual Studio 2005 and SQL Server 2005.
> Can the Reporting Services take this image data and convert it to an array
> of doubles so that it can be displayed using Reporting Services?
I don't know Reporting Services, so I canot answer the question with any
certainty, but my gut feeling is that you would have to call some piece
of code to unpack that array. Tip: there is a Reporting Services newsgroup,
microsoft.public.sqlserver.reportingsvcs.
The main reason I post, is that I can't refrain from making the comment
table design appears a bit unorthodox to me. Or to put it more bluntly, a
serious violation of first normal form since it includs a repearing
group. The normal way of storing the data would be have a subtable,
and store one float value on each row.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Converting a type into another one

Hello,

I've got two tables. An old one an a new one. Called "tbl_Filme" and "tbl_Filme2". The differents are that the data types are a little bit smaller as in the old one.

So I've got a column wich should contains a datetime. Unfortunattley the datatype is just a normal date and not "smalldatetime" (the "tbl_Filme" was created with MS Access 2003). So I get an error message:

1> INSERT INTO tbl_Filme2 (Titel, Genre, Medium, Anzahl, Qualit?t, Filml?nge)
2> SELECT Titel, Genre, Medium, Anzahl, Qualit?t, Filml?nge
3> FROM tbl_Filme
4> go
Meldung '298', Ebene '16', Status '1', Server 'PREDATOR\SQLEXPRESS', Zeile 1
'The conversion from datetime data type to smalldatetime data type resulted in a smalldatetime overflow error.'

My goal is that I can copy the old table in the new table. If I solved that problem maybe you can help me with that problem, too:

I've programmed a programm for accessing the table in VB 2005. I've got theire a GridView where I can change the cells and a button to sync. the changed values with the SQL Database (2005). But finished with the update, the values aren't updated.

I thought this might be an error of my programming but first I tried to use Access for this (created a new Access Project *.adp). But there was an error message "Could update the RecordSet". Is this beacause of the SQL Server 2005 (maby I must wait untill an update, because Access 2003 is older then SQL 2005?)?

Thanks in advance.

OK, first problem. Rather than doing a automatic convertion between the old and the new one, I would check these incompabilities and solve them. Appearantly the datetime are two big / or too small to be reflected in a smalldatetime. So knowing that smalldatetime is defined in the scope of

from January 1, 1900, through June 6, 2079

You should use the query to identify these "old" ones and UPDATE them to a acceptable format for datetime (as above).

Then there should be no more problem with importing them. If you need the dates prior to 1900 or after 2079 you have to use the datetime data type.

Second problem: ""Could update the RecordSet"." This doens′t sound lkike a problem :-). Assuming that the error message is ""Could NOT update the RecordSet".", I would investigate the command that are passed to the Provider. Did you call the UPDATE method ? There has to be an inner exception which should explain the error message a bit more in detail. This would be more helpful to solve your problem.

HTH, jens Suessmeyer.

|||

Hello,

thank you for your answer. The reason why I wanted to choose the datetime is, that I want to write a time value. Is their any data type for only time without a date in it? But I will try your suggestion.

The second problem occured in Microsoft Access. Well, I startet a new project with a new connection to the SQL Server (with the user-ID "sa").

Then I saw all the tables, which were in the database. But I can only read, I cannot change anything. If I try to change a value the error "Couldn't update the RecordSet" occures.

|||

"Is their any data type for only time without a date in it"

-No.

" But I can only read, I cannot change anything"

Create a primary key in the access enviroment on the tables, that should help.

HTH, jens Suessmeyer.

|||

Unfortunattely their is a message (while opening the window where I can edit the data types of the columes) which sais that it is not possible to save the changes because the used SQL Server is newer than the Access version.

Edit:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=196509&SiteID=1

The problem is written their, too. No useable way to solve the problem. Maybe I will wait until the Office 2007.

I have got one question contains the "SQL Server Management Studio". Where do I get this GUI for the SQL 2005. I don't find it.

Edit:

Sorry, found it. :-)

Edit:

Something is strage. After I "played" a little bit with the SQL Server Management Studio" I tried it again with the Microsoft Office Access 2003 and now it works!

Converting a type (e.g. Decimal) BEFORE writing to ResultSet ?

As well known a DATE or TIMESTAMP type can be converted (to VARCAHR) after s
election but BEFORE
writing to the ResultSet by using the CONVERT function e.g.
SELECT CONVERT(char(10), MYTIMESTAMP, 101)) FROM ... WHERE ...
Is there something similar if the original field is a DECIMAL/NUMERIC field?
E.g.
SELECT DEC_CONVERT(char(30), MYDECIMAL, '###########0.00') FROM .... WHERE
...
GeorgeGeorge Dainis wrote:
> As well known a DATE or TIMESTAMP type can be converted (to VARCAHR)
> after selection but BEFORE writing to the ResultSet by using the
> CONVERT function e.g.
> SELECT CONVERT(char(10), MYTIMESTAMP, 101)) FROM ... WHERE ...
> Is there something similar if the original field is a DECIMAL/NUMERIC
> field?
> E.g.
> SELECT DEC_CONVERT(char(30), MYDECIMAL, '###########0.00') FROM ....
> WHERE ...
> George
Just use CONVERT or CAST:
Select CONVERT(char(30), MyDecimal) From ...
Select CAST(MyDecimal as char(30)) From ...
David Gugick
Imceda Software
www.imceda.com|||You can also use function STR.
Example:
declare @.d decimal(8, 2)
set @.d = 50.25 / 2.00
select @.d, str(@.d, 8, 2)
go
AMB
"George Dainis" wrote:

> As well known a DATE or TIMESTAMP type can be converted (to VARCAHR) after
selection but BEFORE
> writing to the ResultSet by using the CONVERT function e.g.
> SELECT CONVERT(char(10), MYTIMESTAMP, 101)) FROM ... WHERE ...
> Is there something similar if the original field is a DECIMAL/NUMERIC fiel
d?
> E.g.
> SELECT DEC_CONVERT(char(30), MYDECIMAL, '###########0.00') FROM .... WHER
E ...
> George
>|||>From the documentation ... "CONVERT converts a character string from
one character set to another. The datatype of the returned value is
VARCHAR2." So what you are seeing is an implicit conversion to varchar2
because you are using a function that accepts a char as input.
Look at the functions TO_CHAR(), TO_DATE(), TO_NUMBER() and CAST() for
type conversion ...
http://download-west.oracle.com/doc.../b10759/toc.htm|||Oh that was strange ... I accessed the question through
comp.databases.oracle.misc but google tells me that the answer will get
posted to a sqlserver group? hmmm.|||On 16 Feb 2005 04:51:03 -0800, David Aldridge wrote:

>Oh that was strange ... I accessed the question through
>comp.databases.oracle.misc but google tells me that the answer will get
>posted to a sqlserver group? hmmm.
Hi David,
The orinal question was crossposted to a total of three groups:
* comp.databases.oracle.misc
* microsoft.public.sqlserver.programming
* comp.databases.oracle
The followup-to was set to only the SQL Server group. The use of CONVERT
in the original question suggests that this is indeed a SQL Server related
question. I have no idea why the original poster has included two Oracle
groups in his crossposting.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||"George Dainis" <george.dainis@.bluecorner.com> wrote in message
news:cuu58t$irh$04$1@.news.t-online.com...
> As well known a DATE or TIMESTAMP type can be converted (to VARCAHR) after
> selection but BEFORE
> writing to the ResultSet by using the CONVERT function e.g.
> SELECT CONVERT(char(10), MYTIMESTAMP, 101)) FROM ... WHERE ...
> Is there something similar if the original field is a DECIMAL/NUMERIC
> field?
> E.g.
> SELECT DEC_CONVERT(char(30), MYDECIMAL, '###########0.00') FROM ....
> WHERE ...
> George
>
Also well known are the TO_CHAR, ROUND, and TRUNC functions -- and don't
forget the CAST operator ;-)
++ mcs

Converting a string of binary numbers to a binary datatype

I have a varchar field which holds numeric (binary) data e.g. '00101111' and I want to convert this to a binary data type with the value e.g. 0x00101111

But when I try the following SQL:

select top 5 flag2,convert(binary,flag2) flag2_as_binary from my_table

I get:

flag2 flag2_as_binary
--- -------------------
00000000 0x303030303030303000000000000000000000000000000000 000000000000
00000000 0x303030303030303000000000000000000000000000000000 000000000000
00000000 0x303030303030303000000000000000000000000000000000 000000000000
00000100 0x303030303031303000000000000000000000000000000000 000000000000
00000100 0x303030303031303000000000000000000000000000000000 000000000000

(5 row(s) affected)

I want some SQL that will return the following (with flag2_as_binary as a real binary datatype):

flag2 flag2_as_binary
--- -------------------
00000000 0x00000000
00000000 0x00000000
00000000 0x00000000
00000100 0x00000100
00000100 0x00000100

(5 row(s) affected)

Thanks.The sql binary datatype is actually displayed hexidecimal base 16 usinge characters 0-9 and A-F, not base 2.

converting a number into a date type using an expression

I am loading data from an iseries into a sql server 2005 DB. Our dates are stored as a numeric value in a format of CYYMMDD where C = Century indicator 20'th is 0 and 21'st is 1, YY = Year, MM = Month and DD = Day!

Today would be 1070701. Now I want to use a derived column which which would be of type date using an expression to do the conversion.

Usually, we would add 19000000 to the number to give us 20070701 then I'd convert it to a string and then substring into a date format.

I'm just getting started with SQL 2005 so I don't know how to do this using an expression.

Any help would be greatly appreciated.

Thanks,

Gray

I think your approach sounds valid. You're going to have to parse it with substring and build it into a date format. Then you can cast the string to a date/time field.

(DT_DBTIMESTAMP)(substring([YourColumn + 19000000],x,y) + "/" + .......)

|||

Hi Phil,

Thanks for the info ... it really helped ... here's my final expression ...

(DT_DBTIMESTAMP)(SUBSTRING(((DT_STR,8,1252)(YearMonthDay + 19000000)),5,2) + "/" + SUBSTRING(((DT_STR,8,1252)(YearMonthDay + 19000000)),7,2) + "/" + SUBSTRING(((DT_STR,8,1252)(YearMonthDay + 19000000)),1,4))

Thanks again,

Gray

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

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 when multiplying smallints

Hi I have two columns of type small int that cause overflow when
multiplied.
SELECT Convert(Bigint,Quantity*UnitCost) FROM Transactions
SELECT Convert(varchar(12),Quantity*UnitCost) FROM Transactions
Arithmetic overflow error converting expression to data type smallint.
What is the correct way to select this?
ThanksTry,
SELECT cast(Quantity as bigint) * UnitCost FROM Transactions
AMB
"hals_left" wrote:

> Hi I have two columns of type small int that cause overflow when
> multiplied.
> SELECT Convert(Bigint,Quantity*UnitCost) FROM Transactions
> SELECT Convert(varchar(12),Quantity*UnitCost) FROM Transactions
> Arithmetic overflow error converting expression to data type smallint.
> What is the correct way to select this?
> Thanks
>|||Which of those statements produce the error? And do you know what values are
producing the error? Small int can go upto 32,767
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"hals_left" <cc900630@.ntu.ac.uk> wrote in message
news:1123850544.468656.282800@.g14g2000cwa.googlegroups.com...
Hi I have two columns of type small int that cause overflow when
multiplied.
SELECT Convert(Bigint,Quantity*UnitCost) FROM Transactions
SELECT Convert(varchar(12),Quantity*UnitCost) FROM Transactions
Arithmetic overflow error converting expression to data type smallint.
What is the correct way to select this?
Thanks|||SELECT Cast(Quantity as Bigint)*Cast(UnitCost as Bigint) FROM Transactions
works...but best option?
Lee-Z
"hals_left" <cc900630@.ntu.ac.uk> wrote in message
news:1123850544.468656.282800@.g14g2000cwa.googlegroups.com...
> Hi I have two columns of type small int that cause overflow when
> multiplied.
> SELECT Convert(Bigint,Quantity*UnitCost) FROM Transactions
> SELECT Convert(varchar(12),Quantity*UnitCost) FROM Transactions
> Arithmetic overflow error converting expression to data type smallint.
> What is the correct way to select this?
> Thanks
>

convert varchar to smalldatetime

A table contains a field of type smalldatetime
The feed has this field as string i.e. '060830'
Would like to insert this string date into the field of the table which has a datatype of smalldatetime.

Is this the correct way to convert a string to smalldatetime?

declare @.DateFeed varchar(6)
declare @.TradeDate smalldatetime

set @.DateFeed = '060830'

set @.TradeDate = convert(smalldatetime, 6 + '-' + 8 + '-' + 30)
print @.TradeDate

Thanks

Use the following query...

Prefixing the century value on your data & casting the result as datetime

Code Snippet

Select Cast('20' + '060830' as smalldatetime)

|||

Use function CONVERT, with style 12. Let SQL Server decides the century (0 - 49 --> 2000 / 50 - 99 --> 1900).

select convert(smalldatetime, '060830', 12)

go

AMB

Convert Varchar to number

Using SQL 2005. I have a field with dollar amounts but field type is VarChar. Need to convert to number to sum. What's the best way to do this or how can I sum a field type Varchar that has dollar amounts. Thank you, Davidfunction = cast
datatype = money
as in


declare @.c1 varchar(10), @.m1 money
select @.c1 = '123.45'
select @.m1 = cast(@.c1 as money)
select @.m1|||Thank you very much. Works great. David

convert txt to money data type

Hi,

I'm using the data type "money" in my SQL database and want to convert what's in txtPrice_textBox to the "money" format. I'm currently using the following code:

' objectCym.price = Convert.ToInt16(txtPrice_textBox.Text) '

Will this work? Is there any reason I should stay away from the "Money" data type?

Thanks,

David

I think you can use Money I only advice not using money when you are have precision problems which happens. I think you should change Int16 to decimal and run your code. Try the link below and look at the shopping Cart code for sample code. Hope this helps.

http://asp.net/CommerceStarterKit/docs/docs.htm

|||you can do a convert.todecimal or convert.todouble. AFAIK there is no reason you should stay away from Money types.|||

Thanks for the help,

dp

Convert Text to Time

Dear all,

I have 30 tables with the same stracture (01 to 30). One of the fields is duration but has a text data type.

Is there a way to convert the duration field into "Time" date type with format "Long Time" using one query only?

If i have to have one query for each table, can i create a new query or a procedure through a command button that runs all the queries?

Thank you

GeorgeSQL Server is pretty good about implicitly converting text to time. If the time for is really odd, then you may need to use the CONVERT function or create a custom function.

Can you do this for all 30 tables in a single query? MAYBE using a union query, but don't count on it.|||I am not using SQL Server but Microsoft Access.

I time had an odd format but i have managed to manipulate it to the format 00:00:00.

I only need to convert the field from text to date/time. I have tried convert() and CDate() but i cannot make it to work.

My table is named "01" and the field "Duration".

Can anyone provide me with the full code to make the change.

I will use "Macros" and "RunSQLQuery" to make the converion for all tables.

This is the last problem i have to solve in order to make it work. I have been working on this database for the last week.

Please note that i am new in SQL with MS Access. I have started only one moth ago|||I would suggest that you post this question in the MS-Access (http://www.dbforums.com/f84) forum. They have more experience with the Access GUI and might be able to give you better suggestions.

I tried using Cdate("13:23:45") in an Access query, and it worked nicely for me. I suspect that that is the conversion that you need, but I'm not certain about what code you need to derive a properly formatted time string.

-PatP|||Ok, I thought of something else.

There is no need to change the data type of the table. I just created a query with all the fields of the table but for Duration i put: CDate([Duration]). The query returns the results as Date/Time and from there i can perform the calculations i require.

It works.

Thanks again, problem solved.|||Gee, you just have to love it when you inadvertanly solve a problem!

-PatP

Convert Text to Time

Dear all,

I have 30 tables with the same stracture (01 to 30). One of the fields is duration but has a text data type.

Is there a way to convert the duration field into "Time" date type with format "Long Time" using one query only?

If i have to have one query for each table, can i create a new query or a procedure through a command button that runs all the queries?

Thank you

GeorgeFirst, you will need to use the CAST or CONVERT SQL function to make your text a datetime field (assuming you need the seconds in the time otherwise smalldatetime will work also). Then use the DATEPART function to return just the time portion of the field.|||I am new in SQL and the book i use doesn't offer much help in th convert function.

Can you give me the sql statement to convert the field duration ofthe table 01 from text to datetime?

Thanks|||MSDN is a big help. See the documentation on Convert() (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_2f3o.asp) either online or in your copy of Books Online (which is installed as part of the SQL client tools).

-PatP|||I still can't figure it out.

I use the following:

UPDATE 01
set CONVERT (datetime, Duration);

and

UPDATE 01
set CAST (Duration as datetime);

I get a syntax error after (|||You'll have to post at least a few lines of code for me to be able to help you. I can't figure out what you want from what you've posted so far.

-PatP|||My table is named "01".

One of the fields is "Duration".

The table is imported from a csv file. The Duration is not in a valid date/time format so i have to import it as "Text", modify it and convert is to "Date/Time".

I can easily do this from the design view of the table but since i have 31 similar tables, i require a faster way (i don't want to go through all 31 tables to make the changes, it takes too long). The fastest way i can think is through SQl.

So, it would be much apreciated if someone can provide me with the command to update the date type of "Duration" field from "Text" to "Date/Time" using SQL or a Macro.

I will use the command to do the same change for all 31 tables.

This is the last problem i have to solve before i can make the database work.