Changing Data Format On Select

Jul 20, 2005

Hey all,
I have a basic table that looks something like this.

CREATE TABLE MyTable
(
ID INT IDENTITY PRIMARY KEY,
Company_ID INT NOT NULL,
Round VARCHAR(50) NOT NULL,
Details VARCHAR(250) NOT NULL
)

It has a few rows of data that look like this:

Identity Company_ID Round Details
--------------------------------------------
1 5 A Blah, blah.
2 5 B Generic data, blah blah.
3 5 WERT More generic blah blah.


Now what i'm trying to do during my select statement is select all the rows
that belong to company_id 5 but if any of the rows round value contains the
text "WERT" convert that text into just a "--" for presentation purposes,
but still select that row. I can't seem to figure out how i would transform
the text in the select statement? My immediate thought was substring /
replace but i would need to combine it with an if else statement which i've
no idea how to make work in a select (sub-query maybe?) statement. Is this
possible? Perhaps i'm stuck iterating through the returned data within the
client application before presenting?

Any help, as always, would be greatly appreciated.

Muhd

View 5 Replies


ADVERTISEMENT

Changing Format Of Data In Gridview Using VB

Mar 24, 2008

Hello all,
I am trying to modify the output of a SELECT statement in a VB asp.net page that pulls data from a SQL DB. I only need to modify one column in the gridview, but I'm having some issues.
Here is the situation:SELECT     TOP 24 ID, RecID, Timestamp, Answered, Holds, Dropped, Waits, Voicemail, Busied, LiveWaitsFROM         mTrafficORDER BY Timestamp DESC
The Timestamp column returns a number value (i.e. 564566).In terms of a date, 564566 doesn't mean anything to the user, so I need to convert this number into a recognizable and accurate date.
The formula I need to implement is Timestamp/1440 - 1
The following SELECT statement returns the number as a date, but makes all records the same date:
SELECT     TOP 24 ID, RecID, Convert(datetime, Timestamp/1440 + 1) as Timestamp, Answered, Holds, Dropped, Waits, Voicemail, Busied, LiveWaitsFROM         mTrafficORDER BY Timestamp DESC
What do I need to do to implement this function dynamically into my gridview?
Thanks in advance!
 
Chris

View 2 Replies View Related

Integration Services :: How To Load Data Without Changing Excel File (CSV Format)

Jul 14, 2015

Facing problem while loading date in MS SQL Server 2005 from excel file (csv format).

How to load the excel file data without changing the excel file (csv format) .

see the [Start Date] and [ Exp End Date] having values like this : " 2015/07/31"

View 5 Replies View Related

Transact SQL :: Select Data From Table With Custom Format

Sep 15, 2015

I want to select custom format from table?

View 7 Replies View Related

Changing Table Format

Mar 11, 2004

Hi there,

Its probably easier to draw this problem than describe it, so here goes:

I have a sales forecast table (A,B,C are products [PRODUCT], the dates refer to the month for which the forecast is [FORECAST_DATE], and the integer is the forecast sales qty [FORECAST_QTY])

A 01/03/2004 30
B 01/03/2004 28
C 01/03/2004 24
A 01/04/2004 11
B 01/04/2004 09
C 01/04/2004 41

I need to convert the table into a more sensible format, like this:

(NB ...Dots are just there to help with formatting - basically I'm talking about a field for FORECAST_03_2004, FORECAST_04_2004 etc etc)


........ 01/03/2004....01/04/2004
A..........30..................11
B..........28..................09
C..........24..................41


I'm really no t-SQL guru, and I can't seem to get any joy out of BOL. It must only be a tiny bit of code. Can anyone help?

Thanks very much

Sam

View 4 Replies View Related

Changing Date Value Format

Dec 6, 2013

Aim – Currently i have column “[LAST-STATUS-CHG]” in the following date format “042312” i need this date format to be changed into “23-04-12”.

Also i would like an additional column created called “Start of month” this column should look at “[LAST-STATUS-CHG]” and what ever the day is in the defauly it to 01

Current results
FDMSAccountNoACCOUNT-STATUSLAST-STATUS-CHG
87800000088416042312
87800000088513011212
87800000088612100712

Desired results
FDMSAccountNoACCOUNT-STATUSLAST-STATUS-CHGStart_of_month
8780000008841623-04-1201-04-12
8780000008851301-12-1201-12-12
8780000008861210-07-1201-07-12

My query is
SELECT [FDMSAccountNo]
,[ACCOUNT-STATUS],
[LAST-STATUS-CHG]
FROM [FDMS].[dbo].[stg_LMPAB501]
where FDMSAccountNo = '878000000884'

View 2 Replies View Related

Date Format Changing In SQL SMS

Nov 27, 2007

Howdy,

I live in Australia and use the date format of - dd/mm/yyy, but when i do a query in SQL Management it seems to want its inputs in US format mm/dd/yyy.

For example it errors when i do a search and one of the dates is 27/11/2007, ie there is no 27th "US" Month.

I have checked my local settings (regional and language in control panel), and it all points to Australian formats, it just seems the SMS wants the dates in US.

Any Ideas?



Dwayne Schaffarz

View 4 Replies View Related

Changing The Format In Flatfile

Jan 28, 2008



Hi,

Iam migration data from a table into a comma delimited flatfile,but i need to specify all the columns within [ " ] in the flatfile

for example

i have a column [Name] the values are John,Mani,Raghu.....

The flat file should be outputted as
"John"
"Mani"
"Raghu"

Is there anyway to do this.Pls help.

Thanks,
SVGP

View 3 Replies View Related

Changing SQL Server Date Format

Jul 19, 2002

SQL 2k w2k server sp2

When I first installed the SQL server on my server, the default date
format was mm/dd/yy. Now I need to change that to dd/mm/yy in
regional settings, but its still not reflected in the DB's in SQL (its
still mm/dd/yy).


Is it possible to change it in SQL without a complete reinstallation
of the server?

View 1 Replies View Related

DTS To Excel Export - Changing Format

Oct 26, 2004

Hi,

I have a dts package which is reading from a sql table and writing it to an excel file, its working fine except that I have a decimal field in the sql table but in excel file its writing it as string field.

The way I create this package is that I create a template file and I format that column to a "Number" format. Then I take this template file, rename it, export all the data to this file.

But when I open this file that decimal field is displayed as a string column and its left aligned.

Is there any way to fix this problem?

Thanks,

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

Changing Identity Column Format

Mar 19, 2007

Hi,

Is it possible to change the format of the identity column? i.e. I have and integer value that will be the ID for the records to follow in the database. Instead of the values being 1,2,3,4,5,6,7,8,9 etc I want them still to be in sequence but I want the format to be 001,002,003,004,005,006,007,008,009 etc.

Is it possible to do that in SQL when you're creating the table?

View 1 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

Changing Date Format And Casting As Varchar?

Jul 21, 2014

My current Query takes the DATE value stored in P.CreatedDate and makes it VARCHAR, then stores it in the table. I need to format this to return YYYYMMDD. How would I go about this?

Current Code:

Code:
CAST(P.CreatedDate AS VARCHAR) AS DateEntered,

View 3 Replies View Related

Reporting Services :: Changing Date Format In SSRS

Nov 5, 2015

Fields!TaskStartDate.Value &  Fields!TaskName.Value & Fields!StatusForExecutiveReporting.Value & Fields!NotesForExecutiveReport.Value

This is my expression in a cell.when i run my report  task start date value is something like the:

01/25/2015 8:00:00AM.

I want this date to be in this form: jan 2015

View 3 Replies View Related

I Want Ot Convert Xml Data Format Into The General Data Format

Jun 9, 2006

hi siri have table hh .it has two columnsone is hhno is integer datatype another hhdoc is xml data type likehh tablehhno hhdoc---------------------------------------------------------------------------------------------100<suresh>sfjfjfjfjf</suresh>....................................101<ramesh>hhfhfhf</ramesh>..................................how to convert the xml data format into the general data format plshelp me with examples

View 1 Replies View Related

Changing Output Of Select

Feb 18, 2015

I have a table: it contains a list of data belonging to a name, however the field 'name' defines the source.

name |field1 |field1data |

test1 |in |123
test1 |out |345
test2 |in |123
test2 |out |456
test3 |in |123
test3 |out |456

I'm trying to get the data out of the database in a columed format, I would be quite comfortable doing this if 'in' and 'out' where there own field. The output i'm trying to get is:

name |data |data |

test1 |123 |456
test2 |123 |456
test3 |123 |456

in an ideal world i'd like to be able to have the header the name of the field data - eg

name |in |out |

test1 |123 |456
test2 |123 |456
test3 |123 |456

I can use - select name,field1data from table where field1 in ("in","out")but this returns each piece of data as a new row - eg:

name |field1data |

test1 |123
test1 |456
test2 |123
test2 |456
test3 |123
test3 |456

View 1 Replies View Related

Changing The SQLDatasource SELECT For A Gridview

May 19, 2008

I cant seen to change the Select command for a SQL Datasourcetry #1            SqlDataSourceProfilesThatMatch.SelectCommand = strSQLForSearch            SqlDataSourceProfilesThatMatch.SelectParameters("ProfileID").DefaultValue = pProfileID            SqlDataSourceProfilesThatMatch.SelectParameters("LoggedInUsersZipcode").DefaultValue = pUsersZipCode            SqlDataSourceProfilesThatMatch.SelectParameters("ZipDistance").DefaultValue = pDistance
            NewProfilesThatMatchGridView.DataBind()
try #2            SqlDataSourceProfilesToBeMatched.SelectParameters.Clear()            SqlDataSourceProfilesThatMatch.SelectCommand = strSQLForSearch            SqlDataSourceProfilesThatMatch.SelectParameters.Add("ProfileID", pProfileID)            SqlDataSourceProfilesThatMatch.SelectParameters.Add("LoggedInUsersZipcode", pUsersZipCode)            SqlDataSourceProfilesThatMatch.SelectParameters.Add("ZipDistance", pDistance)
            NewProfilesThatMatchGridView.DataBind()
No errors but no rows show in the gridview. If I debug and get the value strSQLForSearch and paste it into a new SQL query window I get results.
Any ideas????
Thanks
 

View 3 Replies View Related

DB Engine :: TOP Changing Cursor Select Performance

Jun 15, 2015

the cursor at the bottom  iterates only to print the number of rows.The problem is in the select. This takes 30 seconds to iterate through 1242 records.But if I add a TOP 1000000 or whatever number to the select, the same iteration takes less than a 1 second.I've tested each query without cursor, and  both have the same cost and performance. (Not exactly the same plan)Note that I got the same performance improvement declaring the cursor as STATIC.Why the top is affecting the cursor iteration so much?

Declare @query varchar(512)
DECLARE Itera CURSOR --LOCAL STATIC READ_ONLY FORWARD_ONLY
FOR
select --TOP 1000000

[code]....

View 2 Replies View Related

How To Select In The Below Format ?

Oct 11, 2006



This is my Table :

date(m/dd/yy) points Work

9/9/06 3 Design
9/10/06 3 Design
9/11/06 3 Programming
9/12/06 3 Design
10/9/06 3 Design
10/10/06 3 Design
10/11/06 3 Programming
10/12/06 2 Design

How to select in the below format

Month/Year Design Programming
9/06 9 3
10/06 8 3

View 4 Replies View Related

Do We Have To Alawys Use Slowly Changing Dimensions (SCD) Component In The Data Flow For The Loading Of Table Data?

Feb 28, 2008

Hi, all experts here,
Do we always have to use SCD component for the loading of data into data warehouse to handle changes of rows?
I am looking forward to hearing from you and thank you very much in advance for your help.
With best regards,

View 4 Replies View Related

T-SQL (SS2K8) :: Load Data From Flat File Source Into OleDB Destination By Changing Data Types In SSIS

Apr 16, 2014

I have an source file and i have to load it into the data base by changing datatype of the columns in ssis

View 1 Replies View Related

Select Datetime Format Changes

Apr 9, 2008

Hello

I'm hoping someone can help explain why when i select timestamp field
2008-03-25 18:57:39.000 the view result changes to
04-04-2008 11:12:01 AM.

I need result of view to be in same format as table.

Thanks

Fred

Fred

View 17 Replies View Related

Format Number On Select

Dec 26, 2007

I have a price field that when I query it, it returns the price as"5.0000". What function can I use to return the price as "5.00"?THANKS

View 4 Replies View Related

Data Unexpectedly Changing In Data Flow Task

Oct 3, 2007

I have a fairly simple data flow task that loads data from one table (OLE DB Source) into another table (OLE DB Destination). The data type for one of the pairs of columns is nVarChar(120) and it contains version information that looks like a decimal. When I run the export, the destination has a trailing zero added after the decimal point as if it were a numeric column which invalidates our comparisons (string 1.0 is not the same as string 1.00). There is no cast or convert done to this column, it is a straight copy. Any ideas what could be causing this or how to fix?

View 6 Replies View Related

Changing Data Source Programatically Does Not Transfer Data

Aug 29, 2006

I have created a SSIS package that transfer data from a Foxpro database to an instance of SQL Server 2005 Express. I used the wizard to create the package but I load and execute the package within a custom application that I have written in C#.

The way the custom application is intended to work is that the user can have the database in any location on the computer and all he has to do is specify the location then the application programatically changes the location of the source on the package that it has loaded and then execute it. When I initially run the package the first time (using the original path), it works fine and transfers the data. However, every subsequent time I run the application and specify a different path, the database on the SQL Server side gets created as expected but the data is not transfered!

Where am I going wrong? Do I need to save the package after I modify the source then reload and run it again or do i need to change something else in the Data Flow to make this work?

View 4 Replies View Related

Select Month(getdate()) Format Plz

Apr 11, 2008

How can i return month in the format of 'April'.
 
When i am trying select month(getdate()) i am getting 4 but i am looking for text format - April.
 
Please correct format.
 
Thanks
Dk

View 2 Replies View Related

Select And Format Zip Code Results

May 19, 2008

In my select statement I concatenate city+state+zipcode. In some cases the zipcode is 9 digits but is missing the '-'. How can I format zipcode to be '#####-####' when > 5 characters from the select statment.
Thanks,
Ken

View 1 Replies View Related

How To Select Amt With Thousand Separator Format?

Mar 10, 2008

Hi,
Can you tell me the query for selecting the amount with thousand separator format?

for example : select 1234.678 is need to show the output line 1,234.68

View 7 Replies View Related

How To Select The Time From DateTime Format (MSSQL) ???

Oct 31, 2006

Hi expert.. I am doing a project that have function to capture the Day and Night time talking duration of caller.... I make use of MSSQL to store the date time file..Let say I have this date: "2006-10-02 00:02:09".. How di I select "00:02:09" only???? Thanks in advancesuigion

View 2 Replies View Related

How To Change Date Format In A Select Statement

Dec 1, 2006

 when i use this command in a aspx file
"SELECT DISTINCT Format$([dbo.classgiven.classdate], 'mm/yyyy') AS monthyear,{.............................. 
 
'Format$' is not a recognized function name.
 
so how do i change date from mm/dd/yyyy to mm/yyyy

View 2 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

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







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