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

Formatting Query Data

When I run queries against tables in MSSQL2000 it returns dates with a time component and has way too many zeros after the decimal point.

Is there any functions or something I can put in the query so my data comes back with only a date for the date and only two decimal points for the numbers?You are generally best off leaving the formatting to the code that receives the data from the database. But you can use the Convert function to format a date and the round function for numeric values.

SELECT Convert(varchar,DateField,101),Round(NumericField,2)
FROM YourTable

However, Round may not work quite as you expect. It will round the value but not necessarily format it for display as you might expect. Other date formats are available, check Sql Server Books OnLine under the convert function for the options.|||I agree with McMurdo, however, that the best place to handle this is outside of the database, in your code that is displaying the data.|||I agree, this is what I wanted - the ability to have the data formatted correctly from SQL. I tried for ages to find a way to do that on the basis of what I new about MySQL (which has a lot of functions for this).

But I'll look into it, as ideally I want my data coming in right from the SQL Query, rather than .Net doing it. Indeed, there is another thread in another forum arguing that as people kept giving me the whole Eval solution.|||Can you strip trailing spaces as well - I have a CUSTNMBR that is 5 characters long put it pads it with four spaces to to make it 9 (the length of the field).

I'll investigate the day, so I may have the answer this morning (UK Time).|||Use RTRIM or LTRIM to strip spaces.sql

Formatting Parameter and Field Dates

I'm new to Reporting Services so bear w/ me,

I'm having troubles with formatting the parameter and field dates. To resolve the Field dates I just formmated them in the query, but I still haven't found a way for the Parameter fields. The date parameters is passed as YYYYMMDD(oracle number data type), and I want to format and display it as MM/DD/YYYY.

Any help would be appreciated,

also would like how to know how this works for the field values as well,

Thanks,
Nick

If you want to change the format of the parameter (assuming that your referring to a date parameter) you can use this one:

=Format(Now(), "MM/dd/yyyy")

Click on the "Tools" then "Report Parameters". Under the Default Values, click on the Non-Queried option then place on the text field the formula above.

Hope this helps..

|||

Thanks for the reply. I'm not sure if I did it the best way, but I ended up using

="From Date: " + Parameters!FromDate.Value.substring(4,2) + "/" + Parameters!FromDate.Value.substring(6,2) + "/" + Parameters!FromDate.Value.substring(0, 4).

FOrmatting Paramater Dates

More Begin and End dates, I have the following
=Parameters!BEG.Value &" through "& Parameters!END.Value
Its giving me date + time. How can I format this to remove the time?Try this one
=FormatDateTime(Parameters!BEG.Value,2) & " through " &
FormatDateTime(Parameters!BEG.Value,2)
--
Do you really need to print that email?
"BLAW" wrote:
> More Begin and End dates, I have the following
> =&" through "& Parameters!END.Value
> Its giving me date + time. How can I format this to remove the time?
>|||that did it, thanks!

formatting for a birth date field?

Hello, I'm new to SQL Server, working for a non-profit computerizing a
lot of its data.

I imported a table of people's names, birth dates, etc. into SS2005
from Access, and the birth_date was imported as an Access date/time
field, giving it the datetime datatype in SQL.

The column values look like:

10/14/1964 12:00:00 AM

Where and how do I learn to specify that all fields like this should be
in 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 have
to be stored properly in the database table as column/field
birth_date...

Thank you, Tomtlyczko wrote:

Quote:

Originally Posted by

Hello, I'm new to SQL Server, working for a non-profit computerizing a
lot of its data.
>
I imported a table of people's names, birth dates, etc. into SS2005
from Access, and the birth_date was imported as an Access date/time
field, giving it the datetime datatype in SQL.
>
The column values look like:
>
10/14/1964 12:00:00 AM
>
Where and how do I learn to specify that all fields like this should be
in 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 have
to be stored properly in the database table as column/field
birth_date...
>
Thank you, Tom

A DATETIME column doesn't have any specific format. SQL Server cannot
control the format of dates as displayed by your client application.
For that you have to use the features of your client app or development
environment.Typically these might be based on the regional format
defined in Windows Control Panel.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||On 16 Sep 2006 06:12:07 -0700, tlyczko wrote:

Quote:

Originally Posted by

>Hello, I'm new to SQL Server, working for a non-profit computerizing a
>lot of its data.

(snip)

Quote:

Originally Posted by

>Where and how do I learn to specify that all fields like this should be
>in ISO format of yyyy-mm-dd??

(snip)

Quote:

Originally Posted by

>The end users will likely enter mm/dd/yy or mm/dd/yyyy and it will have
>to be stored properly in the database table as column/field
>birth_date...

Hi Tom,

Read Tibor Karaszi's article "The ultimate guide to the datetime
datatypes", and you'll know everything you need to know for safely using
datetimes in SQL Server databases:

http://www.karaszi.com/SQLServer/info_datetime.asp
--
Hugo Kornelis, SQL Server MVP|||David Portas wrote:

Quote:

Originally Posted by

A DATETIME column doesn't have any specific format. SQL Server cannot
control the format of dates as displayed by your client application.
For that you have to use the features of your client app or development
environment.Typically these might be based on the regional format
defined in Windows Control Panel.
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

I was looking at the table itself through SSMS 2005, I did specify
above which server I was working in, there were no error messages, it
was an import using the wizard, but I'll remember the create/insert
etc. items next time.

Thank you, Tom|||Hugo Kornelis wrote:

Quote:

Originally Posted by

Read Tibor Karaszi's article "The ultimate guide to the datetime
datatypes", and you'll know everything you need to know for safely using
datetimes in SQL Server databases:
http://www.karaszi.com/SQLServer/info_datetime.asp

Thank you, I shall...I was looking at the SQL Server table itself in
SSMS 2005, and the above post mentions how SQL stores/displays data,
maybe that's where I'm getting confused, smalldatetime will work for
me, I am only concerned with dates and *maybe* time to the nearest
minute.

Thank you, Tom|||tlyczko (tlyczko@.gmail.com) writes:

Quote:

Originally Posted by

I was looking at the table itself through SSMS 2005,

I don't think so. I think you looked at a textual representation of the
table, as presented by SSMS.

I believe that when you run a SELECT query, you always get ISO format,
but in Open Table regional settings are applied. I cannot really tell
for sure, since my regional settings agree with the ISO format.

--
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|||Erland Sommarskog wrote:

Quote:

Originally Posted by

tlyczko (tlyczko@.gmail.com) writes:

Quote:

Originally Posted by

I was looking at the table itself through SSMS 2005,

>
I don't think so. I think you looked at a textual representation of the
table, as presented by SSMS.
I believe that when you run a SELECT query, you always get ISO format,
but in Open Table regional settings are applied. I cannot really tell
for sure, since my regional settings agree with the ISO format.

Hello, both ideas make sense, however, I now just need to learn how to
update all the fields such that the TIME part for each datum is
00:00:00.etc., I don't need to worry about the time in a birth date
field or anything similar...I'll do this as a separate post.

Thank you, Tom|||tlyczko (tlyczko@.gmail.com) writes:

Quote:

Originally Posted by

Hello, both ideas make sense, however, I now just need to learn how to
update all the fields such that the TIME part for each datum is
00:00:00.etc., I don't need to worry about the time in a birth date
field or anything similar...I'll do this as a separate post.

CONSTRAINT dateonly CHECK (convert(char(8), birthdate, 112) = birthdate)

--
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|||Erland Sommarskog wrote:

Quote:

Originally Posted by

tlyczko (tlyczko@.gmail.com) writes:

Quote:

Originally Posted by

Hello, both ideas make sense, however, I now just need to learn how to
update all the fields such that the TIME part for each datum is
00:00:00.etc., I don't need to worry about the time in a birth date
field or anything similar...I'll do this as a separate post.

>
CONSTRAINT dateonly CHECK (convert(char(8), birthdate, 112) = birthdate)

Hello Erland,
Thank you for taking time to reply...you'll get a lot of stars from
me!! :) :)
Now I know what to read about to begin understanding your statement
above, I'll also add that constraint and check to the field itself (and
other fields too).
Thank you, Tom|||Hi Erland,

I did a quick benchmarking. I always knew that DATEDIFF approach should
be faster than converting to CHAR, but I had no idea it is that faster:

--CONSTRAINT dateonly CHECK (convert(char(8), birthdate, 112) =
birthdate)

DECLARE @.d1 DATETIME, @.d2 DATETIME, @.i INT, @.cnt INT
DECLARE @.d TABLE(ddd DATETIME)
SET NOCOUNT ON

SET @.i = 0
WHILE @.i<100000 BEGIN
INSERT @.d VALUES('20060101')
SET @.i = @.i + 1
END
SET @.d1 = GETDATE()
SET @.i = (SELECT COUNT(*) FROM @.d WHERE (convert(char(8), ddd, 112) =
ddd))
SET @.d2 = GETDATE()
SELECT DATEDIFF(ms, @.d1, @.d2), 'char'

SET @.d1 = GETDATE()
SET @.i = (SELECT COUNT(*) FROM @.d WHERE
(dateadd(d,datediff(d,'1990-01-01',ddd),'1990-01-01') = ddd))
SET @.d2 = GETDATE()
SELECT DATEDIFF(ms, @.d1, @.d2), 'datediff'

-----------------

---- --
346 char

---- ---
46 datediff|||Alexander Kuznetsov (AK_TIREDOFSPAM@.hotmail.COM) writes:

Quote:

Originally Posted by

I did a quick benchmarking. I always knew that DATEDIFF approach should
be faster than converting to CHAR, but I had no idea it is that faster:

Yeah, I know. Other people has been suggesting that as well. I just keep
looking it and saying to myself "what on earth does that do?".

--
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|||On Wed, 20 Sep 2006 07:00:14 +0000 (UTC), Erland Sommarskog wrote:

Quote:

Originally Posted by

>I just keep
>looking it and saying to myself "what on earth does that do?".

Hi Erland,

It's actually quite simple. It counts the number of days between a
randomly chosen pivot date and the input date, then counts that number
of days from the pivot date to arrive back at the input date.

A: "Hey, do you know how many days have passed since Jan 1st?"
B: "That would be 263."
A: "Okay. Next question: what date is 263 days after Jan 1st?"
B: "Hey, stupid, that would be today, of course. Sept 21. Couldn't you
just have asked what day it is instead of these silly calculations?"
A: "Could have, but knowing you, you would have told me the time as
well. I wanted just the date."

--
Hugo Kornelis, SQL Server MVP

Formatting dates using regional settings

When presenting a date in a report, I want Reporting Services to use the
regional settings on the client to determine this. Is this possible. The
following post from Charles Kangai on 8/12/2004 said that this was possible.
However when I follow his instructions, Reporting Services still presents
the date in the US format (presumably using the "Language" property of the
object, set to "default" in my report) whereas my regional settings are set
to UK date format (both short and long date).Thanks for that.
The report level language property was set to US which was why it wasn't
working.
"Charles Kangai" <CharlesKangai@.discussions.microsoft.com> wrote in message
news:9609E7E7-5091-4631-BE18-CE0C4B9B2F50@.microsoft.com...
> Hi,
> Make sure the Report's Language property and the textbox's Language
property
> are both set to Default. Then set the Format property of your textbox to
"d"
> (Short Date) or "D" (Long Date), without the double quotes. It should
display
> according to the setting in your Regional Settings. I have just tried it
> again after reading your post, and it works.
> Charles Kangai, MCDBA, MCT
> "GML" wrote:
> > When presenting a date in a report, I want Reporting Services to use the
> > regional settings on the client to determine this. Is this possible.
The
> > following post from Charles Kangai on 8/12/2004 said that this was
possible.
> > However when I follow his instructions, Reporting Services still
presents
> > the date in the US format (presumably using the "Language" property of
the
> > object, set to "default" in my report) whereas my regional settings are
set
> > to UK date format (both short and long date).
> >
> >
> >

Formatting dates on a chart when sourced from SSAS2k5

I am creating a few charts in SSRS that are sourced from a cube in AS. On the x-axis is time, which is what I assume a date-like field in the cube. On the y-axis is the measure.

My problem is that I cannot seem to get the dates to format on the chart - regardless of the format code I use, the dates appear in long format, like "Thursday, November 23 2006".

Are the dates from SSAS just string values?

I feel that you may have mentioned some expression or modification done on that field.I faced this problem before where i came to know that Format expression which will be like (#000# Something...) when some operation is being performed on that date. Take out that any operation on that field and give only format option or use format function in the expression.

Hope it should work

Regards,

Raj Deep.A

|||Hi Raj,

I'm not sure I follow - I haven't modified the field at all, yet. I merely created an MDX Query using the designer, and Time is one of the dimensions I used to splice my measure by. I wanted to expose this data in a chart, but for some reason I can't apply any standard formatting codes to the Time label on the x-axis.|||

Based on your query in the MDX query generator, the date time value is provided as a string to Reporting Services. That's why format codes won't work.

You could look into either changing your MDX query to get the value as DateTime object (which means you have to hand-write the MDX) or you could try to convert the string back into a DateTime object in the chart category grouping expression by using an expression similar to this: =CDate(Fields!DateValue.Value)

But depending on the actual strings, the CDate() function may fail to convert and you may need to look into using a combination of several VB runtime conversion functions / DateTime functions to achieve the conversion.

-- Robert

|||That's what I feared...thanks for the confirmation. Hopefully something to look forward to in Katmai? [better integration between SSAS and SSRS, that is]|||

Another aproach is to assing a value to your attribute members and use the MemberValue MDX function.

1. In your cube, assign the Value property of the Date attribute hierarchy to a Date column in the underlying table.

2. Unfortunately, the RS SSAS data provider doesn't currently surface the MemberValue function so you need to create a calculated member, e.g. NativeDate with the following expression [Date].[Date].MemberValue.

3. Now you can format the calculated member as you wish, e.g. Format(Fields!NativeDate.Value, "MMM/dd/

|||How does this work with server time dimensions?|||Sorry, it won't b/c you won't be able to assign a value column to a server time dimension.

Formatting dates on a chart when sourced from SSAS2k5

I am creating a few charts in SSRS that are sourced from a cube in AS. On the x-axis is time, which is what I assume a date-like field in the cube. On the y-axis is the measure.

My problem is that I cannot seem to get the dates to format on the chart - regardless of the format code I use, the dates appear in long format, like "Thursday, November 23 2006".

Are the dates from SSAS just string values?

I feel that you may have mentioned some expression or modification done on that field.I faced this problem before where i came to know that Format expression which will be like (#000# Something...) when some operation is being performed on that date. Take out that any operation on that field and give only format option or use format function in the expression.

Hope it should work

Regards,

Raj Deep.A

|||Hi Raj,

I'm not sure I follow - I haven't modified the field at all, yet. I merely created an MDX Query using the designer, and Time is one of the dimensions I used to splice my measure by. I wanted to expose this data in a chart, but for some reason I can't apply any standard formatting codes to the Time label on the x-axis.|||

Based on your query in the MDX query generator, the date time value is provided as a string to Reporting Services. That's why format codes won't work.

You could look into either changing your MDX query to get the value as DateTime object (which means you have to hand-write the MDX) or you could try to convert the string back into a DateTime object in the chart category grouping expression by using an expression similar to this: =CDate(Fields!DateValue.Value)

But depending on the actual strings, the CDate() function may fail to convert and you may need to look into using a combination of several VB runtime conversion functions / DateTime functions to achieve the conversion.

-- Robert

|||That's what I feared...thanks for the confirmation. Hopefully something to look forward to in Katmai? [better integration between SSAS and SSRS, that is]|||

Another aproach is to assing a value to your attribute members and use the MemberValue MDX function.

1. In your cube, assign the Value property of the Date attribute hierarchy to a Date column in the underlying table.

2. Unfortunately, the RS SSAS data provider doesn't currently surface the MemberValue function so you need to create a calculated member, e.g. NativeDate with the following expression [Date].[Date].MemberValue.

3. Now you can format the calculated member as you wish, e.g. Format(Fields!NativeDate.Value, "MMM/dd/

|||How does this work with server time dimensions?|||Sorry, it won't b/c you won't be able to assign a value column to a server time dimension.

Formatting Dates as YYYY/MM/DD for a particular query

Anyone know how to format dates from 10/3/05 to 2005/10/03 in a query?
The only way I know is (cast(year(srecordeddate) as char(4)))+ cast('/'as char) + cast (month(srecordeddate) as char(2))... Which seems so rediculous. There's gotta be a better way!select convert(varchar(10), getdate(), 111)|||Anyone know how to format dates from 10/3/05 to 2005/10/03 in a query?

The only way I know is (cast(year(srecordeddate) as char(4)))+ cast('/'as char) + cast (month(srecordeddate) as char(2))... Which seems so rediculous. There's gotta be a better way!

With the caveat that it's almost ALWAYS better to handle formatting on the client side, you might try:

Convert(varchar(10), [YourDate], 120)

Regards,

hmscott|||select convert(varchar(10), getdate(), 111)
Thanks! You've bailed me out of another toughee...

formatting dates

Hi, I was wondering if there is a way to output a special format for dates in SELECT statements. Currently, my date field is returning my date in this format:
2004-01-19 00:00:00.000
but I want it to be like this:
1/19/04 or 1/19/2004
Is there a way to do this?
Thanks.Try this:

SELECT CONVERT(char(10),getdate(),103)|||Please.. use the 101 code for the date format..

like this:

SELECT CONVERT(char(10),getdate(),101)

the other example is another format.

You can consult all the date format in the SQL SERVER BOOKS ON LINE!.|||But doesn't that just return the current date?
Here's my SQL statement:

SELECT id, FirstName, LastName, TerrNumber, IsCheckedOut, IsLPCheckedOut, FirstNameLP, LastNameLP, checkedOutDate, ReturnedDate FROM Checkouts";

checkedOutDate and ReturnedDate are the fields I want to format. How would this work with getdate()?|||Just replace the getdate() with checkedOutDate. Do the same again with ReturnedDate (a separate query).

Originally posted by domiflichi
But doesn't that just return the current date?
Here's my SQL statement:

SELECT id, FirstName, LastName, TerrNumber, IsCheckedOut, IsLPCheckedOut, FirstNameLP, LastNameLP, checkedOutDate, ReturnedDate FROM Checkouts";

checkedOutDate and ReturnedDate are the fields I want to format. How would this work with getdate()?|||Look at my post from yesterday. I had the same question.
exdter|||That's what I thought you're supposed to do. But when I do that...here's my new SQL statement:

SELECT id, FirstName, LastName, TerrNumber, IsCheckedOut, IsLPCheckedOut, FirstNameLP, LastNameLP, CONVERT(char(10),checkedOutDate,101), ReturnedDate FROM Checkouts

I get an error when trying to display my results of the query when encountering that field:

Warning: odbc_result(): Field checkedoutdate not found in d:\web\territories\index.php on line 173

If it helps: my webserver is Win2k Pro running Apache and PHP, and my SQL server is on Win2K server and it's actually MSDE 2000.|||Originally posted by exdter
Look at my post from yesterday. I had the same question.
exdter

I'm getting the same error message on my query:

Warning: odbc_result(): Field checkedoutdate not found in d:\web\territories\index.php on line 174

Here's the SQL statement:

SELECT id, FirstName, LastName, TerrNumber, IsCheckedOut, IsLPCheckedOut, FirstNameLP, LastNameLP, CONVERT(char(10),CONVERT(datetime, CAST(checkedOutDate as varchar(12))),103), ReturnedDate FROM Checkouts

Why isn't this working for me?
Thanks,
domiflichi|||Try this

SELECT id, FirstName, LastName, TerrNumber, IsCheckedOut, IsLPCheckedOut, FirstNameLP, LastNameLP
, CONVERT(char(10),CONVERT(datetime, CAST(checkedOutDate as varchar(12))),103) AS checkedOutDate, ReturnedDate FROM Checkouts|||Thank you Brett!...it worked! And thank you to everybody else for getting me through each step, and being patient with this newbie. And thank you all for such quick replies!

Formatting Dates

Hi all,

Soryy for the question because of its simplicity but i'm newbie.

I have a date column in the following format:

2002-04-01 15:16:32.187

I want to query and show this column in the format:

dd/mm/yyyy

How can I do it in a simple query?. I don't want to use cursors or user defined functions.

Thanks in advance.

God Blessselect convert(varchar(10),getdate(),103)

Formatting Dates

I 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

How did you do the import? What is the datatype you currently have for the column?|||It's a varchar now. I tried to convert to a date, but there must be fields somewhere with bad data because it won't let me. The problem is though that this table has over 6 million rows so it's not easy to figure out where the problem is.|||

Your value of 050729 is not clear as to which part of it is month and which part of it is day.

A quick SELECT CONVER(Datetime, '050729') returns 2005-07-2900:00:00.000.Not sure if that is what you want?

|||

The select you showed me is what I was looking for. Could I then do something like this?

update RetailSalesConvert(DateTime,InvoiceDate)

|||

First, is InvoiceDate varchar? Then it makes no sense. You could add another column with Datetime data type (Set it to null initially). Update that row with the converted value from InvoiceDate, then drop the existing column, rename the new column to old.

Here's a series of steps to follow:

--Add a new columnALTER Table RetailSalesADD COLUMN InvoiceDate2DatetimeNULLGo--Update the column with the values converted to datetimeupdate RetailSalesSet InvoiceDate2 =Convert(DateTime,InvoiceDate)GO--Drop the existing InvoiceDateALTER TABLe RetailSalesDROP COLUMN InvoiceDateGo--Rename the new column to InvoiceDatesp_Rename'InvoiceDate2','InvoiceDate'

Formatting Dates

Why would this not format a date into the dd/mm/yy format ?
CONVERT(datetime, Actioned,103) As Actioned
Actioned is a column of type datetime.
Thanks in advance...Because you're converting it to datetime, not a string. ;)
"McHenry" wrote:

> Why would this not format a date into the dd/mm/yy format ?
> CONVERT(datetime, Actioned,103) As Actioned
>
> Actioned is a column of type datetime.
>
> Thanks in advance...
>
>|||Probably because the ONLY -- repeat for those who never learned
Standard SQL-- the **ONLY** format is based on ISO-8601 (should explain
ISO?).
Duh!
It is also the only one in the rest of the ISO Standards. But you did
your research, before you posted, right?
You are one of the kids I want to hit with a stick!! You think that
your local "hillbilly dialect" is law of the universe.
Why are you formatting data in the back end? The basic principle of a
tiered architecture is that display is done in the front end and never
in the back end. This a more basic programming principle than just SQL
and RDBMS.
Violate ISO standard in your applications and not in the database. And
comment your errors, so that a better programmer can find a correct
after you are fired.|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1145329781.514897.34950@.v46g2000cwv.googlegroups.com...
> Probably because the ONLY -- repeat for those who never learned
> Standard SQL-- the **ONLY** format is based on ISO-8601 (should explain
> ISO?).
>
Joe it's been a while since I've had the pleasure of one of your opinionated
condescending responses, great to hear from you again and thanks for the
input. Last time I had the pleasure of your whine was when you helped with
nested sets, an excellent solution I might add.

> Duh!
> It is also the only one in the rest of the ISO Standards. But you did
> your research, before you posted, right?
> You are one of the kids I want to hit with a stick!! You think that
> your local "hillbilly dialect" is law of the universe.
Joe if hitting kids with sticks is a fantasy I am sure there are
professionals that can help, were you spanked roughly as a child ?

> Why are you formatting data in the back end? The basic principle of a
> tiered architecture is that display is done in the front end and never
> in the back end. This a more basic programming principle than just SQL
> and RDBMS.
I would have done my formatting in the frond end application however the
rows returned are being displayed in an MS Access list box that does not
allow for formatting and therefor I must format the information at the
server end, not to confuse you with the facts Joe as it would interrupt the
constant flap of your jaw

> Violate ISO standard in your applications and not in the database. And
> comment your errors, so that a better programmer can find a correct
> after you are fired.
>
Personal experience ?|||"Rob Farley" <RobFarley@.discussions.microsoft.com> wrote in message
news:8C104A08-E6B9-4B10-BC20-805041F19702@.microsoft.com...
> Because you're converting it to datetime, not a string. ;)
> "McHenry" wrote:
>
Thanks Rob, interestingly I have two scenarios where I have made the same
mistake, one formatted the dates as desired however this one didn't...|||"Rob Farley" <RobFarley@.discussions.microsoft.com> wrote in message
news:8C104A08-E6B9-4B10-BC20-805041F19702@.microsoft.com...
> Because you're converting it to datetime, not a string. ;)
> "McHenry" wrote:
>
Rob, so formatting to 103 should be to type char(8) ?|||>> Joe it's been a while since I've had the pleasure of one of your opiniona
ted
condescending responses, great to hear from you again and thanks for
the
input. Last time I had the pleasure of your whine was when you helped
with
nested sets, an excellent solution I might add. <<
Thank you, Grasshopper :) Oh, my wife should become a Zen Monk this
year. Then she can beat you with a stick :)
Out of the "zen mode"; have you found a good enumerated path set model
to Nested Sets model algorithm? All I have is a enumerated path to
adjacency list to nested sets program. The overhead is bad.|||"McHenry" <mchenry@.mchenry.com> wrote in message
news:44445f25$0$16677$5a62ac22@.per-qv1-newsreader-01.iinet.net.au...
> "Rob Farley" <RobFarley@.discussions.microsoft.com> wrote in message
> news:8C104A08-E6B9-4B10-BC20-805041F19702@.microsoft.com...
> Rob, so formatting to 103 should be to type char(8) ?
>
Oops meant CHAR(10)|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1145331802.606758.250160@.u72g2000cwu.googlegroups.com...
> condescending responses, great to hear from you again and thanks for
> the
> input. Last time I had the pleasure of your whine was when you helped
> with
> nested sets, an excellent solution I might add. <<
> Thank you, Grasshopper :) Oh, my wife should become a Zen Monk this
> year. Then she can beat you with a stick :)
Reading between the lines Joe I think the beating with sticks thing goes
back long before you even started reasearching Zen...

> Out of the "zen mode"; have you found a good enumerated path set model
> to Nested Sets model algorithm? All I have is a enumerated path to
> adjacency list to nested sets program. The overhead is bad.
>
It was quite a while ago and the scenario was a component breakdown into
parts and sub parts, your solution worked perfectly and is very impressive,
once I got my head around it. I did try at a later time to adapt it to a
genealogy application using two parallel nested sets however it simply got
too complicated and as it was a private project I put it to bed...
Thanks again for your help and I often think of your opinionated tones and
smile when I'm photocopying sections from one of your books :)|||CONVERT(carchar(10), Actioned,103) As Actioned
Is it right?
"McHenry"?? ??? ??:

> Why would this not format a date into the dd/mm/yy format ?
> CONVERT(datetime, Actioned,103) As Actioned
>
> Actioned is a column of type datetime.
>
> Thanks in advance...
>
>

Formatting Dates

I have a Text box containing the following info:
="Effective: " & Parameters!PMStartDate.Value & " to "
& Parameters!PMEndDate.Value
How do I control the format of the date? I want the Format to be dd/MMM/yyyy.You will need to use Format(). See
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vafctFormat.asp
for more information.
Below is a sample expression based on your parameter names:
="Effective: " & Format(Parameters!PMStartDate.Value, "Long Date") & "
to " & Format(Parameters!PMEndDate.Value, "Short Date")
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"SAcanuck" <SAcanuck@.discussions.microsoft.com> wrote in message
news:9A6A54AE-1EE6-4A94-9A5C-244E77DD42A8@.microsoft.com...
> I have a Text box containing the following info:
> ="Effective: " & Parameters!PMStartDate.Value & " to "
> & Parameters!PMEndDate.Value
> How do I control the format of the date? I want the Format to be
dd/MMM/yyyy.|||Try this:
1. Pull up the report properties dialog, go to the code tab, and paste the
following into the code textbox:
public shared Function Convert(dt As datetime) As String
return dt.ToString("dd/MM/yyyy")
End Function
2. Paste the following into the textbox in question: ="Effective: " &
Code.Convert(Parameters!PMStartDate.Value) & " to " &
Code.Convert(Parameters!PMEndDate.Value)
The .NET datetime format strings are explained in detail in
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconcustomdatetimeformatstrings.asp?frame=true.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"SAcanuck" <SAcanuck@.discussions.microsoft.com> wrote in message
news:9A6A54AE-1EE6-4A94-9A5C-244E77DD42A8@.microsoft.com...
> I have a Text box containing the following info:
> ="Effective: " & Parameters!PMStartDate.Value & " to "
> & Parameters!PMEndDate.Value
> How do I control the format of the date? I want the Format to be
dd/MMM/yyyy.|||My favorite way of formatting strings is
=String.Format("From {0:d} to {1:d}", Parameters!
From.Value, Parameters!To.Value)
>--Original Message--
>I have a Text box containing the following info:
>="Effective: " & Parameters!PMStartDate.Value & " to "
>& Parameters!PMEndDate.Value
>How do I control the format of the date? I want the
Format to be dd/MMM/yyyy.
>.
>

formating dates

I have a date being input from a stored procedure. It come in as 11/22/2005.
I tried to format this date using:
=Format(Fields!DATE_RECEIVED.Value,"mm/dd/yy"). The date returned to me was:
00/22/05. What am I doing wrong?Hi Donna,
You need to capital M's for month as follows:
Format(Fields!DATE_RECEIVED.Value,"MM/dd/yy")
"DONNA" wrote:
> I have a date being input from a stored procedure. It come in as 11/22/2005.
> I tried to format this date using:
> =Format(Fields!DATE_RECEIVED.Value,"mm/dd/yy"). The date returned to me was:
> 00/22/05. What am I doing wrong?|||What I did to fix this, under the properties of the text box or field
display change the format code to "d".

Formating Dates

In my chart report, my x-axis date is displaying as 9/1/05, 10/1/05, etc. I
would like to display the date as 9/05, 10/05, etc, is it possible?
Thanks,
JimmyTry the value in the format tab if the list provides the format select it .
otherwise try giving dd/mm in the text box of the format tab.
Amarnath
"jcl_tw" wrote:
> In my chart report, my x-axis date is displaying as 9/1/05, 10/1/05, etc. I
> would like to display the date as 9/05, 10/05, etc, is it possible?
> Thanks,
> Jimmy|||Amarnath,
Tried using "mm/yy" in the Format Code field. The date shows up as 00/05
for all the months.
Jimmy
"Amarnath" wrote:
> Try the value in the format tab if the list provides the format select it .
> otherwise try giving dd/mm in the text box of the format tab.
> Amarnath
> "jcl_tw" wrote:
> > In my chart report, my x-axis date is displaying as 9/1/05, 10/1/05, etc. I
> > would like to display the date as 9/05, 10/05, etc, is it possible?
> >
> > Thanks,
> > Jimmy|||Try using instr function to seperate the mm/yy ofcourse use date to string
convertion.
Amarnath
"jcl_tw" wrote:
> Amarnath,
> Tried using "mm/yy" in the Format Code field. The date shows up as 00/05
> for all the months.
> Jimmy
> "Amarnath" wrote:
> > Try the value in the format tab if the list provides the format select it .
> > otherwise try giving dd/mm in the text box of the format tab.
> >
> > Amarnath
> >
> > "jcl_tw" wrote:
> >
> > > In my chart report, my x-axis date is displaying as 9/1/05, 10/1/05, etc. I
> > > would like to display the date as 9/05, 10/05, etc, is it possible?
> > >
> > > Thanks,
> > > Jimmy|||Jimmy,
Why dont you try this
=Format(DatePart("D",Fields!Date.Value),"#0") & "\" &
Format(Datepart("M",Fields!Date.Value),"#0")
jcl_tw wrote:
> Amarnath,
> Tried using "mm/yy" in the Format Code field. The date shows up as 00/05
> for all the months.
> Jimmy
> "Amarnath" wrote:
> > Try the value in the format tab if the list provides the format select it .
> > otherwise try giving dd/mm in the text box of the format tab.
> >
> > Amarnath
> >
> > "jcl_tw" wrote:
> >
> > > In my chart report, my x-axis date is displaying as 9/1/05, 10/1/05, etc. I
> > > would like to display the date as 9/05, 10/05, etc, is it possible?
> > >
> > > Thanks,
> > > Jimmy|||RajDeep,
Try your solution but unfortunately, the "Format Code" field under "Show
Labels" in the chart doesn't allows that many characters.
Jimmy
"RajDeep" wrote:
> Jimmy,
> Why dont you try this
> =Format(DatePart("D",Fields!Date.Value),"#0") & "\" &
> Format(Datepart("M",Fields!Date.Value),"#0")
> jcl_tw wrote:
> > Amarnath,
> >
> > Tried using "mm/yy" in the Format Code field. The date shows up as 00/05
> > for all the months.
> >
> > Jimmy
> >
> > "Amarnath" wrote:
> >
> > > Try the value in the format tab if the list provides the format select it .
> > > otherwise try giving dd/mm in the text box of the format tab.
> > >
> > > Amarnath
> > >
> > > "jcl_tw" wrote:
> > >
> > > > In my chart report, my x-axis date is displaying as 9/1/05, 10/1/05, etc. I
> > > > would like to display the date as 9/05, 10/05, etc, is it possible?
> > > >
> > > > Thanks,
> > > > Jimmy
>|||use MM/yy
mm refers to minutes, MM to month.
jcl_tw wrote:
> RajDeep,
> Try your solution but unfortunately, the "Format Code" field under "Show
> Labels" in the chart doesn't allows that many characters.
> Jimmy
> "RajDeep" wrote:
> > Jimmy,
> >
> > Why dont you try this
> >
> > =Format(DatePart("D",Fields!Date.Value),"#0") & "\" &
> > Format(Datepart("M",Fields!Date.Value),"#0")
> >
> > jcl_tw wrote:
> > > Amarnath,
> > >
> > > Tried using "mm/yy" in the Format Code field. The date shows up as 00/05
> > > for all the months.
> > >
> > > Jimmy
> > >
> > > "Amarnath" wrote:
> > >
> > > > Try the value in the format tab if the list provides the format select it .
> > > > otherwise try giving dd/mm in the text box of the format tab.
> > > >
> > > > Amarnath
> > > >
> > > > "jcl_tw" wrote:
> > > >
> > > > > In my chart report, my x-axis date is displaying as 9/1/05, 10/1/05, etc. I
> > > > > would like to display the date as 9/05, 10/05, etc, is it possible?
> > > > >
> > > > > Thanks,
> > > > > Jimmy
> >
> >|||Jen,
It works!
Thanks,
Jimmy
"Jen" wrote:
> use MM/yy
> mm refers to minutes, MM to month.
> jcl_tw wrote:
> > RajDeep,
> >
> > Try your solution but unfortunately, the "Format Code" field under "Show
> > Labels" in the chart doesn't allows that many characters.
> >
> > Jimmy
> >
> > "RajDeep" wrote:
> >
> > > Jimmy,
> > >
> > > Why dont you try this
> > >
> > > =Format(DatePart("D",Fields!Date.Value),"#0") & "\" &
> > > Format(Datepart("M",Fields!Date.Value),"#0")
> > >
> > > jcl_tw wrote:
> > > > Amarnath,
> > > >
> > > > Tried using "mm/yy" in the Format Code field. The date shows up as 00/05
> > > > for all the months.
> > > >
> > > > Jimmy
> > > >
> > > > "Amarnath" wrote:
> > > >
> > > > > Try the value in the format tab if the list provides the format select it .
> > > > > otherwise try giving dd/mm in the text box of the format tab.
> > > > >
> > > > > Amarnath
> > > > >
> > > > > "jcl_tw" wrote:
> > > > >
> > > > > > In my chart report, my x-axis date is displaying as 9/1/05, 10/1/05, etc. I
> > > > > > would like to display the date as 9/05, 10/05, etc, is it possible?
> > > > > >
> > > > > > Thanks,
> > > > > > Jimmy
> > >
> > >
>|||you dont need to mention anything in the format code,for value you will
have Fields!Date.Value and under that if you can see some thing like
label,write the expression posted there.
even though you got the solution ,it will be useful when it is
important
Regards
Raj Deep.A
jcl_tw wrote:
> RajDeep,
> Try your solution but unfortunately, the "Format Code" field under "Show
> Labels" in the chart doesn't allows that many characters.
> Jimmy
> "RajDeep" wrote:
> > Jimmy,
> >
> > Why dont you try this
> >
> > =Format(DatePart("D",Fields!Date.Value),"#0") & "\" &
> > Format(Datepart("M",Fields!Date.Value),"#0")
> >
> > jcl_tw wrote:
> > > Amarnath,
> > >
> > > Tried using "mm/yy" in the Format Code field. The date shows up as 00/05
> > > for all the months.
> > >
> > > Jimmy
> > >
> > > "Amarnath" wrote:
> > >
> > > > Try the value in the format tab if the list provides the format select it .
> > > > otherwise try giving dd/mm in the text box of the format tab.
> > > >
> > > > Amarnath
> > > >
> > > > "jcl_tw" wrote:
> > > >
> > > > > In my chart report, my x-axis date is displaying as 9/1/05, 10/1/05, etc. I
> > > > > would like to display the date as 9/05, 10/05, etc, is it possible?
> > > > >
> > > > > Thanks,
> > > > > Jimmy
> >
> >

format month name in chart

I'm trying to format the month name in a chart. I have dates, such as 1/31/2006, 2/28/2006 etc. I want them to display as Jan, Feb, Mar etc.

I know I can put 'P0' in the format field to format as a %.

Is there a format for the month name?

Yes, DateName(DatePart(month,"1/31/2006"))

Hammer

Format Dates in TSQL - My code below

Howdy,
Is this proc:
SELECT ID, CID, dtDate, strTime, dtRSVP,
CASE
WHEN dtRSVP > 1/1/1900 THEN
'RSVP by ' + CAST(MONTH(dtRSVP) as varchar(15)) + '/' + CAST(DAY(dtRSVP)
as varchar(15)) + '/' + CAST(YEAR(dtRSVP) as varchar(15))
ELSE
NULL
END AS niceRSVP
FROM tblDates
the best solution to get these results:
ID EID dtDate strTimes
dtRSVP niceRSVP
3 4 2005-12-01 00:00:00 1:00 PM to 3:00 PM 2005-11-25
00:00:00 RSVP by 11/25/2005
4 4 2005-12-02 00:00:00 12-4 PM
1900-01-01 00:00:00 NULL
Basically, I want the dates formatted like 11/25/2005, and empty date fields
to be NULL not 1/1/1900. Same goes for the dtDate and dtRSVP fields, but I
made the niceRSVP field to compensate.
Please advise!
Thanks!
David Lozzi
Web Applications Developer
dlozzi@.(remove-this)delphi-ts.comDavid,
Is it required to be a NULL or the text NULL. If the latter, the following
will work. See as example:
CREATE TABLE TBL_DATES
(ID INT NOT NULL,
DTVAL DATETIME )
GO
INSERT TBL_DATES(ID,DTVAL)
VALUES(1, '10/12/05')
INSERT TBL_DATES(ID,DTVAL)
VALUES(2, '')
INSERT TBL_DATES(ID)
VALUES(3)
GO
SELECT CASE
WHEN CONVERT(VARCHAR(10),DTVAL,101) = '01/01/1900' THEN 'FALSE NULL' ELSE
CONVERT(VARCHAR(10),DTVAL,101)
END AS 'DATE'
FROM TBL_DATES
HTH
Jerry
"David Lozzi" <DavidLozzi@.nospam.nospam> wrote in message
news:elUhO1eyFHA.3096@.TK2MSFTNGP10.phx.gbl...
> Howdy,
> Is this proc:
> SELECT ID, CID, dtDate, strTime, dtRSVP,
> CASE
> WHEN dtRSVP > 1/1/1900 THEN
> 'RSVP by ' + CAST(MONTH(dtRSVP) as varchar(15)) + '/' + CAST(DAY(dtRSVP)
> as varchar(15)) + '/' + CAST(YEAR(dtRSVP) as varchar(15))
> ELSE
> NULL
> END AS niceRSVP
> FROM tblDates
> the best solution to get these results:
> ID EID dtDate strTimes dtRSVP
> niceRSVP
> 3 4 2005-12-01 00:00:00 1:00 PM to 3:00 PM 2005-11-25
> 00:00:00 RSVP by 11/25/2005
> 4 4 2005-12-02 00:00:00 12-4 PM 1900-01-01 00:00:00
> NULL
> Basically, I want the dates formatted like 11/25/2005, and empty date
> fields to be NULL not 1/1/1900. Same goes for the dtDate and dtRSVP
> fields, but I made the niceRSVP field to compensate.
> Please advise!
> Thanks!
> --
> David Lozzi
> Web Applications Developer
> dlozzi@.(remove-this)delphi-ts.com
>
>|||Or this with NULL.
SELECT CASE
WHEN CONVERT(VARCHAR(10),DTVAL,101) = '01/01/1900' THEN
NULLIF('01/01/1900',DTVAL) ELSE
CONVERT(VARCHAR(10),DTVAL,101)
END AS 'DATE'
FROM TBL_DATES
HTH
Jerry
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:e634GCfyFHA.2064@.TK2MSFTNGP09.phx.gbl...
> David,
> Is it required to be a NULL or the text NULL. If the latter, the
> following will work. See as example:
> CREATE TABLE TBL_DATES
> (ID INT NOT NULL,
> DTVAL DATETIME )
> GO
> INSERT TBL_DATES(ID,DTVAL)
> VALUES(1, '10/12/05')
> INSERT TBL_DATES(ID,DTVAL)
> VALUES(2, '')
> INSERT TBL_DATES(ID)
> VALUES(3)
> GO
> SELECT CASE
> WHEN CONVERT(VARCHAR(10),DTVAL,101) = '01/01/1900' THEN 'FALSE NULL' ELSE
> CONVERT(VARCHAR(10),DTVAL,101)
> END AS 'DATE'
> FROM TBL_DATES
>
> HTH
> Jerry
> "David Lozzi" <DavidLozzi@.nospam.nospam> wrote in message
> news:elUhO1eyFHA.3096@.TK2MSFTNGP10.phx.gbl...
>|||Or even more succinctly:
SELECT
niceRSVP = 'RSVP by ' + CONVERT(varchar(15),dtRSVP,101)
FROM
tblDates
A null value in dtRSVP will pass through CONVERT as a null, and adding
anything to a null equals null.
JR
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:e634GCfyFHA.2064@.TK2MSFTNGP09.phx.gbl...
> David,
> Is it required to be a NULL or the text NULL. If the latter, the
> following will work. See as example:
> CREATE TABLE TBL_DATES
> (ID INT NOT NULL,
> DTVAL DATETIME )
> GO
> INSERT TBL_DATES(ID,DTVAL)
> VALUES(1, '10/12/05')
> INSERT TBL_DATES(ID,DTVAL)
> VALUES(2, '')
> INSERT TBL_DATES(ID)
> VALUES(3)
> GO
> SELECT CASE
> WHEN CONVERT(VARCHAR(10),DTVAL,101) = '01/01/1900' THEN 'FALSE NULL' ELSE
> CONVERT(VARCHAR(10),DTVAL,101)
> END AS 'DATE'
> FROM TBL_DATES
>
> HTH
> Jerry
> "David Lozzi" <DavidLozzi@.nospam.nospam> wrote in message
> news:elUhO1eyFHA.3096@.TK2MSFTNGP10.phx.gbl...
>|||Jim,
How does this code account for the NULL if 01/01/1900?
HTH
Jerry
"Jim Ross" <jratwork_at_hotmail.com@.nowhwere.com> wrote in message
news:OMTaUPfyFHA.2076@.TK2MSFTNGP14.phx.gbl...
> Or even more succinctly:
> SELECT
> niceRSVP = 'RSVP by ' + CONVERT(varchar(15),dtRSVP,101)
> FROM
> tblDates
> A null value in dtRSVP will pass through CONVERT as a null, and adding
> anything to a null equals null.
> JR
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:e634GCfyFHA.2064@.TK2MSFTNGP09.phx.gbl...
>

Format Dates in ISO 8601

Hi,

I'm totally new at SQL Server, I'm sorry if this question looks very simple, but I just don't know how to change the datetime value in a field on my database.

I'm working in vb.net and I have a datetime field in sql server, I can store date values passing this value:

DatePick.Value.ToString("s")

dates are saved fine, but if want to make this query:

Select * from backlog where date1>=isdate('21/02/2006') order by date1 asc

I get all the values from that field, no filter is done. Date are shown like this 2004-05-23 14:25:10.000 in the query.

What do I have to do ? do I have to change the date format of my field to ISO 8601 ? how ? do I use a script or what ?

Please remeber that my sql script should be done from vb.

I really appreciate your help.

George

Hi,

ok, the following query looks strange to me as the "isdate('21/02/2006')" part will always return 1 as the date in the brackets is always a date, the function isdate only returns 0 for a non.date and 1 for a valid date. For a date comparison you should use something like date1>= '02/21/2006' (see the switched month and day for an automatic / implicit date conversion)

I get all the values from that field, no filter is done.

Date are shown like this 2004-05-23 14:25:10.000 in the query.

Thats by design, datetime values include date as well as time, but the application should be able to get the value and only use the datepart of it (if you want to).

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

You don't necessarily have to switch the day and month around. This is actually controlled by your language setting (for the login). If you have a language setting that interprets dates as day/month/year, then you can use your original format.

When using dates within SQL Server, it is best to use the ISO Unseperated Date Format which is YYYYMMDD. SQL Server will never misinterpret a date in this format.

The problem with your query is that IsDate returns a 1 or 0. Since IsDate('21/02/2006') probably returns a 1, you are actually selecting all dates greater than Jan 2, 1900 (which is probably all of your records).

I suggest you modify your query to...

Select * from backlog where date1 >= '20060221' order by date1 asc

To determine your language settings, you can run:

sp_configure 'default language'

This will return a config_value which you can use to lookup the actual language when you run:

sp_helplanguage

You should know that the language setting is based on the login.

Format date with leading zeros

Right now we're displaying dates like this: 2/6/2006
We'd like to display them like this: 02/06/2006
Currently we have two types of dates we're dealing with. In the first
case
we have a datetime and we're doing this:
=FormatDateTime(Code.GetStartDate(),vbShortDate)
in the second we just have a date, and we set the format code to 'd'
by selecting one of three date formats that are offered when we hit the
... button
on the Format tab.
Anyway, in each case we want the leading zeros.
TIA,
JimLet me refine that.
I've solved this to a point by using mm/dd/yyyy in the format. But
it's entirely possible
that this will be used where we would want dd/mm/yyyy.
So I would want a format that will adapt to regional settings, but will
also show leading zeros.
But only if it's simple ;)|||Hi,
If I have understood your problem correctly, the "d" format code will do
this, according to the language property of the report.
E.g. if the date '8 Jan 2006 23:15:58' would display as '08/01/2006' if
format was set to "d" and Language to 'English(United Kingdom).
If Language was set to 'English(United States), it would display as
'01/08/2006'.
If Language was set to '=User.Language', it would display in whatever format
was set on the client machine.
I hope this helps.
Ed Allison
<jhcorey@.yahoo.com> wrote in message
news:1140125197.767867.274710@.g43g2000cwa.googlegroups.com...
> Let me refine that.
> I've solved this to a point by using mm/dd/yyyy in the format. But
> it's entirely possible
> that this will be used where we would want dd/mm/yyyy.
> So I would want a format that will adapt to regional settings, but will
> also show leading zeros.
> But only if it's simple ;)
>|||I probably should have added that that is what we were using
originally, and while it no doubt handles the different regional
formats, it would not display the leading zeros.|||Really? It does for me. Sorry not to have been of more help.
<jhcorey@.yahoo.com> wrote in message
news:1140194093.511041.100970@.z14g2000cwz.googlegroups.com...
>I probably should have added that that is what we were using
> originally, and while it no doubt handles the different regional
> formats, it would not display the leading zeros.
>|||Hmm...
This made me think that it has to do with the regional settings on my
machine.
So I went in there and changed the short date format to mm/dd/yyyy.
But that didn't do it. I'm running everything local (I think). But I
think I'm on a track.