Formulas for Column Properties

I have a date column that has 1/1/1900 entries and I want to write a formula..(i think)...in the column formula section that when this date is encountered, the column should show NULL. I don't know the syntax to use in the formula section of the column properties. Can anyone give some syntax examples.

Thanks

Hi,

if you want to store 1/1/1900 but just display NULL you should consider using a view or a query to decide on the value. If you want to change the data upon insert in the table, you should consider a trigger to change the data on the events like update / Insert to change if its a 1/1/1900 to NULL.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

sql

Formulas

I have a table with 1 columns in it
CREATE TABLE [dbo].[date] (
[Date_updated] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
[Day_of_w] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
[Day_of_month] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
[Day_of_year] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
I want to aaply the following formulas to the last 3 columns, but when I
enter the formula in the on the design table screen, it doesn't like the
syntax. I am entering the following for each column
datepart(DW,Date_occured_from)
datepart(DD,Date_occured_from)
datepart(DY,Date_occured_from)
Can someone help with the correct syntax for doing this please. ThanksI do not see column [Date_occured_from] in the table definition.
Example:
use northwind
go
create table t (
colA datetime default (getdate()),
colB as cast(right('0' + ltrim(datepart(dw, colA)), 2) as char(2))
)
go
insert into t default values
go
select * from t
go
drop table t
go
AMB
"Munch" wrote:

> I have a table with 1 columns in it
> CREATE TABLE [dbo].[date] (
> [Date_updated] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> [Day_of_w] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> [Day_of_month] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> [Day_of_year] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> I want to aaply the following formulas to the last 3 columns, but when I
> enter the formula in the on the design table screen, it doesn't like the
> syntax. I am entering the following for each column
> datepart(DW,Date_occured_from)
> datepart(DD,Date_occured_from)
> datepart(DY,Date_occured_from)
> Can someone help with the correct syntax for doing this please. Thanks
>|||"Enter the formula" ' Are you trying to create computed columns?
If so, the column [Date_occured_from] must exists in the table, and it must
be a datetime, or smalldatetime datatype.
The only column in your table that appears to be a date is Date_Updated, and
it is typed as a Char(8)... If you want to use that, you will have to cat it
to a datetime first, and use the correct column name
datepart(DW, Cast(Date_Updated As DateTime))
datepart(DD, Cast(Date_Updated As DateTime))
datepart(DY, Cast(Date_Updated As DateTime))
"Munch" wrote:

> I have a table with 1 columns in it
> CREATE TABLE [dbo].[date] (
> [Date_updated] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> [Day_of_w] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> [Day_of_month] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> [Day_of_year] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> I want to aaply the following formulas to the last 3 columns, but when I
> enter the formula in the on the design table screen, it doesn't like the
> syntax. I am entering the following for each column
> datepart(DW,Date_occured_from)
> datepart(DD,Date_occured_from)
> datepart(DY,Date_occured_from)
> Can someone help with the correct syntax for doing this please. Thanks
>|||But, lest I forget, You would be much much better off using DateTime or
SmallDateTime as the datatype of that column (Date_Updated) in the first
place...
"Munch" wrote:

> I have a table with 1 columns in it
> CREATE TABLE [dbo].[date] (
> [Date_updated] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> [Day_of_w] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> [Day_of_month] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> [Day_of_year] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> I want to aaply the following formulas to the last 3 columns, but when I
> enter the formula in the on the design table screen, it doesn't like the
> syntax. I am entering the following for each column
> datepart(DW,Date_occured_from)
> datepart(DD,Date_occured_from)
> datepart(DY,Date_occured_from)
> Can someone help with the correct syntax for doing this please. Thanks
>

Formula Error - Cannot find dimension member

When I run a MDX query for a specific date, where I donot have any data in the fact table, I get the error - "Formula Error - Cannot find dimension member "--" - in a name binding function.
Query 1:
select {[Measures].[BlockOrders]} ON COLUMNS
from USAGEDETAILS
WHERE [Trdate].[All Trdate].[2002].[July].[30]
This works fine, but if I run the following query, I get the above error message, because there is no data for July 1st, in the fact table.

select {[Measures].[BlockOrders]} ON COLUMNS
from USAGEDETAILS
WHERE [Trdate].[All Trdate].[2002].[July].[1]

Can some one help me?
Thanks,
Sophia.We have two tables Loans (Parent) & LoanIndicators (Child).

LoanIndicators stores the Loan status (ex Outstanding Amount) date wise. This table is updated with a new record only when the Outstanding Amount Changes , until that time the last record holds the status.

Ex :
Loan Table
Id LoanId LoanAmount
1 L1 1000
2 L2 2000

LoanIndicator Table

Id LoanId IndicatorDate Outstanding Amount
1 L1 1/1/2005 100
2 L1 1/13/2005 90

So in the above case if i want to know the Outstanding amount on 1/10/2005 of L1 i.e the record dated 1/1/2005 gives me the outstanding amount of 100.

To Create a Cube on this I create a View linking Loan & LoanIndicators i.e the view holds all the Parent + Child Records of each of the Loans.
Also the cube has a dimension on IndicatorDate.

Now in the Cube the Total AMount of Loan disbursed is 4000 but it shouold be 3000.
It is 4000 because it is summing L1 twice as the view has two records for L1.
1) How do we prevent this summing of L1 twice ?

2) If I want to know the Outstanding Amount on 1/10/2005 for L1 , how do i get it (It should be 100 as the position only changes on 1/13/2005).

TIA

Shuchi Agarwalsql

Formula Days Left in Month

What is the best way to get the days left in the month excluding the weekend from todays date?
Also to get the count of the days in the month excluding the weekends?
For example: If today is Mar.15
WeekDays in the month: 22
WeekDays left: 12
Thanks.See the help on DateSerial re. getting the last day in the month. See the help on DataDiff for an example of days between two dates, and also how to exclude weekends.

Formatting the date on report

Reporting Services 2005 (SQL Server 2005 CTP V 9.00.1187.00)

I have a date field and I want to format it on a report like:

January 9, 2006

Seems to me that should be pretty simple. But I can't find the correct format code. If I look at the textbox properties and go to the format tab, there is an option for Date, but there are only 3 choices:

1/9/2006 9:47 AM

Monday, January 9, 2006

1/9/2006

If I choose custom I found that Y gives me:

January 2006

and M gives me:

January 09

This is rather frustrating. I'm sure I could modify my source SQL to get each part of the date and past it together, but I would think a good reporting tool would give me the option on how to format the date, so I don't have to always modify the SQL. I've searched for help on the different format options and I can't find any.

Any help would be very much appreciated.

Thanks.

Well, I finally figured it out, with the help of some obscure posting on another forum. I now use format:

MMMM dd"," yyyy

interesting that you have to use MMMM, not mmmm and you have to use yyyy, not YYYY.

Now I could not find this anywhere in the documentation. I'm sure it must be there, but where? Does anybody know? I'm sure I'm going to have other formatting issues and I really don't want to have to search through forum postings on the internet to find crumbs of clues to figure out things that I think should be easily found in the products documentation. I'm sure it's me. It's gotta be right in front of me, or I did something wrong when installing and I don't have the correct documentation. (but then why can't I find it at MSDN?)

|||

You can find documentation about these custom .NET format strings (e.g. MMMM and yyyy) on MSDN: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconcustomdatetimeformatstrings.asp

-- Robert

|||Thanks!

Formatting Question

I have an old DB2 app that has date values in 4 fields (i.e. Date1_MO, Date1_DA, Date1_CN, Date1_YR). I have several MSAccess queries that I convert this to a date by doing the following:

CDate(Date1_MO & "/" & Date1_DA & "/" & Date1_CN & Format(Date1_YR,"00"))

Piece of cake...however I am struggling with this in MSSQL.

Mostly I am fighting formatting the Year. As you can see, If I were to concatinate the above values i would come up with something like 3/9/204 for a date of March 9, 2004. (Each field is a numeric value).

I have gotten this far...

select CAST(Date1_MO as varchar(2))+ '/' + CAST(Date1_DA as varchar(2))
+ '/' + CAST(Date1_CN as varchar(2))+ CAST(Date1_YR as varchar(2)) as Date1
From tPrices

I still need to convert the whole string to a date, but more importantly, I cannot figure out how to get the last element (Year) to format as '04' instead of '4'. I can't concatinate a 0 in front of it for obvious reasons. (Athough I was tempted, just joking)

I looked through a lot of the T-SQL docs but have come up dry.

Anyway HELP!!!!!!Try this for the last 2 digits of the year:

right('0'+CAST(Date1_YR as varchar(2)), 2)|||Came to the same conclusion about the same time you replied...

Just playing with the conversion now.

Thanks for your response...

Formatting question

I want to show a parameter date minus 1 year, how can I do this?
="PERIOD MINUS 1 YEAR: " & Parameters!startdate.Value & " to " &
Parameters!stopdate.ValueTry:
="PERIOD MINUS 1 YEAR: " &
System.DateTime.Parse(Parameters!startdate.Value).AddYears(-1).ToShortDateString()
& " to " &
System.DateTime.Parse(Parameters!stopdate.Value).AddYears(-1).ToShortDateString()
or something simular
"Chris Patten" <ChrisPatten@.discussions.microsoft.com> wrote in message
news:33AB832F-0620-4A7C-B2FC-5D38A36C7429@.microsoft.com...
>I want to show a parameter date minus 1 year, how can I do this?
> ="PERIOD MINUS 1 YEAR: " & Parameters!startdate.Value & " to " &
> Parameters!stopdate.Value|||That is the shizzle my fizzle!!
(or however that should sound, thanks!)
Chris
"Oleg Yevteyev" wrote:
> Try:
> ="PERIOD MINUS 1 YEAR: " &
> System.DateTime.Parse(Parameters!startdate.Value).AddYears(-1).ToShortDateString()
> & " to " &
> System.DateTime.Parse(Parameters!stopdate.Value).AddYears(-1).ToShortDateString()
> or something simular
> "Chris Patten" <ChrisPatten@.discussions.microsoft.com> wrote in message
> news:33AB832F-0620-4A7C-B2FC-5D38A36C7429@.microsoft.com...
> >I want to show a parameter date minus 1 year, how can I do this?
> >
> > ="PERIOD MINUS 1 YEAR: " & Parameters!startdate.Value & " to " &
> > Parameters!stopdate.Value
>
>sql

Formatting parameters

Hi!
I would like to change date parameter from
4/30/2002 12:00:00 AM to 30.4.2002 in report.
How can parameters be formatted?
Thanks
Romanhi Bender,
Write this expression in the the textbox that you want to show you the date:
=format(Globals!ExecutionTime,"dd.MM.yyyy")
i hope it will help you.
"Bender" wrote:
> Hi!
> I would like to change date parameter from
> 4/30/2002 12:00:00 AM to 30.4.2002 in report.
> How can parameters be formatted?
> Thanks
> Roman
>
>|||For parameters, you must aplicate the function format to
Parameters!parameter_name.value (instead of Globals!ExecutionTime).
"Bender" wrote:
> Hi!
> I would like to change date parameter from
> 4/30/2002 12:00:00 AM to 30.4.2002 in report.
> How can parameters be formatted?
> Thanks
> Roman
>
>|||I'll try to explain again what I want to do :)
In the Report Parameters dialog I define my Parameter and set
Data Type to Datetime (there is not many options anyway).
When I run report I get 4/1/2002 12:00:00 AM in parameter box on
top of report (parameters section of report), which is correct.
But I want to format this to see and make input like
dd.MM.yyyy and without time part.
Any ideas.
Thanks
"Mirela" <Mirela@.discussions.microsoft.com> wrote in message
news:037EE286-0206-42A1-88C9-A6C3303EE254@.microsoft.com...
> For parameters, you must aplicate the function format to
> Parameters!parameter_name.value (instead of Globals!ExecutionTime).
> "Bender" wrote:
> > Hi!
> >
> > I would like to change date parameter from
> > 4/30/2002 12:00:00 AM to 30.4.2002 in report.
> >
> > How can parameters be formatted?
> >
> > Thanks
> > Roman
> >
> >
> >

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 in cube

Is there a way to format data in the cube?

I have a date formatted as such:

YYYY-MM

I want to show the date as 01, 02, 10, 11, ... instead of 2006 1, 2006 2, I want 2006 01, 2006 02, and so on

is this possible to accomplish?

Also,

I have numbers showing

5.12345677999E-02

How can i get this to show as 5.12345 only?

Is this something that should be handled on the db/select statement side or is there a way to do this on the analysis services cube side?

thanks

On the date formatting, I am assuming that your dates are part of a dimension. In this case what you would normally do is to define a column either in a view or in the DSV that formats the date the way you want it and returns this as a string. You then set this string up to be the attributes name and set up the actual value to be the attribute key. This allows you to do things like sort by values, but display nice strings for the names.

On the numbers issue, there is a format string that you can define for every measure. The only problem here is that Analysis Services can return both the formatted and unformatted values and some clients (like Excel prior to Excel 2007) only take the unformatted value, so the formatting may have to be done client side.

|||

i have my derived column formatted as yyyy-dd-mm but how would I have the 0 in front of 1, 2, 3, 4, 5, 6, 7, 8, 9 and not in front of 10,11,12?

what would i have to add in the derived column task?

|||You can use the Len() function to determine if your number is 1 character long, and prefix a zero if it is. Len is supported in the SSIS derived column transformation, if that is what you were referring to in your question. It's also supported in most databases, so you could implement it in your data source view as well.|||

In T-SQL you could do something like the following, just replace the call to GETDATE() with the date column name in your table

SELECT LEFT(CONVERT(varchar,getdate(),121),7)

The 121 format is "yyyy-mm-dd hh:mi:ss.mmm(24h)" and you just grab the left 7 of that.

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

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

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 Date/Time

What do I have to use to Format my date/time fiels so that they will not include the both the date and the time? I imported the db's and reports from access and anything date/time shows both. The VBA functions I know are powerless to fix this.

So:
Format(fields!DateField, 'hh:mm') ?
Format(fields!DateField, 'ddmmyyy') ?

What are the equivalents? Yes, I read the manual. Still not obvious to me.

- Thanks in advance.

Format is the VB.NET Format function (see e.g. http://msdn2.microsoft.com/en-us/library/59bz1f0h(vs.80).aspx). The following expression should work for you (note: you have to use double quotes not single quotes for the format string):
=Format(Fields!DateField.Value, "hh:mm")

Additional information about datetime format strings is available here:
* http://msdn.microsoft.com/library/en-us/cpguide/html/cpconcustomdatetimeformatstrings.asp
* http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpcondatetimeformatstrings.asp

-- Robert

|||Thank you Robert. This works in my .rdl reports. I also want to apply Format to a Parameter which uses a query. I tried to code Format into my SQL as I would with VBA/Jet SQL and it's not working. How can I apply Format to my SQL output or Parameters?|||

Please check the TSQL documentation on the Cast and Convert functions:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_2f3o.asp

-- Robert

Formatting Date in SRS

I am trying to format a date value as XX/XX/XX when I place a "d" in the format property it formats it as XX/XX/XXXX any ideas on how to change it to XX/XX/XX?

thanks in advance

use dd/MM/yy

Formatting Date as integer

I want to for that format the date in YYYYMMDD and MMDDYY, with no '-' as data type is integer

I have used the following code (not the conversion function as I don’t need Hyphen '-')

for YYYYDDMM

SET @.DATE = CONVERT(INT,(CONVERT(VARCHAR(4),DATEPART(YYYY,GETDATE())) +

CONVERT(VARCHAR(4), DATEPART(MM,GETDATE())) +

CONVERT(VARCHAR(4), DATEPART(DD,GETDATE())) ))

& for MMDDYY

SET @.DATEUS = CONVERT(INT,(CONVERT(VARCHAR(3),DATEPART(MM,GETDATE()))+

CONVERT(VARCHAR(3),DATEPART(DD,GETDATE())) +

SUBSTRING(CONVERT(VARCHAR(4), DATEPART(YY,GETDATE())),3,4) ))

I am getting the result

YYYYMMDD= 200688

MMDDYY =8806

but i want result in

YYYYDDMM = 20060808

MMDDYY = 080806

note: I need to convert in integer, finally, caz database data type is integer.

can any one give me solution

waiting for quick reply

regards,

Anas

Just use convert:

select cast(convert(varchar(8),getdate(),112) as integer)

I use this for our date_dimension/calendar table surrogate keys all of the time.

|||thanx for your quick reply.

Formatting Date /Time in SQL 2000

I used to have an Access 2k query that formatted this date field:
Format([PrDateStart],"yyyy/mm/dd"" 00:00:01""") AS ProjectStartDate
That gave me the date as this:
2006/03/30 00:00:01
Recently, we moved the data to a SQL 2000 table, so now I need to
create a view that gives me this date in that same format like I was
able to get it with that access query.
The actual date stored in the SQL table is 03/30/20006.
We use (convert(varchar,getdate(),101)) to get only the actual date
without the time.
Can anyone help me figure out how to get the same date format in SQL
Server 2000?
Thanks.SELECT CONVERT(CHAR(10), GETDATE(), 111);
http://www.aspfaq.com/2464
<ILCSP@.NETZERO.NET> wrote in message
news:1143819658.697747.320460@.j33g2000cwa.googlegroups.com...
>I used to have an Access 2k query that formatted this date field:
> Format([PrDateStart],"yyyy/mm/dd"" 00:00:01""") AS ProjectStartDate
> That gave me the date as this:
> 2006/03/30 00:00:01
> Recently, we moved the data to a SQL 2000 table, so now I need to
> create a view that gives me this date in that same format like I was
> able to get it with that access query.
> The actual date stored in the SQL table is 03/30/20006.
> We use (convert(varchar,getdate(),101)) to get only the actual date
> without the time.
> Can anyone help me figure out how to get the same date format in SQL
> Server 2000?
> Thanks.
>|||First off, if you've changed to a SQL server 2000 back end I'd hope
you've moved to a sensible front end (like .NET) in which case this is
where you should be doing the formatting (.NET has a very easy and
powerful set of format strings).
otherwise, to my knowledge you can only use the set formats that
convert gives you, otherwise you'll have to build up your string using
CAST(DatePart(YEAR,mydate) as char(4)) + '/' + CAST(DATEPART(MONTH,
mydate) as char(2)) + '/' etc...
NOTE: it's important to cast them as strings, otherwise sql my
interpret this as an arithmetic expression and start dividing the year
by the month etc.
Cheers
Will|||SELECT replace(convert(varchar, getdate(), 120),'-','/') as todays_date|||Hi guys, I did use Aaron's date and then I added a string showing the
seconds and that worked out well..
Thanks for your suggestions.
By the way, yes, we're moving to .NET in the coming months.

Formatting a date displayed in a crystal report

Hello

I am using VS 2005 CR 10.

I am using typed datasets to populate a report.

From my database I have 1 field called incidentDate, this is a smallTimeDate datatype.

My reports always displayes the correct date, but is followed by the time which is always 12:00. For example 18/12/2006 12:00 AM.

I don't want to display this time. I have tried setting the DateWindowsDefaultType in the report properties for that field. But this did not work.

Does anyone have any other ideas,

Thanks very much,

SteveMake sure the type of the field in the report is Date.then whenu put it in the report right click choose formatObject u will find a Date Tab click and choose the format u want...