convert year from mm/dd/yyyy to mm/dd/yy

I am working with a chart. I want to display the year on the X axis as mm/dd/yy. I have tried entering yy, etc into the format code field and have had no luck.

Any comments are appreciated.

Reporting Services uses similar formatting commands to Excel. You should be able to use MM/dd/yy.

If that doesn't work, another way would be to create a function that would return the values in a string format and parse the string. Something like:

public function getDate(dt as string) as string

string dt = left(dt,6) & right(dt,2)

return dt

end function

cheers,

Andrew

|||

Thanks! I was able to get this to work

CONVERT(varchar(12), field_name, 10) AS Date

Convert week into date

In my database I am storing a delivery week in format YYYYWW, ie '200245'. Now I want to display this as a date.
I want the first date for the week to display as DD MMM YYYY, ie '04 Nov 2002'.
Does anyone know any simple way to do this?Originally posted by Pedalen
In my database I am storing a delivery week in format YYYYWW, ie '200245'. Now I want to display this as a date.

I want the first date for the week to display as DD MMM YYYY, ie '04 Nov 2002'.

Does anyone know any simple way to do this?

You can use a T-SQL statement such as:
DECLARE @.WK_DTE CHAR(6),
@.YR CHAR(4),
@.WK INT
SET @.WK_DTE = '200245'
SET @.YR = LEFT(@.WK_DTE,4)
SET @.WK = CAST(RIGHT(@.WK_DTE,2) AS INT)
SELECT CONVERT(CHAR(11), DATEADD(WEEK,@.WK,'01/01/' + @.YR), 106)

you could put this into a function or a stored procedure|||You need to subtract 1 week. If you run the datepart function on your result you'll see that it yields week number 46, not 45.

You also need to find the start of the week.

declare @.YEARWEEK char(6)
set @.YEARWEEK = '200346'

declare @.weekstring char(6)
set @.weekstring = '200345'
declare @.weekstart datetime
set @.weekstart = dateadd(ww, cast(Right(@.weekstring, 2) as int)-1, '01/01/' + left(@.weekstring, 4))
set @.weekstart = dateadd(d, 1-datepart(dw, @.weekstart), @.weekstart)

blindman|||Originally posted by blindman
You need to subtract 1 week. If you run the datepart function on your result you'll see that it yields week number 46, not 45.

You also need to find the start of the week.

declare @.YEARWEEK char(6)
set @.YEARWEEK = '200346'

declare @.weekstring char(6)
set @.weekstring = '200345'
declare @.weekstart datetime
set @.weekstart = dateadd(ww, cast(Right(@.weekstring, 2) as int)-1, '01/01/' + left(@.weekstring, 4))
set @.weekstart = dateadd(d, 1-datepart(dw, @.weekstart), @.weekstart)

blindman

Thanks, this helpssqlsql

Convert VARCHAR to money value

Ok i know this is simple but i just don't know the syntax. What I have
is a bunch of values and i want to be able to display them as money
values with a $ in front.
eg.
16000000
1000
160000
TO THIS
$16,000,000.00
$1000.00
$16,000.000
I'm using MS SQL 2000
Cheers
I guess it's not straight forward by just using convert function to change
datatype to money.
You need to write a function which will append $ and will separate thousands
with comma.
Woodies_46@.hotmail.com wrote:
>Ok i know this is simple but i just don't know the syntax. What I have
>is a bunch of values and i want to be able to display them as money
>values with a $ in front.
>eg.
>16000000
>1000
>160000
>TO THIS
>$16,000,000.00
>$1000.00
>$16,000.000
>I'm using MS SQL 2000
>Cheers
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...ivity/200603/1

Convert VARCHAR to money value

Ok i know this is simple but i just don't know the syntax. What I have
is a bunch of values and i want to be able to display them as money
values with a $ in front.
eg.
16000000
1000
160000
TO THIS
$16,000,000.00
$1000.00
$16,000.000
I'm using MS SQL 2000
CheersI guess it's not straight forward by just using convert function to change
datatype to money.
You need to write a function which will append $ and will separate thousands
with comma.
Woodies_46@.hotmail.com wrote:
>Ok i know this is simple but i just don't know the syntax. What I have
>is a bunch of values and i want to be able to display them as money
>values with a $ in front.
>eg.
>16000000
>1000
>160000
>TO THIS
>$16,000,000.00
>$1000.00
>$16,000.000
>I'm using MS SQL 2000
>Cheers
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...tivity/200603/1|||On 7 Mar 2006 20:32:18 -0800, Woodies_46@.hotmail.com wrote:

> Ok i know this is simple but i just don't know the syntax. What I have
> is a bunch of values and i want to be able to display them as money
> values with a $ in front.
> eg.
> 16000000
> 1000
> 160000
> TO THIS
> $16,000,000.00
> $1000.00
> $16,000.000
>
> I'm using MS SQL 2000
> Cheers
Ideally, this formatting would be done in your front-end application (or
reporting tool), rather than being formatted by SQL.

Convert VARCHAR into Money Value

Ok i know this is simple but i just don't know the syntax. What I have
is a bunch of values and i want to be able to display them as money
values with a $ in front.

eg.

16000000
1000
160000

TO THIS

$16,000,000.00
$1000.00
$16,000.000

I'm using MS SQL 2000

Cheers(Woodies_46@.hotmail.com) writes:
> Ok i know this is simple but i just don't know the syntax. What I have
> is a bunch of values and i want to be able to display them as money
> values with a $ in front.
> 16000000
> 1000
> 160000
> TO THIS
> $16,000,000.00
> $1000.00
> $16,000.000
>
> I'm using MS SQL 2000

Normally, formatting should be done client-side. Not the least because
the format is dependent on regional settings. The above is not how I
want my decimal commas and thousands separator.

Nevertheless, you can do this:

declare @.x money
select @.x = 9009090909
select '$' + convert(varchar, @.x, 1)

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Convert UTC time to local time

Hello,

I am new with the reporting services. I am creating a report and I need to display date/time on the report. But the servers stores those date/time in UTC. How can I convert them to the local time in my report.

Thanks for your help.

You need to use System.TimeZone.ToLocalTime(UTCTime).

See http://msdn2.microsoft.com/en-us/library/system.timezone.tolocaltime.aspx.

You should be able to use TimeZone.CurrentTimeZone if you want to convert using the server time zone.

|||

Hello,

i tried this tip with no luck.

I used the expression = System.TimeZone.ToLocalTime(!Fields.DateTime.Value) in one of my cells and got an BC30469 error.

|||

Try this instead:
=System.TimeZone.CurrentTimeZone.ToLocalTime(Fields!DateTime.Value)

-- Robert

|||

Robert,

thanks alot. Works like a charm.

|||

This works for conversion based on the time zone of the report server, but not the client. Is that correct? I tested this by using the function in a textbox on the report that I deployed to the report server. Then on my workstation PC, I changed my timezone and viewed the report. The time in the report still reflected the time on the report server (converted from the UTC time of course).

How do you change the dates in the reports dynamically based on the area of the country someone opens the report? Because the report renders as HTML first before being delivered to the client, does this mean it will always use the report server time zone?

Thanks

-Kory

|||I have the same question as KoryS. Anyone have an answer?

Thanks.

Convert UTC time to local time

Hello,

I am new with the reporting services. I am creating a report and I need to display date/time on the report. But the servers stores those date/time in UTC. How can I convert them to the local time in my report.

Thanks for your help.

You need to use System.TimeZone.ToLocalTime(UTCTime).

See http://msdn2.microsoft.com/en-us/library/system.timezone.tolocaltime.aspx.

You should be able to use TimeZone.CurrentTimeZone if you want to convert using the server time zone.

|||

Hello,

i tried this tip with no luck.

I used the expression = System.TimeZone.ToLocalTime(!Fields.DateTime.Value) in one of my cells and got an BC30469 error.

|||

Try this instead:
=System.TimeZone.CurrentTimeZone.ToLocalTime(Fields!DateTime.Value)

-- Robert

|||

Robert,

thanks alot. Works like a charm.

|||

This works for conversion based on the time zone of the report server, but not the client. Is that correct? I tested this by using the function in a textbox on the report that I deployed to the report server. Then on my workstation PC, I changed my timezone and viewed the report. The time in the report still reflected the time on the report server (converted from the UTC time of course).

How do you change the dates in the reports dynamically based on the area of the country someone opens the report? Because the report renders as HTML first before being delivered to the client, does this mean it will always use the report server time zone?

Thanks

-Kory

Convert String to Numeric without Decimal

Hi,

I am using Crystal Report 8.5 with Visual Basic 6.0 and MS Access database. Now situation is I have to display three different values for a particular condition. If data is there then number of records else zero and if even no depedend entry is there then "NA".

Variable is numeric and formula is

if {temp.var1} = 0 and {temp.var11} = 0 then 'NA'
else if {temp.var11} = 1 then '0'
else CStr ({temp.var1})

NOW problem is if I am using this report on a system having crystal report installed and set the numeric value as no decimal, it is working fine but when I try the same program on the system on which crystal is not installed it is showing decimal values upto 2 digits. I have used Report Expert Distribution also.

Any idea how to solve this problem. Thanks

VishalFormat that format field number as you want|||What does it mean. Could you please help me out by writing a example code for that.

thanks|||Roght click the field, select format section then goto number and select the required format

Convert number into Chinese Text

Please help to advoce how to convert a number into text (Chinese characters). For example, to display $2880.85 into:
貳仟捌佰捌拾元捌拾伍仙
Thankshow about making out a formular like

if = 2 then...?

other wise is quite hard... due to chinese have to enter the 佰捌, 元 and 仙...
thats difficult|||I can create a mapping but need to identify the correct location of the digit first. In this example, how do I separate the 2 from the entire figure so that it is equal to 2000 first, and the 1st 8 is 800, and so forth for the rest of the digits? What is the formula to do this identification?

If I can tick out the correct place of the digits, then I can map it to the correct translation, say if X's dec. place is in thousand, then translate 2 to Chinese text + the chinese thousand sign...

Pls help to define the formula to tick out the digits' dec. place...Thanks

Convert Integer To Time Only In SELECT

I have stored, as seconds, a duration in a table. I would like to use a SELECT statement to retrieve the contents of the column but display them as a time. For example:

45 = 0:00:45
241 = 0:04:01
575 = 0:09:35

and so on...

I have tried using the built-in CONVERT() function but always end up with StartDate + Integer and a time of 0:00:00.

Can anyone help please?

Cheers,

Roy

You can do:

declare @.seconds int
set @.seconds = 241
select convert(char(8), dateadd(second, @.seconds, ''), 114)

|||Thanks for this.

I took your example and turned it in to this which works:

SELECT CONVERT(char(8), DATEADD(second, Duration, ''), 114) AS Duration ...

Cheers,

Roy

Convert function

In a query, how do I convert a datetime to display as mm/dd/yy hh:mm AMorPM?
I don't see that option on the list of values in Sql Books. The only option
I find for including the time abbreviates the month rather than using
numericals:
Convert(varchar, myDateTime, 9) displays: Jun 17 1998 12:00:00:000AM
where as I want it to display 06/17/98 12:00 AM
Same for using numbers that you want it to always display decimals.
Convert(varchar, myNumber, 1) displays 7.1750000e+002
whereas I want it to display 7.17, or I want 7.014 to display as 7.01 and I
want 7.1 to display as 7.10convert(VarChar(10), myDateTime, 1) + ' ' +
Right(convert(VarChar(26), myDateTime, 9), 14)
And for Numbers, Check out the Str() FUnction Takes 3 Arguments,
Str(val, N, M)
val is expression evaluates to a number (integral or floating)
N Is Number of digits to Display,
M is number of digits to right of decimal point to round off to...
"Tory" wrote:

> In a query, how do I convert a datetime to display as mm/dd/yy hh:mm AMorP
M?
> I don't see that option on the list of values in Sql Books. The only opti
on
> I find for including the time abbreviates the month rather than using
> numericals:
> Convert(varchar, myDateTime, 9) displays: Jun 17 1998 12:00:00:000AM
> where as I want it to display 06/17/98 12:00 AM
> Same for using numbers that you want it to always display decimals.
> Convert(varchar, myNumber, 1) displays 7.1750000e+002
> whereas I want it to display 7.17, or I want 7.014 to display as 7.01 and
I
> want 7.1 to display as 7.10
>
>|||The convert for the date works great! For the number, however, I don't know
the "N" as it may be 3 characters (123) or it may be 5 characters (1234.5).
Also I want the number to always display with 2 digits to the right, for
instance 123 would be returned as 123.00
Thanks.
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:F0CE3A77-FF02-40D6-AC58-B186D897B38A@.microsoft.com...
> convert(VarChar(10), myDateTime, 1) + ' ' +
> Right(convert(VarChar(26), myDateTime, 9), 14)
> And for Numbers, Check out the Str() FUnction Takes 3 Arguments,
> Str(val, N, M)
> val is expression evaluates to a number (integral or floating)
> N Is Number of digits to Display,
> M is number of digits to right of decimal point to round off to...
> "Tory" wrote:
>
AMorPM?
option
and I|||The N Just has to be big "enough" If you are outputing an integer, for
example, it can only go to 2 Billion, so N = 9 + hpwever many decimal places
will suffice... Str Fucntion will add extra spaces up to the value of N
Try this:
Select Str(123.5678912345, 12, 4) to see what I mean...
If you don't want the extra spaces, Use Ltrim() Function to strip them off..
Select Str(123.5678912345, 12, 4), LTrim(Str(123.5678912345, 12, 4))
"Tory" wrote:

> The convert for the date works great! For the number, however, I don't kn
ow
> the "N" as it may be 3 characters (123) or it may be 5 characters (1234.5)
.
> Also I want the number to always display with 2 digits to the right, for
> instance 123 would be returned as 123.00
> 8
> Thanks.
>
> "CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
> news:F0CE3A77-FF02-40D6-AC58-B186D897B38A@.microsoft.com...
> AMorPM?
> option
> and I
>
>|||While you have that "code museum" proprietary CONVERT (), the basic
principle of a tiered architecture is that you do frmatting and display
in the front end and never in the database. Standard SQL has one and
only one date format, based on ISO-8601. Each X3J host language
Standard has conversion rules for SQL data types.
You are still thinking in terms of a 3GL, and not SQL. In a 3GL, the
files and the apps were integrated, monolithic. In a tiered
architecture, each tier does one and only one task.|||Got it! Thanks so much, that works perfectly!
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:AA1384B2-F109-44C3-8F32-B7D7A8DDBFAA@.microsoft.com...
> The N Just has to be big "enough" If you are outputing an integer, for
> example, it can only go to 2 Billion, so N = 9 + hpwever many decimal
places
> will suffice... Str Fucntion will add extra spaces up to the value of N
> Try this:
> Select Str(123.5678912345, 12, 4) to see what I mean...
> If you don't want the extra spaces, Use Ltrim() Function to strip them
off..
> Select Str(123.5678912345, 12, 4), LTrim(Str(123.5678912345, 12, 4))
> "Tory" wrote:
>
know
(1234.5).
only
using
12:00:00:000AM
7.01