Field Formatting
Apr 24, 2008Hi
Is there a way to format a field in a table with a datetime data type to show just the date?
ie. 24/04/2008 17:25:00 would just become 24/04/2008.
Even showing 24/04/2008 00:00:00 would be helpful.
Thanks
ADVERTISEMENT
Formatting A Datetime Field
Jun 13, 2002I am selecting a datetime value from a table but I want it to be
in this format: mm/dd/yyyy
This query is as close as I have come:
select char(month(nancyDateSent))+ '/'+ datename(day,nancyDateSent) + '/' + datename(yy,nancyDateSent) from tbloutgoingobms_hold
but is not correct - I get dates that look like this: /11/2002
Please help - there must be an easier way !!!
Thanks in advance,
Nancy
Formatting A Money Field
Jun 12, 2008Hi guys,
Presently I have a column which represents the amount of money someone spent and for some reason, the fields are being outputted like this 29.8600.
This field is infact suppose to 29.86. Now initially this file was an excel spread sheet and I imported it into sql server 2000 and the datatype is money. What would i use to get the desired field.
Formatting A Varchar Field
Jun 12, 2008Hi guys,
How do you convert a date varchar field which looks like 20080525 to 2008/05/25?
Formatting A Field On SQL Server
Sep 11, 2007Hello again,
I have a huge question haha. I just simply want to make a number display differently before I insert it into the table. for instance in Access, you can simply say FORMAT(c,format) ex: FORMAT(@CHARMINS, 00000) and it should work that way.
How would I accomplish this in SQL server? Right now I have the number 30 displaying as 30.00 in the variable it is set to.... I want it to be shown as 00030.... and any other number that is like 30.00, I want it shown the same way. Like 100.00 would be 00100. Thank you.
Formatting A Field To Spec In SQL
Oct 5, 2006
Can anyone tell me how to format a field to spec?
Example:
Select num_field from table = 12345
I want
Select Format(num_field,"0000000000") from table = 0000012345
or some equivalent. I know in Access you could do this using the Format function, but I cannot seem to find anything like this in SQL.
Thanks in advance,
Michael
Partial Field Formatting
Feb 8, 2008Is it possible to format just part of a field? I've got a report that return a collection of users based on some search criteria. Bascially, you enter a string and the report returns all users with a user name or display name simialr to your search string. I wanted to highlight the part of the name that matches the search string.
For instance, if you search for 'nny'
You might see a result of 'Johnny User'
Thanks.
J
Formatting For A Birth Date Field??
Sep 16, 2006Hello, I'm new to SQL Server, working for a non-profit computerizing alot of its data.I imported a table of people's names, birth dates, etc. into SS2005from Access, and the birth_date was imported as an Access date/timefield, giving it the datetime datatype in SQL.The column values look like:10/14/1964 12:00:00 AMWhere and how do I learn to specify that all fields like this should bein ISO format of yyyy-mm-dd??Do I have to create a new column and put all the dates into it??Should I just convert the data in queries/views??Use a constraint to format the data??I can redo the Access table if necessary, it is only 300-some rows.I tried BOL but it was not helpful...The end users will likely enter mm/dd/yy or mm/dd/yyyy and it will haveto be stored properly in the database table as column/fieldbirth_date...Thank you, Tom
View 11 Replies View RelatedRemoving Rich Text Formatting From A Field
Feb 6, 2007I have a field where the data is stored with RTF. I know SQL Reporting Services cannot display it properly. Is there way to remove the RTF formatting codes through a function to just pull out the text and displya it in the text box?
View 4 Replies View RelatedExpression: Conditional Formatting Of Field Size!
Apr 11, 2008Can I build an expression that allows me to change the field size of a column or row in SSRS2005?
View 5 Replies View RelatedReporting Services :: Formatting A Date Field To Just Show Date Not Minutes?
Jul 6, 2015A date field in a report returns the date value as:
2015-07-01 13:30:27.000
Is there any way I can script this to appear as:
01 July 2015 (or 01-07-2015 or 01/07/2015) - basically to cut out the hours, minutes and seconds?
The best I have managed is: CONVERT (varchar(17),DATE,113) AS Date1 but this still leaves me with:
01 July 2015 13:30
Transact SQL :: Return Field When A Field Contains Text From Another Field
Aug 25, 2015I'm new to SQL and I'm trying to write a statement to satisfy the following:
If [Field1] contains text from [Field2] then return [Field3] as [Field4].
I had two tables where there were no matching keys. I did a cross apply and am now trying to parse out the description to build the key.
How To Add Date Field And Time Field (not Datetime Field )
May 4, 2006Good morning...
I begin with SQL, I would like to add a field that will be date like 21/01/2000.
Actually i find just "datetime" format but give me the format 21/01/2000 01:01:20.
How to do for having date and time in two different field.
Sorry for my english....
Cordially
A newbie
Formatting
Aug 18, 2005Hello all,I have a strange problem that i need some advice on. I have the following field called FILENO. It is a SQL 2000 field with the Data Type set to Char (7). The following sql statement works perfectly:SELECT TOP 1 RTRIM(FILENO) AS TEST, RIGHT(DATEPART(Yy, FILENOYEAR), 2) AS YEAR FROM tblRecordsWHERE RIGHT(DATEPART(Yy, FILENOYEAR), 2) = '05'ORDER BY FILENO DESCIt returns the correct data, 0050. Now, what i'm trying to do is add 1 to the value so i can get the next available number which is 0051. But, when i run the following sql statement, i get 51 instead of 0051.SELECT TOP 1 RTRIM(FILENO+1) AS TEST, RIGHT(DATEPART(Yy, FILENOYEAR), 2) AS YEAR FROM tblRecordsWHERE RIGHT(DATEPART(Yy, FILENOYEAR), 2) = '05'ORDER BY FILENO DESCDoes anyone have an idea how to solve this? Thanks.Richard M.
View 1 Replies View RelatedSQL Formatting.
Feb 28, 2001I'm working as a consultant for a project and I have been having constant issues with the in house "database guru" about stored procedure formating.
He insists I use a format similar to this:
create procedure getUser @UserId int = NULL
as
select
UserName
, UserPassword
, UserRole
from
Users
join
UserRole
on
User.ID = UserRole.ID
where
User.ID = @UserID
and
User.Active = 1
where as I prefer this format:
create procedure getUser
(
@UserId int = NULL
)
as
select UserName,
UserPassword,
UserRole
from Users
join UserRole on User.ID = UserRole.ID
where User.ID = @UserID
and User.Active = 1
Now normally I don't argue about style issues but I find his format confusing and it bothers me that it senselesly wastes lines make the stored procedure longer than necessary.
I'd love to get some comments on this issue or reference to any discussions on issues such as this.
Thanks
SQL Formatting
Apr 9, 2008Hello -
Does anyone know why SQL Server Reporting Services formatts SQL weird? -- If I type a SQL statement in notepad or notepad++ and then paste it in Reporting Services it will change the whole structure and "expand" it out to three or four times its size.
Thanks
Adam
Formatting In Sql Query
Dec 12, 2006Hello All,
Is it possible to format an integer to a float value with 2 decimals in an sql query.
If yes, Please help?
SQL Formatting A SUM As Currency
Jan 24, 2007I need to show my SUM of the 2 columns added in the query below formatted as currency. Is this possible?
SELECT SUM(QVSTDN + QVNONC) AS Total FROM INVOICE_TBL WHERE QVORDN = @QVORDN AND QVINV = @QVINV
I tried:
SELECT CONVERT(varchar(12), SUM(QVSTDN + QVNONC) , 1) AS Total FROM INVOICE_TBL WHERE QVORDN = @QVORDN AND QVINV = @QVINV
But this does not format it as currency. Any input would be helpful.
Formatting Dates
Apr 25, 2007I imported data from a text file, where dates are currently in the format: 050729 to represent July 29, 2005.
How could I write a sql statment to put make this show up as 07/29/05?
Thanks
Text Formatting In SQL
Jan 31, 2008I am trying to output data from SQL Server to an email body message with a little formatting (just to make it legible)
Using this SQL statement:
select top 1 'Name', app_first_name+' '+app_last_name [name] ,'Submission Date', submission_date ,'Address', app_address+', '+app_city+', '+app_state+' '+app_zip from vw_bpa_export where app_email = dave@email.com order by submission_date desc
the resulting output displays this in the email body
"Name","Brian Vogler","Submission Date","2008-01-30 07:45:00","Address","6285 Harrow Trace, Norcross, GA 30092 "
but what I want is the text to look more like this:
Name Brian VoglerSubmission Date 2008-01-30 07:45:00Address 6285 Harrow Trace, Norcross, GA 30092
Is there a way to do this in the SQL statement (without HTML tags)?
Formatting A Filter
Jun 9, 2008In a SQLDataSource, the following line throws an error as an undefined function. FilterExpression='left$(lname,1) = "D"'
Isn't this a standard VB function and why does it produce an error. How do I fix?
Thanks
Formatting Dates
Dec 13, 2000Hi,
In MS Access I can do the following:
Format([SW_Dttm],"Short Date")
What is the equivalent in SQL Server?
Thanks,
Bill
Formatting Values
Jun 19, 2000I have values in my database that I'd like to display as $xxx,xxx.xx. Some of these values use the money datatype and others use float.
I'm building a string in a stored procedure that is eventually passed to Visual Basic. I want to format these values in the above format in the string that is passed. I've searched all through MSN and I couldn't find anything related to VB's FORMAT function in SQL Server.
Does Transact-SQL have any functions that could format these values for me?
Data Formatting
Mar 13, 2001Does anyone familiar with a tool or a quick way to put apostrophes and commas around some values, so they could be used in a query ? Example, if I have a bunch of zip codes such as:
10001
10003
10009
54321
89043
etc
Let's say I have a couple of hundreds of them. I need to be able to see them as -
'10001','10003','10009','54321','89043' etc
Appreciate very much your time.
Ivan
SQL 6.5 Query Formatting Help
Feb 1, 1999I am trying to run a query for the company that I work for and I am getting the right data but more than needed. the query is like this
select distinct(companyname),max(calldate) from tablename
what I want to see is the latest call made to a customer for each company
but what I get back is the company listed several times with the dates of each call. I only want the latest. Any help?
Formatting Data Help!!
Apr 12, 1999Hello Everyone,
I have been requested to take a number of columns and concatenate them into one column with a specifc format/layout. For example I need to take this, (noting all columns are of different datatypes:
col_1 col_2 col_3 col_4 col_5 col_6 col_7
PK #1 John Doe 123 Elm Street Anywhere NM 55660
#2
#3
etc..
...and create this on a different table (noting col_2 is of datatype "text":
col_1 col_2
PK #1 John Doe
123 Elm Street
Anywhere NM 55660
#2
#3
etc..
Can this type of formatting be done?
I know how to concatenate data and use the convert function, but how do you add in CR-LF to get the proper formatting??
Any help would be greatly appreciated.
Troy
Xp_sendmail - Formatting
Jun 12, 2001Requirement:
On Insert, Trigger xp_sendmail to complete pre-formatted email with specific content and send to given email address.
Question:
What tools/methods are available to develop a template that SQL Mail's (SQL Server) xp_sendmail function can utilise without resorting to attachments.
e.g. - how do Amazon.com format and send their automated shipping confirmaion emails ??
Date Formatting
Oct 10, 2001I'm currently getting a date in this format 2001-10-08 10:35:45
(yyyy-mm-dd hh:mm:ss) how would I convert the date to this format
2001-10-08/10:35:45 (yyyy-mm-dd/hh:mm:ss)? The only thing added was the / between the date and time.
Thanks in advance,
Ed
Date Formatting
Aug 6, 2004Hi,
I need to know how i can format the date so that the query results will come out as the format mm/dd/yy hh:mm:ss?
Thanks alot.
Getting Formatting Into Data
Sep 8, 2006I am new to php/MySQL and am working on an online manual. The data is divided into Title and Content. The title is easy as it is one line but the content is meant to be in a number of paragraphs.
The Content field is currently set as "blob" and the text comes out as just one paragraph. Wha can I do to get paragraphs etc into the text?
Thanks.
Formatting Output
Oct 7, 2004Hi, I am running a script which inserts certain rows into a table and at the end of the execution, I do a select statement to show the inserted data as output in the results pane and it is showing as truncated...How can I show the full results..
Here is my script to show the results.
----------
SET NOCOUNT ON
DECLARE @errorCount INT
SELECT @errorCount = COUNT(*) FROM error_report WHERE id != - 2 ANDid != - 5
IF @errorCount = 0
BEGIN
INSERT INTO error_report VALUES( '' , - 1 , 'No error found.' )
END
INSERT INTO error_report VALUES( '' , - 2 , 'The Report was generated on ' + CAST(CONVERT(VARCHAR(23), GETDATE(), 1) AS VARCHAR) )
GO
SELECT table_name + ' table has bad data at id = ' + CAST(CONVERT(VARCHAR(23), id) AS VARCHAR) + ' (' + CAST(reason AS VARCHAR) + ')'FROM error_report WHERE id > 0
SELECT table_name + ' table has bad data (' + CAST(reason AS VARCHAR) + ')' FROM error_report WHERE id = 0
SELECT reason FROM error_report WHERE id = - 1
SELECT ''
SELECT reason FROM error_report WHERE id = - 2
SET NOCOUNT OFF
GO
-------------------------
The Results in the bottom pane looks like this below
------------------
NODETABLE table has bad data (There are 2 duplicate subclass)
Propertytable table has bad data (at Parentid = 2000000859 and p)
Propertytable table has bad data (at Parentid = 10122 and proper)
-------------------
But, when I do a select, they are like this below
----
NODETABLE0There are 2 duplicate subclass name: Specification
Propertytable0at Parentid = 2000000859 and propertyid = 721
Propertytable0at Parentid = 10122 and propertyid = 9
Formatting Numbers
Jan 18, 2005I am using a MS Access ADP connected to SQL Server Data
In a view I have the following formula:
dbo.tblQuoteItem.Cost + dbo.tblQuoteItem.Markup * dbo.tblQuoteItem.Cost * .01
this calculates cost + markup
I cannot get it to format in Currency
Ex:
Cost is $1.75
Markup is 2.00 (2%)
Total shows - 1.785000
I want $1.78
Also - I am using this formula to calculate the Quoted price for the Qty Entered
dbo.tblQuoteItem.Qty * dbo.tblQuoteItem.Cost + dbo.tblQuoteItem.Markup * dbo.tblQuoteItem.Cost * .01
Using a Qty of 2 for above, I get 3.535000
I want $3.53
Any help is appreciated - AB
BCP -- Formatting Output
Jun 13, 2007Hi,
I wrote the below code and procedure that exports two tables contents into 2 separate Excel files. Is there a way to export contents of two tables via BCP utility into 1 Excel file but 2 different Worksheets of this file?
DECLARE @FileName varchar(50),
@FileName1 varchar(50),
@bcpCommand varchar(2000)
SET @FileName = 'E:GPPD_db_stats.XLS'
SET @FileName1 = 'E:GPPD_file_stats.XLS'
print @FileName
SET @bcpCommand = 'bcp "master.dbo.spdbdesc" OUT ' + @FileName + ' -Samex-srv-gppdb -T -c'
print @bcpCommand
EXEC master..xp_cmdshell @bcpCommand
SET @bcpCommand = 'bcp "master.dbo.spfiledesc" OUT ' + @FileName1 + ' -Samex-srv-gppdb -T -c'
print @bcpCommand
EXEC master..xp_cmdshell @bcpCommand
exec master.dbo.xp_stopmail
set @bcpCommand = ' ' + @FileName + '; ' + @FileName1 + ''
DECLARE @body VARCHAR(1024)
SET @body = 'Please find enclosed files with the database status reports as of '+
CONVERT(VARCHAR, GETDATE()) + '. Please DO NOT respond to this email or the ones coming in the future ' +
'with data files as this email address is not monitored for incoming emails. However, if you have any ' +
'questions/concerns please contact ....'
EXEC master..xp_sendmail
@recipients='alla.levit@amex.com',
@message = @body,
@subject = 'Database Weekly Statistics Report',
@attachments = @bcpCommand
Thanks in advance!
-Alla