SQL 2012 :: Syntax To Convert From Ounces To Pounds In Format Of 6 Lb 3 Oz
Jun 3, 2015
I'm very new to sql and it's syntax (access users), I've played around with various combinations, how would I get the weight to display as pounds and ounces? the field type is varchar
This works to convert, but number doesn't display the way I want:
(dbo.BIRTH_WT_NUM / 0.035274) AS [Birth weight in grams]
I am stuck at one place, where I have to convert CSV format file data into SAP IDOC format file. In SSIS we don't have any such SAP adapter (though we have .NET Data Provider for mySAP suite [SSIS SAP Adapter] but this is still not fully supported by Microsoft, plus it doesn't have feature to convert data into IDOC format) that can do this. Can someone here please provide me some pointers on any third party adapters available in market to do this job or if anyone has already developed some custom approach to achieve this task?
Your quick response on this is highly appreciated.
Hi, I have a report which displays monetary amounts. I have set the format of the table cells to c0, and everything is showing in dollars. However, I need this to show as pounds and can't seem to find out how to do this. I have checked the regional settings on my PC which are correctly set to English (United Kingdom). I have also tried going to tools, options in the report and then changing the language to "Same as Microsoft Windows" under International Settings but this does not seem to have done the trick. Also I have to enter date parameters in as mmddyy as opposed to ddmmyy. Does anybody know how I can change these things? Please help, it's driving me up the wall!!
E.g, i have a store procedure. The start date is long date (4/15/2007 3:00pm). i want to select the start date with a particular date (short date format 4/15/2006). Thanks in advance.
I have a table that has a DATE field named. AccountingDate that is in the format YYYY-MM-DD. It's not a VARCHAR field. I simply want to convert this date field into the format MM/DD/YYYY and call it New_Accounting_Date.
I've played with various combinations of CAST & CONVERT but haven't been able to get it to work.
Below is my latest effort which returns the error:
Incorrect syntax near the keyword 'as'
What code would work to return a MM/DD/YYYY value for New_Accounting_Date?
Select GLBATCH.AccountingDate, convert(GLBATCH.AccountingDate as date),101) AS New_Accounting_Date from GLBATCH
SELECT DATEADD(day, DATEDIFF(day, 0, LastDateIn), 0) AS Date_Checked_In, COUNT(*) AS No_Files FROM tblFiles WHERE (CONVERT(datetime, LastDateIn, 103) >= DATEADD(day, DATEDIFF(day, 10, GETDATE()), 0)) GROUP BY DATEADD(day, DATEDIFF(day, 0, LastDateIn), 0) ORDER BY DATEADD(day, DATEDIFF(day, 0, LastDateIn), 0) DESC
I want Date_Checked_In not to show the time. I have tried using convert on the 1st line to just show the date but keep getting a syntax error.
I'm new here so I'm not sure if this is the correct spot to post this question but here goes.
In the cube design view under the Calculations tab I have several calculated members that I have created. Each of these are dollar amount fields for which I have defined a Format String property as follows:
Format String: "$#,##0;($#,##0);;N/A"
This format correctly handles my positive value, Negative value and my NULL value the way I want. However I need to deloy this cube on one of my UK severs and I don't want the $ but instead I would like the pound sign without having to recreate this entire project to do so. I have tried the following syntax but it is incorrect.
Format String "Currency;;;N/A"
Can someone help me with the correct way to code the property so that I can get my currecy symbol based on the regional setting as well as handle my NULL values.
Hi... I have 3 tables:SportTeams (TeamID, TeamName)SportAthletes (TeamID, AthleteID, AthleteName)SportMedals (AthleteID, Medal) I want to have a brief medal list (TeamID, G, S, B). I can write query in systax: Select a.TeamID, a.TeamName_en,g.G,s.S,b.B from SportTeams aLeft Join(Select c.TeamID,Count(*) as G from SportMedals b Inner Join SportAthletes c On b.AthleteID = c.AthleteIDWhere Medal = 'G'Group By c.TeamID) g On a.TeamID = g.TeamIDLeft Join(Select c.TeamID,Count(*) as S from SportMedals b Inner Join SportAthletes c On b.AthleteID = c.AthleteIDWhere Medal = 'S'Group By c.TeamID) s On a.TeamID = s.TeamIDLeft Join(Select c.TeamID,Count(*) as B from SportMedals d Inner Join SportAthletes c On d.AthleteID = c.AthleteIDWhere Medal = 'B'Group By c.TeamID) b On a.TeamID = b.TeamIDOrder By g.G desc, s.S desc, b.B desc, a.TeamID asc But I can't write it in LINQ syntax (I am beginner with C#, LINQ) Can you help this sample to LINQ systax? Thanks!
I am posting this information simply as an FYI. This is in reference to the MS-SQL command "CONVERT"
I spent over 2 hours :rolleyes: screwing around trying to find out different ways of formatting dates from MS-SQL into something that makes sense for what I needed. I googled everything I could think of and found multiple references that said the info is available on MSDN.. but I could not find it. What I did find were thousands of relatively useless references to "format codes" for converting dates, but with no references to what the different format codes would ultimately yield, or what format codes were available to use.
What I ended up doing was writing a small script to generate a list of all of the variations I could find.
Below is the script, and the output that it yielded. Now, before I get bombarded with "there is a better way" I know there probably is. But this is the way that I needed to do it this particular time. If there are technical errors in my explanation, anyone is welcome to correct them. But after 2 hours of messing with this for what should have been a super simple single .0009 second command, I am just irritated beyond belief that it had to be this complicated to find any useful information on the subject. That is why I am creating this. Hopefully it helps someone else.
The format for the MS SQL CONVERT command is :
CONVERT( length_of_output, date, format_code )
length_of_output : is exactly that . the number of characters that you want returned as your result. If you use a length of 6 you will only see the first 6 characters that are returned. I found the longest valid length to be 28 characters, but I went as high as 128 just for giggles and to see if it revealed any secrets.
date : is a valid date, I used directly the getdate() function
format_code : well.. that's the tricky part. See below.
What I did was ran a script that originally went from 1 to 20,000. It crashed at 15. Apparently the format codes are not totally sequential. So I put in an on error resume next.
What I found is that : 1) the codes are not uninterrupted sequential numbers. 2) the code output repeats every 255 3) negative numbers can be used, but its pointless. 4) useful valid codes are in the ranges of : 0-14, 20-25, 100-114, 120, 121, 126, 130 and 131 5) 0-25 typically represent "short dates" with the year being only 2 digits, but there are exceptions 6) 100 and above always returned a 4 digit year. the exception was 130 and 131, I don't know what it was trying to do.
Here is the script i ran
<% on error resume next
for iintCounter = 0 to 256 SQL = "SELECT CONVERT(CHAR(128), getdate(), " & iintCounter & " ) as TheDate" Set rsTheDateFormat = TheDatabase.Execute(SQL)
I have this stored procedure, and im trying to figured out how i can convert the date field into something i can actually sort in the right order in reporting services. Such as mm-dd-yy , whats the SQL code for doing that?
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?
The data file contains the answers given to questions asked during the course of an interview. This file contains data only for completed interviews. The file is ASCII based, and contains data in a card column format. This means that each record is spread over several rows in the file €“ called cards. Currently 39 cards make up one completed record, although this may change over time. There are 80 columns per row.
I have three differents sub-reports, each one inside a table object.
Each table has a property "page break-before" enable.
When I execute the view-report in HTML 4.0 format, the reports are executed with success, but when I export to PDF format, the property "page break-before" inserts a blank page at the end of each sub-report. How can I do to suppress the blank-pages when exporting to PDF format ??
I was given the following code for use in a Script Task, but it is written in C#: Â Â Â Â Â Â Â Â Â Â Â Â Company.File.ACH.NACHA file = new Company.File.ACH.NACHA(126, @"Data Source=SERVERSAND;Initial Catalog=Database;Persist Security Info=True;User ID=ID;password=pswd;"); Â Â Â Â Â Â Â Â Â Â Â file.Create(); Â Â Â Â Â Â Â Â Â Â Â FileInfo t = new FileInfo("C:/newfile.txt");Â Â Â Â Â Â Â Â Â Â Â StreamWriter Tex = t.CreateText(); Â Â Â Â Â Â Â Â Â Â Â //byte[] bytearraye = file.OutputArray; Â Â Â Â Â Â Â Â Â Â Â Tex.WriteLine(file.ToString());Â Â Â Â Â Â Â Â Â Â Â Tex.Close(); Â I've tried to convert this to vb.net for use in a Script Task, but I'm doing something wrong.
,convert(varchar,getdate(),101) as [CONFIRMATION_DATE!1!REPORT_DATE] The above displays as 8/26/2006, anyway you can convert that to a long format in the SP? I.E. August 26, 2006 Thanks.
Hi, How can I convert a text file (.txt) into SQL in ASP.net 2.0 ? The sample of the file format is like that ... 09/03/2007 08:41 "Fung, Kitty" Granted Access D1 Main 2354 111 09/03/2007 08:42 "Ng, Jaclyn" Granted Access D1 Main 21906 18 09/03/2007 08:42 "Leung, Agnes" Granted Access D1 Main 21920 18 Cheers
I want to convert a datetime type into and speciically formatted time:the table contains this:1899-12-30 10:00:00.000I want to reformat it to appear like this:10:00 AM I want to do this with SQL using a CONVERT of something along that line.I've been able to find all kinds of date and date/time formats, but not time alone in the above format.And suggestions?TIA</chaz>
I need to convert a SQL table or SQL table data to XML format. I tried using the Import Export Wizard in SQL 2005 (used SQLXMLOLEDB and SQLXMLOLEDB 4.0 as the source). However, it didn't work. Any way you know how I can convert and obtain data in XML format?
I have a customer time duration format like "12:15" that means 12 minutes and 15 seconds. I want to convert to be "12.25 " that means 12.25 minutes. Any suggestion please. Thanks a lot.
I am trying to write a simple query that retrieves the data field from a table (stored in the smalldatetime format) and converts the date to mmm yy format. The closest I can get is retrieving the date in the dd mmm yy format using the query below.
select convert(varchar(10),DATA_DATE,06) As DATA_DATE
If there an easy way to parse out the information I want? I also attempted to use the SUBSTR functions, but they always returned error messages.
How to convert SQL trace files into excel files without doing any work on SQL Profiler / SQL server using any scripting code. Consider that we only have SQL Trace files. What are the steps involved in converting into .CSV format using single "CLICK"
Precisely, here's what I need:When I run getdate(), I get, for example:August 9 2004 5:17 P.M.I want to turn the date portion into:8/9/2004 format and update one column with itI want to turn 5:17 P.M. into:hhmmss and update another column with it.I've been playing around with datepart, with substr, with you name it,and I'm stumped.Any code samples, other help most appreciated.Thanks,Google Jenny*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
Let me start by saying that I'm brand new to SQL Server 2005 and SSIS. I'm using the import wizard in SQL2005 to import from a flat file into a table and everything works fine except for dates. A typical date in my flat file is 01-JAN-06. 01 represents the day of the week, JAN represents the month and 06 represents the year. The flat file also contains date values of 00-XXX-00 which represent no date. For example a column containing last purchase date data would look like this:"DateOfLastOrder" "01-JAN-06" "02-JAN-06" "00-XXX-00" "03-DEC-05"The value of 00-XXX-00 means that there is no purchase date.I want to bring these columns into my table and replace the 00-XXX-00 values with a NULL.
The table Data Type is datetime.
If I use the import wizard using the example above I get this error message:
- Copying to [cpstest].[dbo].[date] (Error) Messages Error 0xc0202009: Data Flow Task: An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Invalid character value for cast specification". (SQL Server Import and Export Wizard)
Error 0xc020901c: Data Flow Task: There was an error with input column "DateOfLastOrder" (32) on input "Destination Input" (26). The column status returned was: "The value could not be converted because of a potential loss of data.". (SQL Server Import and Export Wizard)
Error 0xc0209029: Data Flow Task: The "input "Destination Input" (26)" failed because error code 0xC0209077 occurred, and the error row disposition on "input "Destination Input" (26)" specifies failure on error. An error occurred on the specified object of the specified component. (SQL Server Import and Export Wizard)
Error 0xc0047022: Data Flow Task: The ProcessInput method on component "Destination - date" (13) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. (SQL Server Import and Export Wizard)
Error 0xc0047021: Data Flow Task: Thread "WorkThread0" has exited with error code 0xC0209029. (SQL Server Import and Export Wizard)
If I remove the 00-XXX-00 values and import something like this:
The data is comming in from a CSV as a string. I am happy to get the day and year, its just converting the month (eg "AUG") into I a valid number.
I was thinking Lookup, but that only seems to work against a datasource. I can create a lookup table for it to work against but that seemed a bit clunky.