Way To Format Query

Oct 22, 2013

I have to fetch some rows within a Crystal report. I need to select

from Orderhist a where
order# a = order# cr
and trncde is in 'pwb' 'pdb' 'pbb'

but i need then that trncde as a report field. also the order# cr is a formula field because its a subscript embedded 8 char within a 24 char. this is already available to the cr but can u do the where = on a formula field?

View 1 Replies


ADVERTISEMENT

How Can I Use Format As HH:MM:SS From Seconds Within The SQL Query

Nov 8, 2007

I have the following SQL query where i want thease to be populate to GridView, but the Duration field is in Second format, I want it would be in HH:MM:SS format.
cmd = "select subscriber_id as Subscriber_no,,amount,duration from MyTable" ;
Please help me how to format this within the Query to display in GridView.

View 3 Replies View Related

Query Criteria Format

Feb 9, 2004

I have a text box that is used to submit stock symbols that are to be saved in a sql table. The symbols are to be separated by a space or a comma (I don't know which, yet). I want to retrieve the symbols later to be used in a query, but I don't know how to get the symbols in the proper string format for the query, eg

The symbols are stored in the tables as: A B C D
The query string criteria would look like: IN('A', 'B', 'C', 'D')

The IN('A', 'B', 'C', 'D') citeria would be the values in the @Symbol variable in this SPROC

SELECT a_Name_Symbol.Symbol, a_Financials.Revenue
FROM a_Financials INNER JOIN
a_Name_Symbol ON a_Financials.Symbol = a_Name_Symbol.Symbol
WHERE (a_Name_Symbol.Symbol @Symbol)
ORDER BY a_Name_Symbol.Symbol

Is there a slick (ie easy) way to change the contents entered in the text box (A B C D) into IN('A', 'B', 'C', 'D') ?

Thanks,

Paul

View 1 Replies View Related

Query Regarding Date Format

Jul 20, 2005

select last_name,hire_date,to_char(hire_date,'DAY') DAYfrom employeesorder by to_char(hire_date-1,'d')i wanted to know how the function to_char(hire_date-1,'d') works...its basically a query used to find the day on which a employee washired ,also it requires that the query be sorted by the day of theweek on which the employee was hired.it'll be helpful is someone replies to this

View 2 Replies View Related

Query Format Date

Sep 10, 2007


Hi
I wrote query to get date from database SQL 2000 server. Sometime I get different date format not MMDDYYYY but number as Julian calendar?? Do you know what wrong with this? How can I wrote SQL so that all date will be MMDDYYYY?
Thanks
Daniel

View 1 Replies View Related

View The SQL Query Out-put In Horizontal Format.

Jan 10, 2005

I have the following SQL query that I like to view the out put in horizontal format:

Select ID, First_name, Last_name from ABC

Instead of getting out-put like

Id First_name Last_name
1Jim Smith
2Tom Jones

I like to see the out-put like:

Id1 2
First_nameJimSmith
Last_nameTomJones

Please advice.

Thanks in advance,


Abrahim

View 1 Replies View Related

Convert Smalldatetime To Mmm Yy Format In Query

Jan 7, 2004

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.

View 3 Replies View Related

SQL Vs Access Date Query Format

Mar 7, 2004

I am new to SQL server, and am learning as I move an Access db to MSDE2000A. With Access db I run several different queries from a VB6 application in the basic format:

SELECT testdb.*
FROM testdb
WHERE testdb.datefield = #1/01/2004#

When working with the MDSE version of the db, problem is with the "#" delimiter of dates. MSDE is giving a bad query error. If I change the format to: datefield = '1/01/2004' , the query works on MSDE

However, using SQL builder in VB Design Environment I can run the query in either format and get a result.

Any suggests what I am missing? Thanks.

View 2 Replies View Related

Sql Query To Get A Desired Format Of Display

May 23, 2008

Hi Guys,

Below is example of the current structure of table1 I have at run time:

------------------------
GroupName Resourcename Week1 Week2 ..cont.. dynamically

Associates A1 0 80 ......
Assocaites A2 20 40 ......
Associates A3 50 100 ......
Principal P1 20 100 ......
Principal P2 0 0 ......
Principal P3 0 100 ......
------------------------
I want to change the above to something like below table2:
---------------------------
GroupName Status Week1 Week2 ....cont

Associates Assigned 2 3
Associates NotAssigned 1 0
Principal Assigned 1 1
Principal NotAssigned 2 1
---------------------------

I will try to explain how I am deriving table2 from table1. I have to count the number of Resource name against each Groupname for a particular week column(Weeki i 1 to n dynamic) where value of Week column is 0, then use this numbber against NotAssigned and the complementary number to be stored as Assigned.

The table formaating is lost in HTML view but just consider any gaps between fields as next column value.

Am I clear in what i am asking , if not please ask me.

Any help will be highly appreciated.

View 2 Replies View Related

Changing The Format For A SQL Server Query

Apr 3, 2006

Hi,

I have to generate a daily report of survey answers by users? My question is there a way to reformat the query so it generates a table or report with it showing the rows as columns instead.

Here is my initial query.

SELECT

dbo.Reporting_SurveyAnswers.DateCreated AS DateCreated

,dbo.Reporting_SurveyAnswers.questionid AS QuestionID

,dbo.Reporting_SurveyAnswers.surveyid AS SurveyID

,dbo.Reporting_SurveyQuestions.ordernumber AS OrderNumber

,dbo.Reporting_SurveyAnswers.userid AS UserID

,dbo.Reporting_User.LastName1 AS LastName

,dbo.Reporting_User.FirstName AS FirstName

,dbo.Reporting_SurveyQuestions.QuestionText AS QuestionText

,dbo.Reporting_SurveyAnswers.QuestionAnswer AS QuestionAnswer


FROM

dbo.Reporting_Surveys

INNER JOIN dbo.Reporting_SurveyQuestions

ON dbo.Reporting_Surveys.surveyid = dbo.Reporting_SurveyQuestions.surveyid

INNER JOIN dbo.Reporting_SurveyAnswers

ON dbo.Reporting_SurveyQuestions.QuestionID = dbo.Reporting_SurveyAnswers.QuestionID

INNER JOIN dbo.uvwReporting_User

ON dbo.Reporting_SurveyAnswers.userid = dbo.uvReporting_User.userid

WHERE

dbo.uvReporting_SurveyAnswers.surveyid = 1125

Order by dbo.Reporting_SurveyAnswers.DateCreated

,dbo.Reporting_SurveyQuestions.ordernumber

Select

dbo.Reporting_SurveyQuestions.ordernumber AS OrderNumber

, dbo dbo.Reporting_SurveyQuestions.QuestionText AS QuestionText

To complicate matters, some of the users did not answer some of the questions and some of the questions are duplicated in the rows because the database assigned them one answer each.

Example. Question 18 says "Name all the industries you have worked in. Check all that apply.

What happens is lets say the user checks 4 different boxes. In the query results, it will show 4 rows with question 18 with each answer they checked off.

Any help would be appreciated.

Thanks

The Accidental Tourist

View 3 Replies View Related

Data Format Consistency Or Query?

Oct 2, 2006

Hi

I have a table that stores the period and year as two seperate fields the problem that i have is that when the data is entered in the data base the period is sometime entered as 6 or 06, hence i have a table thaat looks like as follows:


Period Year
====== ====
6 2006
06 2005
12 2006
3 2005
2 2005
4 2005
04 2005
03 2005



when i currently query the information using the period i use the LIKE command in SQL which gives me the results but the problem is when the like is done with 2 as the period value it also retrieves the 12, 02, and 2, in that case the period 12 was not that was requested.

I was wondering is there a way to reformat the data so that all the data is in a consistent format?

Or is there a better way of quering the information in the current format?

View 13 Replies View Related

SQL Server Date Format Query

Aug 16, 2007

Dear All,

I'm having a query problem regarding to the date format. From a table, there's a record of patients' birthdate.

In order to identify their age, how should i perform the query?

Select * from patient where BirthDate.Year < 1950

I've tried the above query where i want to extract patients' records who born before 1950, however, it generates error. Can somebody help?

M i K e

View 7 Replies View Related

Query Analyzer Vs Isqlw Different Format

Jul 23, 2005

HelloI have a SQL 2000 server, the server setting is default language :French and all Collation names in FrenchIf i launch the 'Query Analyzer' from the SQL Entreprise Manager on mydatabase, and run a T-SQL my dates are in English.If i do a : DBCC USEROPTIONS, my results arelanguageus_englishdateformatmdyNow if i launch 'Query Analyzer' from the startup menu, my date formatis ok in French, if i do DBCC USEROPTIONS, results are:languageFrançaisdateformatdmyIn the Query Analyzer i checked the option in Tools>Options>Connections(Use regional settings is unchecked) and they are the same in both case.How can i resolve my problem ?Thanks

View 1 Replies View Related

How Can I Format The Date In The SQL Table Using A SQL Query

Aug 23, 2006

Hi

I have a SQL table that contains date in this format :-


2006-07-02 16:20:01.000
2006-07-02 16:21:00.000
2006-07-02 16:21:01.000
2006-07-02 16:22:00.000
2006-07-02 16:22:02.000
2006-07-02 16:23:00.000
 

The date above contains seconds that I dont want, how can I remove those seconds so that the output looks like :-



2006-07-02 16:20:00.000
2006-07-02 16:21:00.000
2006-07-02 16:21:00.000
2006-07-02 16:22:00.000
2006-07-02 16:22:00.000
2006-07-02 16:23:00.000
 

Your help will be highly appreciated.

View 9 Replies View Related

Changing The Number Format In SQL Query

Jan 30, 2008

Hi


I have query

SELECT col1, a.Inv_Amount as Amount
FROM SPS_Oustandings a

I can get the result values for example as 12345.67 as result, but i need it in german format with , used as decimal point and . as thousand seperator

i need output as 12.345,67
My DataGrid (ASP.NET) is directly bound to SP which have this query, i want to show amount in above format in grid.

Please Help,
Thanks

View 6 Replies View Related

Preserve SQL Format In Query Editor?

Dec 11, 2007

Not exactly a TSQL question. Query Editor in Management Studio keeps reformatting my sql code every time I hit save, making the code much harder to read. Is there a way to preserve line breaks and indentation in the SQL when creating a view? Thanks.

View 1 Replies View Related

Format Of DateTime Column In SELECT Query

Dec 19, 2006

I am using SQL2005 and ASP.NET 2.0
I have one column in database called DateTime and it is defined like type datetime.It is formated like: day.month.year hour:minutes:seconds
My question is: I want to get date from Column DateTime in format day.month.year in SELECT query, not in stored procedure.
 thanks

View 3 Replies View Related

Need Help In Query - To Get Data From 2 Tables In A Particular Format To Display

Jul 14, 2007

Dear FriendsI need to get the data in the required format. Please help----------------------Table TripSheet----------------------Id,TSNo.,JourneyDate(ddmmyy),CustName,RateType,VehicleNo., BillId, journey hours, journey kmDATA
1, 234, 1-1-2007, Samson Pvt.Ltd, BPORates, Vehi45, 234, 2 hrs, 60 KM2, 235, 2-1-2007, Samson Pvt.Ltd, BPORates, Vehi45, 234, 3 hrs, 30 KM3, 236, 3-1-2007, Samson Pvt.Ltd, BPORates, Vehi45, 234, 2 hrs, 60 KM4, 237, 4-1-2007, Samson Pvt.Ltd, BPORates, Vehi45, 234, 4 hrs, 40 KM5, 238, 5-1-2007, Samson Pvt.Ltd, BPORates, Vehi45, 234, 2 hrs, 60 KM
6, 239, 1-1-2007, Samson Pvt.Ltd, BPORates, Vehi99, 234, 2 hrs, 60 KM7, 240, 2-1-2007, Samson Pvt.Ltd, BPORates, Vehi99, 234, 3 hrs, 30 KM8, 241, 3-1-2007, Samson Pvt.Ltd, BPORates, Vehi99, 234, 2 hrs, 60 KM9, 242, 4-1-2007, Samson Pvt.Ltd, BPORates, Vehi99, 234, 4 hrs, 40 KM10, 243,5-1-2007, Samson Pvt.Ltd, BPORates, Vehi99, 234, 2 hrs, 60 KM----------------------------Table BillTripSheet-----------------------------Id,BillId,VehicleNo., TotalJourneyHours,TotalJourneyKM,SlabApplied, MinAmt, ExtAmtDATA
1, 234, Vehi45, 13hrs, 250km, SlabA, 500, 502, 234, Vehi99,  13hrs, 250km, SlabA, 500, 50
I need to display data to be displayed in the following format in report(grouping based on vehicle no.)
1, 234, 1-1-2007, Samson Pvt.Ltd, BPORates, Vehi45, 234, 2 hrs, 60 KM2, 235, 2-1-2007, Samson Pvt.Ltd, BPORates, Vehi45, 234, 3 hrs, 30 KM3, 236, 3-1-2007, Samson Pvt.Ltd, BPORates, Vehi45, 234, 2 hrs, 60 KM4, 237, 4-1-2007, Samson Pvt.Ltd, BPORates, Vehi45, 234, 4 hrs, 40 KM5, 238, 5-1-2007, Samson Pvt.Ltd, BPORates, Vehi45, 234, 2 hrs, 60 KM----------------------------------------------------------------------------------------------------------1, 234, Vehi45, 13hrs, 250km, SlabA, 500, 50---------------------------------------------------------------------------------------------------------
6, 239, 1-1-2007, Samson Pvt.Ltd, BPORates, Vehi99, 234, 2 hrs, 60 KM7, 240, 2-1-2007, Samson Pvt.Ltd, BPORates, Vehi99, 234, 3 hrs, 30 KM8, 241, 3-1-2007, Samson Pvt.Ltd, BPORates, Vehi99, 234, 2 hrs, 60 KM9, 242, 4-1-2007, Samson Pvt.Ltd, BPORates, Vehi99, 234, 4 hrs, 40 KM10, 243,5-1-2007, Samson Pvt.Ltd, BPORates, Vehi99, 234, 2 hrs, 60 KM----------------------------------------------------------------------------------------------------------2, 234, Vehi99,  13hrs, 250km, SlabA, 500, 50----------------------------------------------------------------------------------------------------------
The Calculation logic is lengthly and the number of records involved is huge.also, only the manager has authority to generate the Bill and rest of the team can view the bill report
So i cannot do the calculations while fetching the records, Bill has to be generated and stored. How do i write the query to fetch the data from the two tables "TripSheet" and "BillTripSheet" ? Please Help
ReagardsSara

View 6 Replies View Related

How To Format A Date Field In Select Query

Oct 4, 2004

Is it possible to format the date field create_date (mm/dd/yyyy or mm/dd/yy)
I use the following query in stored proc. will be called in the asp.net page for population the datagrid.


select id, name, create_date from actionstable;

Please help, Thank you.

View 1 Replies View Related

Procedure To Save Query Out Put In Word Format

Jul 15, 2004

I would like to know , is there a way to save query results in word format.
Liek we use sp_makewebtask to save query output in html format.

Thanks

View 2 Replies View Related

SQL Query Syntax To Format Date Fields

Oct 21, 2005

Hi! Good Day!

What is the syntax to format the datefield? The value of my datefield is like this:

10/13/2005 5:15:02 PM

What is the select query to filter the date only. My desired result should be:

10/13/2005

only.

Thanks :)

View 5 Replies View Related

Query Result In HTML Table Format

Mar 21, 2008



Is there any way can I get the 'SELECT' query result in table format? I want to email the query result so that they can read it very easily.
Thank you,
Gish

View 15 Replies View Related

Query String Field Based On Text Format

Mar 7, 2008

I need to search a nvarchar field based on the format of the text.  This field holds values in two formats: 000 000 000 000 and 000000.   I only want to search through the records that are in the 000 000 000 000 format.  Can anyone give me direction on how to go about doing this or give me some key words to search for on Google?  Fixing this problem is not an option.  This is a county tax DB from a poor county with almost a million records in it. 
 Thanks for the help!

View 2 Replies View Related

How To Send Query Result As Attachment With EXCEL Format

Feb 17, 2012

i am using sp_send_dbmail to send Emails.how to send query result as an attachment with EXCEL Format.

View 1 Replies View Related

SQL 2012 :: Datediff - Query Does Not Work With Yyyyddmm Format

Mar 26, 2014

I have two dates '2014-25-03 01:02:03' & '2014-26-03 01:02:03' (yyyyddmm).

I need the difference between two dates in hh:mm:ss format.

I tried following query

declare @mindate datetime='2014-03-25 01:02:03'
declare @maxdate datetime='2014-03-26 02:03:04'
select cast(
(cast(cast(@maxDate as float) - cast(@minDate as float) as int) * 24) /* hours over 24 */
+ datepart(hh, @maxDate - @minDate) /* hours */
as varchar(10))
+ ':' + right('0' + cast(datepart(mi, @maxDate - @minDate) as varchar(2)), 2) /* minutes */
+ ':' + right('0' + cast(datepart(ss, @maxDate - @minDate) as varchar(2)), 2) /* seconds */

This works fine but as you can see the date I have used in the query is in yyyymmdd format.

The query does not work with yyyyddmm format

The expected answer is =25:01:01.

View 5 Replies View Related

Script Your Data In A T-SQL Format (which Can Be Executed In Query Analyzer)

Jul 20, 2005

Hi all,i'm working with SQL Server for about 4 years, and i have eversearching for a tool which can script my DATA in a T-SQL format tocopy them very easy from one server to another.For example scripts like that:IF NOT EXISTS (...)INSERT INTO (...)ELSEUPDATE (...)I have never found a tool like that, so i write a it by myself.This software is a free version in the moment, you can download it onmy website:http://www.sqlscripter.com..NET Framework on your client PC is required.Have fun with it ...Thomas

View 2 Replies View Related

Is There A Way To Loop Over Report Parameters, And Format Them Before Passing Them Onto The Query

Nov 13, 2007

Good morning all,


I have a report which is getting its parameters from an ASP.net page. My ASP developer wants to send in simple values, such as the list 1,2,3,4 for a parameter. However my report needs that list to look like [CD RSRC].[RSRC].&[1], [CD RSRC].[RSRC].&[2], [CD RSRC].[RSRC].&[3], [CD RSRC].[RSRC].&[4].


Is there any way, on the report services side, to capture an incoming report parameter, parse it, loop over the parsed values and format them?


I don't think there is, but I wanted to check before I go back to the developer and tell him he has to send in tuple lists.


Thanks,
Kathryn

View 1 Replies View Related

MDX Query Results Loses Format After Data Transformation

Dec 28, 2007

Hi,

My first post here. I have an Analysis Services Cube and I need to export some of its data into a flat file (a semicolon separated value file) with fixed length columns.

What I have in Integration Services now is:

DataReader Source (ADO.Net conection)
Data Transformation (NTEXT -> WSTR)
Derived Column (WSTR -> STR, and some ISNULL validation)
Flat File Destination (Delimited by ";")

the thing is I have all the measures and calculated measures in the cube formated with format strings and they work fine when I do a query through the SQL server Managment Studio, but in Integration Services before the columns are written into the file they lose its format.

for example, I have a calculated member called "Deuda Total Nacional" that returns something like this +0001234,00 and I need to take off the decimal separator so it is written into the file like this +000123400 , I'm doing it this way

(ISNULL([Deuda Total Nacional]) ? "+00000000000" : REPLACE([Deuda Total Nacional],",",""))

instead it is written like this 1234.

I hope you can help me with this.

Thanks

View 14 Replies View Related

Check System Date Format And Then Change It Thru SQL Query.

Nov 4, 2007



Hi All,

Is it possible to check the local system date format and then change the format to another format using SQL command in VB.

Eg.

-check current system format dd/MM/yyyy
-change current system format to MM/dd/yyyy format

Thanks
Jam.

View 12 Replies View Related

SQL Query - Use Date Format Data And Match It To MS-SQL's Datetime Datatype?

Apr 12, 2007

Need some quick help here.. The data I got from the text file use the "04/11/2007" date format and the StockDate in MS-SQL use the datetime datatype.

My understanding is that the "04/11/2007" will default to the 04/11/2007 12:00 am" format in MS-SQL.

So, when I use the sql query, how do search only for the date part of the data in MS-SQL and match it to the data from text file? I tried this SQL Query below.

--snip--
SELECT RawID FROM tblPurchaseRaw WHERE VIN = '" & sVin.ToString.Trim & "' AND StockDate = '" & dStockDate.ToString.Trim & "'
--snip--

That way, if I get a row then I know the data is there. If I don't get a row then I know the data is not there.

Bold: I get it now. It is all automatic as MS-SQL does it automatically...

View 5 Replies View Related

T-SQL (SS2K8) :: DB Mail Query From Mentioned Data Format Table

Apr 28, 2014

I'm sending report based on below DB Mail query from mentioned data format table. But i'm getting invalid format output in csv file.

Table Data:

CREATE TABLE Sales_Data
(
ITEM_CODE varchar(25),
Price float,
Purchased_Date Datetime

[code]...

View 5 Replies View Related

SQL Server 2012 :: Converting Query Output To HTML Format

Mar 23, 2015

I am working on a code that will convert the query output into a html query output. That is the output of the query will be along with html tags so that we can save it as a html file.

The stored procedure that i have used is downloaded from symantec website and is working fine. Below is the code of that stored procedure.

/****** Object: StoredProcedure [dbo].[sp_Table2HTML] Script Date: 12/21/2011 09:04:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_Table2HTML] (
@TABLENAME NVARCHAR(500),
@OUTPUT NVARCHAR(MAX) OUTPUT,

[Code] ....

The code works fine and i am able to get the output with html tags. The problem occurs when i insert stylesheet in the code. I tried to enforce styles using a stylesheet for the table returned in my sql code, so that it will look good. below is the stylesheet code that i inserted between <head> and </head> tags.

<style type="text/css">table.gridtable { font-family: verdana,arial,sans-serif; font-size:10px; color:#333333;border-width:1px; border-color: #666666; border-collapse: collapse; } table.gridtable td {border-width: 1px; padding: 8px; border-style: solid; border-color: #666666; background-color: #ffffff;}</style>

If I run the procedure without the style sheet code, it works fine. but when i run the procedure with style sheet code i am getting the below errors.

Msg 105, Level 15, State 1, Line 98
Unclosed quotation mark after the character string '</table></body><'.
Msg 102, Level 15, State 1, Line 98
Incorrect syntax near '</table></body><'.

[Code] .....

I checked the code and i am not able to find what is the mistake. I tried changing the quotation mark but it didn't worked.

View 6 Replies View Related

SQL Server 2012 :: Making Query For Presenting Data In Different Format?

Jun 18, 2015

I have below table:

IF OBJECT_ID('tempdb..#complaints') IS NOt NULL
DROP TABLe #complaints

--===== Create the test table with

create table #cs([Year] float,
[Week] float,
[Month] float,
[C#] float,
[Dept] nvarchar(255)
,[Issue] nvarchar(255), [Type] nvarchar(255), [Dept age] nvarchar(255))

--===== All Inserts into the IDENTITY column

INSERT INTO #cs
([Year], [ Week], [ Month], [C#],[Dept],[Issue], [Type], [Dept age])
SELECT 2015, 14, 4, 188, D1,I1,T1, 5 UNION ALL
SELECT 2015, 14, 4, 452,d1, I1, T2, 5 UNION ALL
SELECT 2015, 14, 4, 63, d1, I1, T1, 6 UNION ALL
SELECT 2015, 14, 4, 9, d1,I2, T1, 7 UNION ALL
SELECT 2014, 14, 4, 187, D1,I1,T1, 5 UNION ALL
SELECT 2014, 14, 4, 451,d1, I1, T2, 5 UNION ALL
SELECT 2014, 14, 4, 62, d1, I1, T1, 6 UNION ALL
SELECT 2014, 14, 4, 10, d1,I2, T1, 7 UNION ALL)

and i want to have a table in below format: (query for it)

Week, Month, C1#,c2# Dept,Issue, Type, Dept age
14, 4 188 187 d1 i1 t1 5
14, 4 452 451 d1 i1 t2 5

I.E. I WANT two columns C1# and C2#, where C1# contains data from 2015 and C2# contains data from previous year (2014). If 2015 data is not present, then C1# will contain data of 2014 and C2# will contain data of 2013.

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved