Formula help ... With Dates

I am pulling in the 2 digit month 01,02,03 etc.... and the four digit year(2005)
I want a formula to only pull the previous 12 months...
Any help... Ideas? I am fairly new to CR... not good w/ the formulas yet.Create a formula and put your date field in there and subtract 365 (the number of days in a year. Also, there is a Year(X) function, this should also prove to be useful.sql

Formating Numbers with Commas

I am pulling numbers from a SQl Table and adding some together and
doing other calculations. How do your format the numbers to insert a
comma and show thousands?Display formatting is always done In the front end, of course This is
the basic idea of a tiered architecture, which is more fundamental than
just SQL.

Formating Numbers with Commas

I am pulling several numbers from a SQL table, adding them and doing
various calculaitons. The numbers do not display a comma to separate
thousands. What is a way to format this?Presentation and formatting are usually done in the client, not the
server. In this case, for example, many countries do not use a comma
for separating thousands, so your client application can check the
user's locale and apply the correct formatting.

Simon|||There's no easy way to do this. For some reason I did need output like
this and wasn't able to use a front end to do the formatting, so I made
my own function.

Use as so:

SELECT dbo.Format_Number(513434512.2344)

Output is $513,434,512.23

Yes, it rounds and adds a dollar sign. But you can change it around.
:)

HTH,
Jennifer

CREATE FUNCTION Format_Number (@.N decimal(18,2))
RETURNS nVarChar(30)

AS

BEGIN

Declare @.NRnd Decimal(18,2)
Declare @.Dollar nVarChar(30)
Declare @.Dollar2 nVarChar(30)
Declare @.L int
Declare @.A int
Declare @.B int
Declare @.C int
Declare @.Cents nvarchar(20)
Declare @.NC nvarchar(30)

Set @.NC = Cast(@.N as Nvarchar(30))

Set @.NRnd = Round(@.N, 0, 1)
Set @.Dollar2 = ''
Set @.Dollar = Cast(@.NRnd as NvarChar(30))
Set @.Dollar = Substring(@.Dollar,1, Len(@.Dollar) - 3)

Set @.C = PATINDEX('%.%',@.NC)
Set @.Cents = Substring(@.NC, @.C, 3)
Set @.L = Len(@.Dollar)
Set @.A = @.L/3

Set @.B = 3
While @.A >= 0
Begin
Set @.Dollar2 = Substring(@.Dollar,@.L - @.B + 1,3) + ',' + @.Dollar2
Set @.B = @.B + 3
Set @.A = @.A - 1
End
If Left(@.Dollar2,1) = ','
Set @.Dollar2 = Substring(@.Dollar2, 2, Len(@.Dollar2))

Return '$' + Substring(@.Dollar2,1, Len(@.Dollar2)-1) + @.Cents
END|||>> For some reason I did need output like this and wasn't able to use a
front end to do the formatting, so I made my own function. <<

Since this is a fundamental violation of software engineering
prtinciples, might you share with us WHAT that reason was? It is worth
a paper in a journal.|||It was a totally stupid reason, of course. :) My boss wanted an email
output of a query emailed to him on a daily basis, so I set up a job to
do that. And then he came back and said, it sure would be nice if
those dollar amounts looked like dollars, and could the output be
changed. So being completely new and straight out of school I did as
asked.

Let me know how that paper comes out, will you? ;)|||--CELKO-- (jcelko212@.earthlink.net) writes:
> Since this is a fundamental violation of software engineering
> prtinciples, might you share with us WHAT that reason was? It is worth
> a paper in a journal.

The world is not always as ideal as you may want to be. There are probably
tons of business reports out there that are run from no other front end
than Query Analyzer, or similar tool. For some reason, someone started to
do it in QA, probably because it was a little urgent, and not possible to
pack into something better. Then that temporary hack became permaent etc.
Until one day, the requirements goes beyond what is really healthy to do
in SQL.

Anoher reason could be that the front-end tool is hopelessly difficult
to use...

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Format Textbox as HTML?

I am pulling some stuff from sharepoint and the text is formated as
html. Is it possible to have the textbox interpret this?This is not supported in the current release (but is on our future features
wishlist).
If you're really ambitious, you could post-process the rendered HTML to
convert < to < and > to >, thereby simulating support for this in the
HTML renderer. But that won't work for other renderers (PDF might
technically be possible to postprocess, but it would be a lot of work).
--
My employer's lawyers require me to say:
"This posting is provided 'AS IS' with no warranties, and confers no
rights."
"Braden" <bluesv650@.hotmail.com> wrote in message
news:400eb9cb.0407090935.3f735bf2@.posting.google.com...
> I am pulling some stuff from sharepoint and the text is formated as
> html. Is it possible to have the textbox interpret this?

Format a number to '00'

Hi,
I am pulling the month from a date like this:
Cast(DatePart(m,[bhDate]) as varchar)
if it comes back 12, I get 12, if it comes back as the 7th month, I want to
get '07'.
I normally use the FORMAT function in VBA, but having trouble finding how to
do it in T-SQL
Any thoughts,
SteveTry:
select
replace (str (DatePart(m,[bhDate]), 2), ' ', '0')
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"SteveInBeloit" <SteveInBeloit@.discussions.microsoft.com> wrote in message
news:CA25F627-E9E8-4147-8D3F-EAABF2D71441@.microsoft.com...
Hi,
I am pulling the month from a date like this:
Cast(DatePart(m,[bhDate]) as varchar)
if it comes back 12, I get 12, if it comes back as the 7th month, I want to
get '07'.
I normally use the FORMAT function in VBA, but having trouble finding how to
do it in T-SQL
Any thoughts,
Steve|||Try this REPLICATE('0',2-LEN(Cast(DatePart(m,[bhDate]) as varchar)))+
Cast(DatePart(m,[bhDate]) as varchar)
--
Thanks & Rate the Postings.
-Ravi-
"SteveInBeloit" wrote:

> Hi,
> I am pulling the month from a date like this:
> Cast(DatePart(m,[bhDate]) as varchar)
> if it comes back 12, I get 12, if it comes back as the 7th month, I want t
o
> get '07'.
> I normally use the FORMAT function in VBA, but having trouble finding how
to
> do it in T-SQL
> Any thoughts,
> Steve
>|||Here is another approch which could be shaved down to smaller set of command
s
but I thought the long version would help with concept.
DECLARE @.v_Month VARCHAR(2),
@.V_Length VARCHAR(10),
@.v_DisMonth VARCHAR(2)
-- Get Month
SELECT @.v_Month =Cast(DatePart(m,GETDATE()) as varchar)-- Feb
-- See Month
SELECT @.v_Month-- before conversion
-- Get Length of Month Return
SELECT @.v_Length = DATALENGTH(@.v_Month)
-- Make two digits if needed
SELECT @.v_DisMonth = CASE WHEN @.v_Length = 1
THEN '0'+@.v_Month
ELSE @.v_Month
END
-- See Month Correctly
SELECT @.v_DisMonth -- After Conversion
"SteveInBeloit" wrote:

> Hi,
> I am pulling the month from a date like this:
> Cast(DatePart(m,[bhDate]) as varchar)
> if it comes back 12, I get 12, if it comes back as the 7th month, I want t
o
> get '07'.
> I normally use the FORMAT function in VBA, but having trouble finding how
to
> do it in T-SQL
> Any thoughts,
> Steve
>|||For some reason, Microsoft hasn't implemented a generic string format
function in T-SQL.
To get 2-digit month, you can also use convert() function and let Microsoft
truncates for you.
convert(char(2), getdate(), 101)
"Ravi" <ravishankart@.hotmail.com> wrote in message
news:A68D4075-7FB0-49B9-B598-7CF06485B21C@.microsoft.com...
> Try this REPLICATE('0',2-LEN(Cast(DatePart(m,[bhDate]) as varchar)))+
> Cast(DatePart(m,[bhDate]) as varchar)
> --
> Thanks & Rate the Postings.
> -Ravi-
>
> "SteveInBeloit" wrote:
>