I have this piece of SQL code coming from Access:
Format([Jobnumber2],"00000") AS [Job Number]
What is the translation in SQL server language? I can't find the equivalent of the Format function!
Otherwise, is there a way to use ASP code to format the number in the way I'd like to?
Cheers,
C
Hi ... i'm sorry to hassle this user group but i have an urgent needfor some code - i have tried and tried to find a solutionn elsewhere -but the problem is i don't really know what i am looking for.What is the equivalent SLQ code for the below statement which works inMS Access?Format(7,"00")Result: 07or alternatively ...what i am actually trying to do is return the string yyyymm based onthe current date. eg 200506 (June 2006)Is there a simple way of doing this?The code i am currently using isCONVERT (char, DATEPART(yyyy, GETDATE())) + CONVERT (char, DATEPART(mm,GETDATE()))Result: 2005 !!!!At least i thought i would get 20056. But obv what i am aiming for is200506.Thanks in advance.TC
View 4 Replies View RelatedHI,I'm pretty new to MS SQL,My problem is setting ....=Format$(Date(),"mm" & "/1/" & "YYYY")(Which is from my MS ACCESS database)as my default value in MS SQL. How can I do this since format() doesnot seem to be a function that is in MS SQL. The above function worksgreat in MS ACCESS, but not in MS SQL.thanks
View 4 Replies View RelatedHello, i don't know how to format a string with an SQL select queryfor my VB6 App.I have a table like this :Code - Name1 - Jonathan2 - Mike....9 - Claudia10 - Robbie11 - SandyBut I would get code column result's with a particular format likethis :0001 - Jonathan0002 - Mike....0009 - Claudia0010 - Robbie0011 - SandyI use the Format(column, "#0000") function in my application ffor themoment but nothing to do with the DB Engine side ???I tried CONVERT function :SELECT CONVERT(varchar(4), code, '0000') FROM Employes;But the code result's stil 1,2,3 and not 0001,0002,0003 !!!Anyone has the solution ?ThanksJonathan
View 3 Replies View RelatedHi,
I am moving the database built in access to Sql 7 and i am unable to find any subsitute of format function of Access in sql. Please help me out ot find a suitable solution of it.
Thanks
Hi,
with the bcp (bulk copy program) I output some data to a file. The data consists of two columns which are datetime values and one column with the difference in time. For the difference calculation I use the DATEDIFF function. This function can only give the diff back in minutes or hours and not in a time format.
Can I use a function to format the result or do I have to do it manually?
I managed to do the calculation manually by using the % (mod) operator, but then I have the time in this format 0:0 where it should be in 00:00 or 00:00:00. In that case I need a function or a way to add preceding zero's to the values.
Note that the formatting or manual calculation should be done in the SELECT clause to be able to use the bcp tool.
Thanks in advance,
Maarten de Jong
Website developer
hi,
the query ----> select format(Total,"0.00") from mytable works very well in MS Access 2000/97 but the same query dosen't work in MS SQL Server 2000.
i need the substitute for this query. please help me it's very urgent
thanks a lot for consideration
Vikas
Hi, I came upon a strange behavior of Format function in report, which I'm unable to explain.
I have some double value, which I want to format. E.g. the value is in db field "Users" and is 303870
1)
If I set a Value property for the field to
=Fields!Users.Value or CDec(=Fields!Users.Value)
//the value is already double so the CDec has no effect
and Format property for the field to
=Format(me.Value,"#,#") or =Format(Fields!Users.Value,"#,#")
than the number which is visible in the report is 3303,873870 ???
2)
If I put in the Value property
=Format(Fields!Users.Value,"#,#")
and the Format property is empty
than the output is ok 303,870, however this is not desired, because the value is than handled as string
3)
If I put in the Value property
=Fields!Users.Value
and into Format property
=Format(CStr(me.Value),"#,#")
than the output is ok again.
As I understand, the Format should be same as in VB (I am actually C# programmer, not very familiar with VB) so I tried to use Format in VB on double value, and the output was as expected (e.g. 303,870), but when I used to a string I got only the style (e.g. #,#).
So I wonder, the ReportingServices Format function works correctly only with string input? But why than works the example 2)? Or do I have somewhere a mistake?
Thanks for the advice.
How to get this out put.
Details:
declare @deadline Datetime = '2014-03-23 15:30:10.000'
SELECT CONVERT(VARCHAR(30),@deadline, 100) AS DateConvert
----With this I am able to produce that like
----o/p: Mar 23 2014 3:30PM
declare @deadline1 Datetime = '2014-03-03 15:30:10.000'
SELECT CONVERT(VARCHAR(24),@deadline1, 100) AS DateConvert
--o/p: Mar 3 2014 3:30PM
--expected O/p: Mar 03 2014 03:30PM
What is the correct date format to achieve this.
I have the below Ms.Access code that I would to transition into SQL.
Is CCUR a usable function in SQL, or would I have to use the convert to money function?
Charges: Sum(((CCUR([Fee Schedule Rate])*CCUR([Units_Charged]))))
Hi guys n gals !
I am having a few problems manipulating the results of my data reader,To gather the data I need my code is:
// database connection SqlConnection dbcon = new SqlConnection(ConfigurationManager.AppSettings["dbcon"]);
// sql statement to select latest news item and get the posters name SqlCommand rs = new SqlCommand("select * from tblnews as news left join tblmembers as members ON news.news_posted_by = members.member_idno order by news.news_idno desc", dbcon); // open connection dbcon.Open();
// execute SqlDataReader dr = rs.ExecuteReader();
// send the data to the repeater repeater_LatestNews.DataSource = dr; repeater_LatestNews.DataBind();
Then I am using: <%#DataBinder.Eval(Container.DataItem, "news_comments")%> in my repeater.What I need to do is pass the "news_comments" item to a function I created which will then write the result. The code for my function is:
// prevent html public string StripHtml(string data) { // grab the data string theData = data;
// replace < with &alt; theData = Regex.Replace(theData, "<", "<"); // return result return theData; }
But I am having problms in doing this,Can anyone point me in the right direction on what I should be doing ???
I have to figure out the items that Legal Name implies individual but Legal Entity Structure indicates a incorporation type. In this sample, you can see Alexander, Justin N. is my target. But my problem is how should I use a query to figure out which one is a individual's name? How should I write a function to check the name format (Last, First Middle)?
Legal Name ////////////////////////////////////// Legal_Entity_Struct
S & H Farm Supply, Ltd.////////////////////////////Company
F.M.Abbott Power Equipment,Co.///////////////Company
Ray's Dixie Chopper, Inc.////////////////////////// Company
Alexander, Justin N. ///////////////////////////////// Company
Alameda Power Equipment, Inc.//////////////// Company
[Code] .....
When I use for instance:
SELECT
FORMAT(BegDate,"dd.mm.yyyy")
FROM TABLE1
result is - |BegDate|22.12.2013.......................|
Is there any way to limit length of string returned from FORMAT function .
Database is on ACCESS.
I'm querying a database table that creates a time stamp in seconds only. I have a starting time of
1099725928 = 11/6/2004 12:25:28 AM. So that if another entry is made 1 second later the time stamp value entered into the table is 1099725929. The front end application does the converstion from the seconds counter to the datetime format. The query I am writing calls information from this table for a different application that does not have the conversion capability. If I know the starting point (I don't want to create a conversion table) which is 1099725928 = 11/6/2004 12:25:28 AM, is it possible to write into my query script a function to convert the seconds value to the correct datetime format? It would need to accurately account for leapyear and even/odd months.
************************************************************************************************
Select Case_ID_, Region, Assigned_To_Group_, Assigned_To_Individual_,
Status, Priority, Category, Type, Item, Affiliate, Hours_to_resolve, Resolved_Time, Assign_Time, Create_Time
From HPD_HelpDesk
Where Region = 'Central Valley'
and Assigned_To_Group_ = 'CVSA Desktop Support'
and Status In (1, 2, 3, 4, 5)
and Priority In (0, 1, 2, 3)
Order by Case_ID_ Desc************************************************************************************************
Hi All,I am facing a problem with a sql what i used in MS Access but its notreturning the same result in MS Sql Server 2000. Here i am giving thesql:SELECT TOP 3 format( MY_DATE, "dddd mm, yyyy" ) FROM MY_TAB WHEREMY_ID=1The above sql in ACCESS return me the date in below format in onecolumn:Friday 09, 2003But in Sql server 2000 i am not getting the same format eventhough iam using convert function, date part function etc.Please if you find the solution would be helpful for me..ThanksHoque
View 3 Replies View Related
Hello,
I am trying to get this to work - but it only returns minutes & seconds:
Function Seconds2mmss(ByVal seconds As Integer) As String
Dim ss As Integer = seconds Mod 60
Dim mm As Integer = (seconds - ss) / 60
Seconds2mmss = String.Format("{0:0}:{1:00}", mm, ss)
End Function
Can anyone help me out? I am not that familiar with VB.
Thanks,
Deb
Hi. Im new to SQL and I need to export a SQL table as a comma delimited text file which is straight forward. However two of the fields are integers and I need these to be right justified with zero's.
In Access I would use something like format(columnname, "00000000") to get it to work, but SQL Server doesn't like this.
How can I do this?
Hi all,
Currently, I am using the SSRS 2005 and try to toggle the report columns similar to Excel's column grouping function. Users can expand "+" / collapse "-" the columns in the render excel report. It works fine when I excute the tabular report in the web yet the toggle function is missing when I render the report to an Excel report. I am not sure the toggle function will only work for the row items or both as the excel report can keep the toggle function for the row items. Please help.
I'm trying to get a calculation based on count(*) to format as a decimal value or percentage.
I keep getting 0s for the solution_rejected_percent column. How can I format this like 0.50 (for 50%)?
select mi.id, count(*) as cnt,
count(*) + 1 as cntplusone,
cast(count(*) / (count(*) + 1) as numeric(10,2)) as solution_rejected_percent
from metric_instance mi
INNER JOIN incident i
on i.number = mi.id
WHERE mi.definition = 'Solution Rejected'
AND i.state = 'Closed'
group by mi.id
id cnt cntplusone solution_rejected_percent
-------------------------------------------------- ----------- ----------- ---------------------------------------
INC011256 1 2 0.00
INC011290 1 2 0.00
INC011291 1 2 0.00
INC011522 1 2 0.00
INC011799 2 3 0.00
I want to change decimal precision dynamically without rounding value
For example
10.56788 value for 2 decimal precision is 10.56.
10.56788 value for 3 decimal precision is 10.567.
---CASE 1 without dynamic parameter---------
DECLARE @DECIMALVALUE AS tinyint
SELECT CONVERT(DECIMAL(10,2),500000.565356) As Amount
[Code] ....
I am getting error as follows......
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '@DECIMALVALUE'
This decimal precision format value will vary company to company ...
I have created SSRS report which has many overlapping objects, the output in PDF format seems to good but in word format it is not giving the required output.
View 5 Replies View RelatedWe are migrating data from old DB2 systems to sql server 2012, the DATE FORMAT in those systems is in decimal format with 7 digits. CYYMMDD format.
I need to convert this into DD/MM/YYYY format.
I have date coming to one page as a string in the following format"May 4 2005 12:00AM"
I need to query one of my tables using this date in combination of other nondate values. How can I convert this date into valid sql server datetime format before I query a database tables
Please help
I have a table which stores date-of-birth in varchar 19861231(yyyymmdd). A view takes this data. I want to store this date as mmddyyyy in the view. How can we achieve this?
View 18 Replies View RelatedHi,
I have a set of csv files and a set of Format Specification files for each of the csv files. I need to convert the csv files into another format of csv files as specified in the Format Specification files. All the columns of the input csv files do not have a mapping with the columns of the output csv files. How can I achieve this using SSIS ? This is an urgent requirement. Please reply asap. Thanks.
Hello Expert!
I need help to I translate this data...
Table "LeaveDetail"
StaffNo | StartDate | EndDate | LeaveType |
1 | 23/04/2006 | 26/04/2006 | AL |
2 | 24/04/2006 | 25/04/2006 | MC |
3 | 26/04/2006 | 27/04/2006 | EL |
1 | 30/04/2006 | 02/05/2006 | EL |
Into this format...
|Apr|Apr|Apr|Apr|Apr|Apr|Apr|Apr|May|May|May|May|
StaffNo |23 |24 |25 |26 |27 |28 |29 |30 |01 |02 |03 |04..
---------------------------------------------------------
1 |AL |AL |AL |AL | | ... |EL |EL |EL |
2 | |MC |MC | | |
3 | | | |EL |EL |
Parameter:
Date From e.g. 23/04/2006 to 23/05/2006
Using only query statement...
Is this possible??
TIA
Regards.
Hi All,
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.
Regards,
Kuldeep Chauhan
dear all can anybody help me soon....
i am using visual studio 2005 webapplication based on sql server 2005 database.
i can get one date from sql using one query.
I am selecting my field based on following code CONVERT(varchar, Oman.Positions.Datum, 9) AS LastUpdate
this case my output is May 4 2008 3:19:45:000AM.....
this output is correct but from this output i want to avoid millisecond part.
ie i want the output like May 4 2008 3:19:45 AM....
how i can do this
regards
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.
View 1 Replies View RelatedI have this function in access I need to be able to use in ms sql. Having problems trying to get it to work. The function gets rid of the leading zeros if the field being past dosn't have any non number characters.For example:TrimZero("000000001023") > "1023"TrimZero("E1025") > "E1025"TrimZero("000000021021") > "21021"TrimZero("R5545") > "R5545"Here is the function that works in access:Public Function TrimZero(strField As Variant) As String Dim strReturn As String If IsNull(strField) = True Then strReturn = "" Else strReturn = strField Do While Left(strReturn, 1) = "0" strReturn = Mid(strReturn, 2) Loop End If TrimZero = strReturnEnd Function
View 3 Replies View RelatedHi all,
I executed the following sql script successfuuly:
shcInLineTableFN.sql:
USE pubs
GO
CREATE FUNCTION dbo.AuthorsForState(@cState char(2))
RETURNS TABLE
AS
RETURN (SELECT * FROM Authors WHERE state = @cState)
GO
And the "dbo.AuthorsForState" is in the Table-valued Functions, Programmabilty, pubs Database.
I tried to get the result out of the "dbo.AuthorsForState" by executing the following sql script:
shcInlineTableFNresult.sql:
USE pubs
GO
SELECT * FROM shcInLineTableFN
GO
I got the following error message:
Msg 208, Level 16, State 1, Line 1
Invalid object name 'shcInLineTableFN'.
Please help and advise me how to fix the syntax
"SELECT * FROM shcInLineTableFN"
and get the right table shown in the output.
Thanks in advance,
Scott Chang
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
I need to know how can i incoporate the functionality of DECODE function like the one in ORACLE in mSSQL..
please if anyone can help me out...
ali