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
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, 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.
Hello folks,
I've got a table like this
PRODUCT_ID LABEL_ID
----------- -----------
6 1
6 2
7 1
1 1
2 1
I need to have a select statement to take all the products with the LABEL_ID containing 1 AND 2. I can't use the IN operator as it will return me results with LABEL_ID 1 OR 2. Is there any solution to this? Sorry I'm relatively new to mysql and not familiar with the select statements.
I'm currently working on something like a search engine. Hope to have some good advice here :)
I'd like to change query:SELECT DM.*, 'condition1', NULL FROM DMWHERE (condition1)UNIONSELECT DM.*, NULL, 'condition2' FROM DMWHERE (condition2)to one SELECT like thisSELECT DM.*, WasCondition1, WasCondition2 FROM DMWHERE (condition1) or (condition2)but how to fill in the WasConditionX column?The UNION version was bad because in case a row fulfilled 2conditions, it was repeated instead of joining them, such like this:SELECT DM.*, 'condition1', 'condition2' FROM DMWHERE condition1 AND condition2UNIONSELECT DM.*, 'condition1', NULL FROM DMWHERE condition1 AND NOT condition2UNIONSELECT DM.*, NULL, 'condition2' FROM DMWHERE NOT condition1 AND condition2or (say DM has columns A, B, C):SELECT A, B, C, SUM(cond1), SUM(cond2) FROM(SELECT DM.*, 1 AS cond1, 0 AS cond2 FROM DMWHERE condition1UNIONSELECT DM.*, 0 AS cond1, 1 AS cond2 FROM DMWHERE condition2) AS DMGROUP BY A, B, C
View 12 Replies View Related
Hi,
I'm using sql server 2005, i want to know is there any substitute for BCP in sql server 2005,
like how we have DTS in 2000 and SSIS in 2005,
I heared that BCP will be deprecated in the next version of Sql server.
so i dont want to implement BCP in 2005 version,
Will someone please tell me whether i have to implement BCP in sql server or we have some other option,
i dont want to use SSIS instead of BCP because latter is faster.
Thanks
Altaf Hussain Nizamuddin
Do anyone know how I can replace or Substitute the current value of a form textbox with another value.For example, the string "YES" appears on the textbox but hit the SAVE button I would like "TRUE" to be entered in the SQL Server database table. Thanks.
View 6 Replies View Related
IS there ny substitute for this ?
=First(Fields!TouchedBy.Value, "Dataset1")
instead of keyword 'First' i need the current record , but also i need to refer dataset name also , is there any way to do that ?
Any idea?
thanks
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
I have only been coding in .Net for about six months, and am not sure if this is a C# problem or an SQL one. I use the Data Access Application Block in my program.
I have two optional fields on my form (RangeFrom and RangeTo). If the user chooses not to enter data into these textboxes(textbox = ""), an entry on the db is created with null values. It works.
But sometimes the user wants to enter 0 as either an upper or lower end of a range. This is where my problem comes in. My program saves 0 as null too.
In my program I do a test on the textboxes and populate two float values in a business object (objQuestion) accordingly, like this:
if (txtrangefrom.Text != "") {
objQuestion.RangeFrom=float.Parse(txtrangefrom.Text);
objQuestion.RangeTo=float.Parse(txtrangeto.Text);
}
else {
objQuestion.RangeFrom=Convert.ToSingle(null);
objQuestion.RangeTo=Convert.ToSingle(null);
}
And this is what my Business object look like. It sets up the parameters and calls the Data Access Application Block to create an entry in my table:
// fieldslist
float cvintRangeFrom;
float cvintRangeTo;
//properties
public float RangeFrom {
get {
return cvintRangeFrom;
}
set {
cvintRangeFrom = value;
}
}
public float RangeTo {
get {
return cvintRangeTo;
}
set {
cvintRangeTo = value;
}
}
// some code deleted for readability....
public int AddOption() {
string cvstrSpName = "addOption";
SqlParameter [] cvstrStoredParams = SqlHelperParameterCache.GetSpParameterSet(gcstrConnectionString, cvstrSpName, true);
//lines deleted for readability...
//check if the optional fields have a value associated with them. if not, assign dbnull.value.
cvstrStoredParams[4].Value=(cvintRangeFrom != Convert.ToSingle(null) ? cvintRangeFrom : (object)DBNull.Value);
cvstrStoredParams[5].Value=(cvintRangeTo != Convert.ToSingle(null) ? cvintRangeTo : (object)DBNull.Value);
//lines deleted for readability...
SqlHelper.ExecuteNonQuery(gcstrConnectionString, CommandType.StoredProcedure, cvstrSpName, cvstrStoredParams);
return(cvintOptionID = Convert.ToInt32(cvstrStoredParams[0].Value));
}
I use Convert.ToSingle when working with nulls (or possible nulls) because I get an error when I use float.parse for this.
The thing is, after this method AddOption has been executed, I test the value if the business object's rangefrom (that is where I entered 0) and display it on my form. I still shows a 0, but on my database table it is null!
objQuestion.AddOption();
//txtrangefrom.Text=""; on the next line I test the value in the business object...
txtrangefrom.Text=objQuestion.RangeFrom.ToString(); // and this displays 0!!!
//txtrangeto.Text="";
txtrangeto.Text=objQuestion.RangeTo.ToString();
So to me it seems the problem seems to be either the DAAB or on the SQL side, but hopefully somebody can prove me wrong! I was thinking that it could also be float.parse/Convert.ToSingle methods and have done various tests, but I am none the wiser...
Any help or ideas will be greatly appreciated...
Hello once again,
My last question concerns (temporary) tables. In a web form (VWD Express, Asp,Net 2.0, VB, .mdf db, Ajax) I have two gridviews. One gets its data from textboxes and dropdownlists as the user clicks an ok button. This data is then sent to a "junk" table in the db. The other gridview retrieves this data "automatically" and displays the data, filtered and grouped by some SQL statements. When the user finally clicks an Insert record button, the data from this second gridview gets inserted in another table in the db and the data from the first "junk" table is disposed.
I guess it shouldn't be necessary to insert, retrieve, and delete the data in and from the "junk" table. Even though it does work like a charm, I guess it would be better if I could do it wit hsome kind of "temporary table", array or the like so that the db wouldn't need to be updated to this extent. Still, I really like the simplicity of using gridviews and the rest of the page's code is complex enough, so I don't really want to substitute this behaviour for tons of hand-written, hard-to-follow, and hard-to-integrate code.
What are my options? Any help would be greatly appreciated.
Pettrer
I only want to select few records from table
Keyword "Top" can't be used in SQL Mobile, is there any substitute?
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,
I need to store large note in SQL Server field, what is substitute of Access's Memo filed in SQL Server?
Appreciated...
Objective
Do a manual installation of SQL Server 2005 (which could be an upgrade from SQL Server 2000) and record the steps. Replay the steps for an unattended installation of SQL Server 2005.
Problem
There isn't a setup.ini file for SQL Server 2005. There is a template.ini for unattended installations of SQL Server 2005, but it doesn't obviously map to selections on manual installation dialog boxes.
Desired workaround
Capture the steps of a manual installation and use them to populate template.ini.
Relevant background for a potential solution
A manual installation creates log files. So does an unattended installation. But the unattended installation writes all of the log files into a single zip file.
What is important to know is that an unattended installation creates an additional file, a *datasource.xml file. This XML file is found in the zip file with all of the other log files. If you open this XML file, you'll see that the attribute names correspond to keywords in template.ini.
Potential solution
If a manual installation could be forced to generate a *datasource.xml file, then it should be possible to map the settings in the file to keywords in template.ini.
So, how can a *datasource.xml file be created? In post http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=359678&SiteID=1, Jeffrey Baker says "add LOGNAME=<path to cab> and run setup again". Unfortunately, these instructions are too vague. Where is this added? To the command line, to a setup.ini (if so, in which directory, and in which section should he addition be made?). I did lots of searches and couldn't find the answers.
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 have a table with a datetime field (named "lnDateDone") and an integer field (named "PlannedHour"). I want to build a new datetime field with the same value as lnDateDone and replacing the hour part by the PlannedHour field. Example:
lnDateDone = '2014-04-09 13:22:31.544'
HourPlanned = 14
new datetime field = '2014-04-09 14:00:00'
I tried the following:
CAST(SUBSTRING(CAST(lnDateDone AS VARCHAR(25)),1,12) + CAST(PlannedHour AS VARCHAR(2)) + SUBSTRING(CAST(lnDateDone AS VARCHAR(25)), 15, LEN(lnDateHeureFin)) AS SMALLDATETIME) AS DatePrevue
but it won't work when casted in DateTime format, only in varchar, but I want it in datetime as I need to do compute some values using date manipulation functions.
How can I do that?
Is it possible to change any line breaks or newlines for a string ...
In a select statement in a store procedure, in this way I can reproduce text in a label on a webpage complete with line breaks ....
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 ...
Hey. I need to substitute a value from a table if the input var is null. This is fine if the value coming from table is not null. But, it the table value is also null, it doesn't work. The problem I'm getting is in the isnull line which is in Dark green color because @inFileVersion is set to null explicitly and when the isnull function evaluates, value returned from DR.FileVersion is also null which is correct. I want the null=null to return true which is why i set ansi_nulls off. But it doesn't return anything. And the select statement should return something but in my case it returns null. If I comment the isnull statements in the where clause, everything works fine. Please tell me what am I doing wrong. Is it possible to do this without setting the ansi_nulls to off??? Thank you
set ansi_nulls off
go
declare
@inFileName VARCHAR (100),
@inFileSize INT,
@Id int,
@inlanguageid INT,
@inFileVersion VARCHAR (100),
@ExeState int
set @inFileName = 'A0006337.EXE'
set @inFileSize = 28796
set @Id= 1
set @inlanguageid =null
set @inFileVersion =NULL
set @ExeState =0
select Dr.StateID from table1 dR
where
DR.[FileName] = @inFileName
AND DR.FileSize =@inFileSize
AND DR.FileVersion = isnull(@inFileVersion,DR.FileVersion)
AND DR.languageid = isnull(@inlanguageid,null)
AND DR.[ID]= @ID
)
go
set ansi_nulls on