Conversion

[genuineblonde]

I am a major newbie...so please be gentle.

I have the following select statements.

SELECT Download_Schedule.Machine, Download_Schedule.EndDateTime, Download_Schedule.ordnumber, Download_Schedule.customer, Download_Schedule.Qty, Download_Schedule.brand, Download_Schedule.BagSpecNo, Download_Schedule.Hours, Download_Schedule.StartDateTime
FROM Download_Schedule Download_Schedule

I need to convert Download_Schedule.Qty to a number and Download_Schedule.EndDateTime to a date and time configuration to be used in an Excel Calculation. Both Items come in as Text.

Example Quantity: 1501
Example EndDateTime: 2004-11-04 08:43:00


I would really appreciate your help.

 

[SQLBill]

I'm assuming they are stored as VARCHAR or CHAR in you SQL Server database.

SET DATEFORMAT YMD
SELECT CAST(Qty AS INT),
       CAST(EndDateTime AS DATETIME)
FROM Download_Schedule

Refer to the BOL for more information on CAST.

-SQLBill

BOL=Books OnLine=Microsoft SQL Server's HELP
Installed as part of the Client Tools
Found at Start>Programs>Microsoft SQL Server>Books OnLine

Posting advice: FAQ481-4875

 

[SQLSister]

Bill's solution will work as long as they really only contain numeric or date time data. Data stored in character type fields rather than the correct data type for the information often contains data which cannot be converted such as wrong dates (Feb 30, 2004) or comments rather than a number, etc. You may need to fix these problems. If this is the type of data you want stored it would be best in the long run to change your data types to int and date time. This would preseve data integrity and speed up querying as things would not need conversion.

Questions about posting. See faq183-874

 

[genuineblonde]

The data is stored in a Notes database, I am using Microsoft query to download the data into a spreadsheet. However, the data has to be converted because it all comes in as text rather than numeric.

The SQL statements are generated when I create the query, so I haven't stored the data anywhere.

 

[SQLBill]

This forum is for Microsoft's SQL Server. It's not for Structured Query Language. All SQL is not created equal, so the help we give you here will be for SQL Server not NOTES, or anything else.

I suggest posting your question in an appropriate forum.

-SQLBill

Posting advice: FAQ481-4875