Forms Authentication

I've successfully set up RS to use Forms Authentication. However, here's
something strange. The first time I access Report Manager (say after a
reboot) and enter the admin credentials, RS churns and churns, then I end up
back at the login screen. If I enter the same credentials again, I get
logged in successfully.
Any one else experience this issue?
ThxAre you using the same server name to access the report server in your
browser as is set in the config files? If not, the cookie won't be getting
through due to cross-(web)domain security.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"FNDS" <absolutelynospam@.nodomain_.com> wrote in message
news:ezRzXl%239EHA.1396@.tk2msftngp13.phx.gbl...
> I've successfully set up RS to use Forms Authentication. However, here's
> something strange. The first time I access Report Manager (say after a
> reboot) and enter the admin credentials, RS churns and churns, then I end
> up back at the login screen. If I enter the same credentials again, I get
> logged in successfully.
> Any one else experience this issue?
> Thx
>|||"Jeff A. Stucker" <jeff@.mobilize.net> wrote in
news:ugC03WC#EHA.3708@.TK2MSFTNGP14.phx.gbl:
> Are you using the same server name to access the report server in your
> browser as is set in the config files? If not, the cookie won't be
> getting through due to cross-(web)domain security.
>
So, how do you address this issue in a hardware load balanced, web farm?
We have a single URL (domain) (ex. http://reporting.xxxxxxx.com/Reports)
but each machine naturally maintains a unique IP that I'm assuming is being
used by each machine when setting the cookie. The web.config files have
matching <machinekey> values to allow for Forms Auth in a web farm but I
don't know how to control the domain of the cookie.

Formatting T-SQL Performacne Boost?

Hey, I'm using PHP with MSSQL, and I'm not having any performace problems or anything, but at the same time I'm trying to optimise our system to work as best as it can as it's going to have a very heavy load once we launch.

I'm running into the age old problem with the battle between optimizing your code and still keeping it readable. I read somewhere that using whitespace in SQL queries is really bad as it take a lot more bandwidth and puts more stress on SQL Server parsing the SQL it is sent. Right now I have a query like so:-

<?php
$selQ = 'SELECT
n.pknewsID AS newsID,
n.title,
n.full_text,
n.publish_up AS datePublished,
CONCAT(u.fname," ",u.lname) AS author,
i.loc AS img_file,
i.descr AS img_caption
FROM tblnews n
LEFT JOIN tblusers u
ON n.fkcreated_by = u.pkuserID
LEFT JOIN tblnews_images i
ON i.fknewsID = n.pknewsID
WHERE
n.pknewsID = '.$articleID.' AND
n.published = 1
LIMIT 1';
$selQ = $DB->setQuery($selQ);

/**
* $DB->setQuery basically is a preg_replace function that
* removes all the tabs in the query string, and replaces them
* with a single space.
*
*/

echo $selQ;

/**
* Printed out it looks like this :-
* SELECT
* n.pknewsID AS newsID
* n.title
* n.full_text
* n.publish_up AS datePublished
* CONCAT(u.fname," ",u.lname AS author,
* i.loc AS img_file,
* i.descr AS img_caption,
* FROM tblnews n
* LEFT JOIN tblusers u
* ON n.fkcreated_by = u.pkuserID
* LEFT JOIN tblnews_images i
* ON i.fknewsID = n.pknewsID
* WHERE
* n.pknewsID = 6 AND
* n.published = 1
* LIMIT 1
*
*/
?>

So the question I have is, does it really matter how the SQL query is sent? I mean, if I put it all on one line (which would kinda suck as it is harder for me to read then), would it speed up transactions significally?
Whitespace does take up more space and increase the network packet size, but I would prefer readability over trying to minimize whitespace. It does not adversely affect the parser.

If you are worried about packet sizes, use stored procedures since a proc name is shorter then a query or batch. Also, use parameterized queries in general since the server can efficiently cache them and avoid compiling the query. This would be a bigger performance savings in your case above.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 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 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 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 /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.

formating string to time

Hello,

I have the following question.

I have a report where i do a lot of time calculation

in one colomn i have values in seconds.

for example i have a total of 246 seconds

how can i format this to view as 00:04:06 or even 4 mins 6 secs

Vincent

Hello Vincent,

Try putting this in your textbox's expression...

=cStr(Floor(Fields!TotalSeconds.Value / 60)) + " mins "
+ cStr(Fields!TotalSeconds.Value - Floor(Fields!TotalSeconds.Value / 60) * 60) + " secs"

Hope this helps.

Jarret

|||

Hello Jarret,

This works great.

Thank you.

Vincent

|||

Hi,

How can i add the hours also to it ?

I'm still really quite new to all this stuff, but i'm working on it.

Thanks

Vincent

|||

For hours, try this...

=cStr(Floor(Fields!TotalSeconds.Value / 3600)) + " hours "
+ cStr(Floor(Fields!TotalSeconds.Value / 60) - Floor(Fields!TotalSeconds.Value / 3600) * 60) + " mins "
+ cStr(Fields!TotalSeconds.Value - Floor(Fields!TotalSeconds.Value / 60) * 60) + " secs"

Jarret

format when taking two fields away from other

hi there
can someone show me how to format the following please
=(Fields!SLAResolutionDate.Value)-(Fields!CreatedDate.Value)
these are both date/time fields - I want to format to just day - two
digits and just time - two digits
so like this =Format(Fields!SLAResolutionDate.Value)-(Fields!
CreatedDate.Value), dd, hh
but I am unsure where to stop and start the brackets to get the
result.
thanks
jewelNot sure if this is the simplest, but it will work.
Try this:
'This first part finds only the "whole days". The second part determines
the "left over hours".
Format(DATEDIFF(d,
Fields!SLAResolutionDate.Value,Fields!CreatedDate.Value),"00") & " " &
Format(DATEDIFF(d, Fields!SLAResolutionDate.Value,Fields!CreatedDate.Value) *
24 - DATEDIFF(hh,
Fields!SLAResolutionDate.Value,Fields!CreatedDate.Value),"00")
Michael
"jewelfire" wrote:
> hi there
> can someone show me how to format the following please
> =(Fields!SLAResolutionDate.Value)-(Fields!CreatedDate.Value)
> these are both date/time fields - I want to format to just day - two
> digits and just time - two digits
>
> so like this =Format(Fields!SLAResolutionDate.Value)-(Fields!
> CreatedDate.Value), dd, hh
> but I am unsure where to stop and start the brackets to get the
> result.
> thanks
> jewel
>|||Correction: the second part should be reversed (hours - days*24):
Format(DATEDIFF(d,
Fields!SLAResolutionDate.Value,Fields!CreatedDate.Value),"00") & " " &
Format(DATEDIFF(hh,
Fields!SLAResolutionDate.Value,Fields!CreatedDate.Value)-
DATEDIFF(d, Fields!SLAResolutionDate.Value,Fields!CreatedDate.Value) *
24,"00")
"Michael C" wrote:
> Not sure if this is the simplest, but it will work.
> Try this:
> 'This first part finds only the "whole days". The second part determines
> the "left over hours".
> Format(DATEDIFF(d,
> Fields!SLAResolutionDate.Value,Fields!CreatedDate.Value),"00") & " " &
> Format(DATEDIFF(d, Fields!SLAResolutionDate.Value,Fields!CreatedDate.Value) *
> 24 - DATEDIFF(hh,
> Fields!SLAResolutionDate.Value,Fields!CreatedDate.Value),"00")
> Michael
> "jewelfire" wrote:
> > hi there
> >
> > can someone show me how to format the following please
> >
> > =(Fields!SLAResolutionDate.Value)-(Fields!CreatedDate.Value)
> >
> > these are both date/time fields - I want to format to just day - two
> > digits and just time - two digits
> >
> >
> >
> > so like this =Format(Fields!SLAResolutionDate.Value)-(Fields!
> > CreatedDate.Value), dd, hh
> >
> > but I am unsure where to stop and start the brackets to get the
> > result.
> >
> > thanks
> > jewel
> >
> >|||thanks Michael
I get the error 'd' is not declared.
I am putting this in the expression on the layout view.
thanks
jewel
On May 9, 4:29 am, Michael C <Micha...@.discussions.microsoft.com>
wrote:
> Correction: the second part should be reversed (hours - days*24):
> Format(DATEDIFF(d,
> Fields!SLAResolutionDate.Value,Fields!CreatedDate.Value),"00") & " " &
> Format(DATEDIFF(hh,
> Fields!SLAResolutionDate.Value,Fields!CreatedDate.Value)-
> DATEDIFF(d, Fields!SLAResolutionDate.Value,Fields!CreatedDate.Value) *
> 24,"00")
>
> "Michael C" wrote:
> > Not sure if this is the simplest, but it will work.
> > Try this:
> > 'This first part finds only the "whole days". The second part determines
> > the "left over hours".
> > Format(DATEDIFF(d,
> > Fields!SLAResolutionDate.Value,Fields!CreatedDate.Value),"00") & " " &
> > Format(DATEDIFF(d, Fields!SLAResolutionDate.Value,Fields!CreatedDate.Value) *
> > 24 - DATEDIFF(hh,
> > Fields!SLAResolutionDate.Value,Fields!CreatedDate.Value),"00")
> > Michael
> > "jewelfire" wrote:
> > > hi there
> > > can someone show me how to format the following please
> > > =(Fields!SLAResolutionDate.Value)-(Fields!CreatedDate.Value)
> > > these are both date/time fields - I want to format to just day - two
> > > digits and just time - two digits
> > > so like this =Format(Fields!SLAResolutionDate.Value)-(Fields!
> > > CreatedDate.Value), dd, hh
> > > but I am unsure where to stop and start the brackets to get the
> > > result.
> > > thanks
> > > jewel- Hide quoted text -
> - Show quoted text -|||Try using dd instead of just d, and be sure there are no quotes around it.
Here is the MSDN file for valid entries!
http://msdn2.microsoft.com/en-us/library/ms189794.aspx
"jewelfire" wrote:
> thanks Michael
> I get the error 'd' is not declared.
> I am putting this in the expression on the layout view.
> thanks
> jewel
>
> On May 9, 4:29 am, Michael C <Micha...@.discussions.microsoft.com>
> wrote:
> > Correction: the second part should be reversed (hours - days*24):
> >
> > Format(DATEDIFF(d,
> > Fields!SLAResolutionDate.Value,Fields!CreatedDate.Value),"00") & " " &
> > Format(DATEDIFF(hh,
> > Fields!SLAResolutionDate.Value,Fields!CreatedDate.Value)-
> > DATEDIFF(d, Fields!SLAResolutionDate.Value,Fields!CreatedDate.Value) *
> > 24,"00")
> >
> >
> >
> > "Michael C" wrote:
> > > Not sure if this is the simplest, but it will work.
> >
> > > Try this:
> > > 'This first part finds only the "whole days". The second part determines
> > > the "left over hours".
> >
> > > Format(DATEDIFF(d,
> > > Fields!SLAResolutionDate.Value,Fields!CreatedDate.Value),"00") & " " &
> > > Format(DATEDIFF(d, Fields!SLAResolutionDate.Value,Fields!CreatedDate.Value) *
> > > 24 - DATEDIFF(hh,
> > > Fields!SLAResolutionDate.Value,Fields!CreatedDate.Value),"00")
> >
> > > Michael
> >
> > > "jewelfire" wrote:
> >
> > > > hi there
> >
> > > > can someone show me how to format the following please
> >
> > > > =(Fields!SLAResolutionDate.Value)-(Fields!CreatedDate.Value)
> >
> > > > these are both date/time fields - I want to format to just day - two
> > > > digits and just time - two digits
> >
> > > > so like this =Format(Fields!SLAResolutionDate.Value)-(Fields!
> > > > CreatedDate.Value), dd, hh
> >
> > > > but I am unsure where to stop and start the brackets to get the
> > > > result.
> >
> > > > thanks
> > > > jewel- Hide quoted text -
> >
> > - Show quoted text -
>
>|||hi Michael
tried as below
=Format(DATEDIFF(dd,Fields!SLAResolutionDate.Value,Fields!
CreatedDate.Value),"00") & " " &
Format(DATEDIFF(hh,Fields!SLAResolutionDate.Value,Fields!
CreatedDate.Value)-
DATEDIFF(dd, Fields!SLAResolutionDate.Value,Fields!CreatedDate.Value)
*
24,"00")
dd - this is underlined and in red but the 2nd instance is not.
showing red on
"00") & " " &
"00")
probably something very simple! thanks for your help
thanks
jewel
On May 10, 4:24 am, Michael C <Micha...@.discussions.microsoft.com>
wrote:
> Try using dd instead of just d, and be sure there are no quotes around it.
> Here is the MSDN file for valid entries!
> http://msdn2.microsoft.com/en-us/library/ms189794.aspx
>
> "jewelfire" wrote:
> > thanks Michael
> > I get the error 'd' is not declared.
> > I am putting this in the expression on the layout view.
> > thanks
> > jewel
> > On May 9, 4:29 am, Michael C <Micha...@.discussions.microsoft.com>
> > wrote:
> > > Correction: the second part should be reversed (hours - days*24):
> > > Format(DATEDIFF(d,
> > > Fields!SLAResolutionDate.Value,Fields!CreatedDate.Value),"00") & " " &
> > > Format(DATEDIFF(hh,
> > > Fields!SLAResolutionDate.Value,Fields!CreatedDate.Value)-
> > > DATEDIFF(d, Fields!SLAResolutionDate.Value,Fields!CreatedDate.Value) *
> > > 24,"00")
> > > "Michael C" wrote:
> > > > Not sure if this is the simplest, but it will work.
> > > > Try this:
> > > > 'This first part finds only the "whole days". The second part determines
> > > > the "left over hours".
> > > > Format(DATEDIFF(d,
> > > > Fields!SLAResolutionDate.Value,Fields!CreatedDate.Value),"00") & " " &
> > > > Format(DATEDIFF(d, Fields!SLAResolutionDate.Value,Fields!CreatedDate.Value) *
> > > > 24 - DATEDIFF(hh,
> > > > Fields!SLAResolutionDate.Value,Fields!CreatedDate.Value),"00")
> > > > Michael
> > > > "jewelfire" wrote:
> > > > > hi there
> > > > > can someone show me how to format the following please
> > > > > =(Fields!SLAResolutionDate.Value)-(Fields!CreatedDate.Value)
> > > > > these are both date/time fields - I want to format to just day - two
> > > > > digits and just time - two digits
> > > > > so like this =Format(Fields!SLAResolutionDate.Value)-(Fields!
> > > > > CreatedDate.Value), dd, hh
> > > > > but I am unsure where to stop and start the brackets to get the
> > > > > result.
> > > > > thanks
> > > > > jewel- Hide quoted text -
> > > - Show quoted text -- Hide quoted text -
> - Show quoted text -

format time to 00 for a date

hi,
How do I format time of a datecolumn to 00:00:00 ?
I have a table with date column. how do I get only date for comparision, and
exclude or convert time to 00:00:00 in sql query
regards
ypulTry this and you will see what you can do:
select dateadd(dd, 0, datediff(dd, 0, getdate()))
Perayu
"ypul" <ypul@.hotmail.com> wrote in message
news:OVD9DHGuFHA.1472@.TK2MSFTNGP15.phx.gbl...
> hi,
> How do I format time of a datecolumn to 00:00:00 ?
> I have a table with date column. how do I get only date for comparision,
> and
> exclude or convert time to 00:00:00 in sql query
> regards
> ypul
>
>
>|||> How do I format time of a datecolumn to 00:00:00 ?
Formatting is usually done at the client tier, but you can handle it using
CONVERT with a style parameter, if you must.
SELECT CONVERT(CHAR(8), GETDATE(), 24)
For a complete list of styles, please see http://www.aspfaq.com/2464

> I have a table with date column. how do I get only date for comparision,
> and
> exclude or convert time to 00:00:00 in sql query
If there is an index on the column, you should always a range query. (And
if you don't care about time, you might consider not storing it.)
For example, to get all the rows with a date of 2005-09-12 (regardless of
time), you can say:
WHERE date_column >= '20050912'
AND date_column < '20050913'
To do this dynamically (e.g. always yesterday),
DECLARE @.start SMALLDATETIME
SET @.start = DATEDIFF(DAY, 0, GETDATE()) - 1
SELECT
..
WHERE date_column >= @.start
AND date_column < @.start + 1
You may be tempted to use BETWEEN but please read the following article:
http://www.aspfaq.com/2280
You maybe tempted to use localized formats for dates (e.g. d/m/y or m/d/y)
but please read the following article and the links therein:
http://www.aspfaq.com/2023|||Basically, what you'd do is to format your data on the client, but if you
insist on doing it on the server, read more here:
http://msdn.microsoft.com/library/d...br />
2f3o.asp
ML|||Thanks a lot all
"Between" article was also useful...
gr8 help
ypul
"ypul" <ypul@.hotmail.com> wrote in message
news:OVD9DHGuFHA.1472@.TK2MSFTNGP15.phx.gbl...
> hi,
> How do I format time of a datecolumn to 00:00:00 ?
> I have a table with date column. how do I get only date for comparision,
and
> exclude or convert time to 00:00:00 in sql query
> regards
> ypul
>
>
>

Format text in "parameter"

hi,
i've got a "parameter" that is of date type, but is there any way so that it
only shows the "short date" format -- don't want the time.
thanks,Maersa,
yeah, u can write a small function is custom code that takes string
as a parameter and return the same. in that function u can write
conversion functionality. and use this function is expresssion of
textbox or wherever u want.
textbox = Code.GetShortDate(Parameters!Name.Value)
and in custom code write a fuction with VB
Note: function declaration here might not be synteticaly correct.
Shared fuction GetShortDate(byval date a string) as String
write code here to convert date to DateTime and back to string
return shortdate
end function
hope this helps.
Thanks,
mahesh|||thanks mahesh,
how would i write a .NET assembly and then use it within my report ?
thanks,
"Mahesh" <gaware@.gmail.com> wrote in message
news:1121998689.809184.145380@.f14g2000cwb.googlegroups.com...
> Maersa,
> yeah, u can write a small function is custom code that takes string
> as a parameter and return the same. in that function u can write
> conversion functionality. and use this function is expresssion of
> textbox or wherever u want.
> textbox = Code.GetShortDate(Parameters!Name.Value)
> and in custom code write a fuction with VB
> Note: function declaration here might not be synteticaly correct.
> Shared fuction GetShortDate(byval date a string) as String
> write code here to convert date to DateTime and back to string
> return shortdate
> end function
> hope this helps.
> Thanks,
> mahesh
>|||There is no way you can do this. You can use the data format elsewhere and
strip the time as the other posting was saying but you cannot have report
manager change how it functions. What you can do is to use string parameter
type instead of date parameter type. That is what I do when I don't want the
time. Of course, then they could put in something that is not a date.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"maersa" <masa_iwasa@.hotmail.com> wrote in message
news:OqI9PGmjFHA.3164@.TK2MSFTNGP15.phx.gbl...
> hi,
> i've got a "parameter" that is of date type, but is there any way so that
> it only shows the "short date" format -- don't want the time.
> thanks,
>|||The code he is talking about can exist in the report itself with no outside
dependencies... Go to Report->Code from the top menu item and add your
functions there...I also have a example that does not use a code function
( if I remember correctly) at www.msbicentral.com
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"maersa" <masa_iwasa@.hotmail.com> wrote in message
news:OqI9PGmjFHA.3164@.TK2MSFTNGP15.phx.gbl...
> hi,
> i've got a "parameter" that is of date type, but is there any way so that
> it only shows the "short date" format -- don't want the time.
> thanks,
>|||I have a report that i did in visual studio that calls a stored procedure.
the @.AsOfDate is of datetime format in the procedure. In visual studio, i
made it a string. I even defaulted a string value of 12/31/2007. When i
deploy it in reporting services, it shows up as string, which is correct, but
it has a default of 12/31/2007 12:00:00 AM. Is there anyway i can get rid of
the time?
"Bruce L-C [MVP]" wrote:
> There is no way you can do this. You can use the data format elsewhere and
> strip the time as the other posting was saying but you cannot have report
> manager change how it functions. What you can do is to use string parameter
> type instead of date parameter type. That is what I do when I don't want the
> time. Of course, then they could put in something that is not a date.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "maersa" <masa_iwasa@.hotmail.com> wrote in message
> news:OqI9PGmjFHA.3164@.TK2MSFTNGP15.phx.gbl...
> > hi,
> >
> > i've got a "parameter" that is of date type, but is there any way so that
> > it only shows the "short date" format -- don't want the time.
> >
> > thanks,
> >
> >
>
>|||Can't you just use FieldName.Value.ToShortDateString ?
--
"Everyone knows something you don't know"
"Wayne Snyder" wrote:
> The code he is talking about can exist in the report itself with no outside
> dependencies... Go to Report->Code from the top menu item and add your
> functions there...I also have a example that does not use a code function
> ( if I remember correctly) at www.msbicentral.com
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> (Please respond only to the newsgroup.)
> I support the Professional Association for SQL Server ( PASS) and it's
> community of SQL Professionals.
> "maersa" <masa_iwasa@.hotmail.com> wrote in message
> news:OqI9PGmjFHA.3164@.TK2MSFTNGP15.phx.gbl...
> > hi,
> >
> > i've got a "parameter" that is of date type, but is there any way so that
> > it only shows the "short date" format -- don't want the time.
> >
> > thanks,
> >
> >
>
>|||Where are you making it a string? Click somewhere blank on your report.
Menu->Report->Report Parameters. Click on the parameter and change the data
type to string.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"deniseamat" <deniseamat@.discussions.microsoft.com> wrote in message
news:296463D8-01F3-4906-BB0B-C0F0DAFB9490@.microsoft.com...
>I have a report that i did in visual studio that calls a stored procedure.
> the @.AsOfDate is of datetime format in the procedure. In visual studio, i
> made it a string. I even defaulted a string value of 12/31/2007. When i
> deploy it in reporting services, it shows up as string, which is correct,
> but
> it has a default of 12/31/2007 12:00:00 AM. Is there anyway i can get rid
> of
> the time?
> "Bruce L-C [MVP]" wrote:
>> There is no way you can do this. You can use the data format elsewhere
>> and
>> strip the time as the other posting was saying but you cannot have report
>> manager change how it functions. What you can do is to use string
>> parameter
>> type instead of date parameter type. That is what I do when I don't want
>> the
>> time. Of course, then they could put in something that is not a date.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "maersa" <masa_iwasa@.hotmail.com> wrote in message
>> news:OqI9PGmjFHA.3164@.TK2MSFTNGP15.phx.gbl...
>> > hi,
>> >
>> > i've got a "parameter" that is of date type, but is there any way so
>> > that
>> > it only shows the "short date" format -- don't want the time.
>> >
>> > thanks,
>> >
>> >
>>

format Now()...

How do I format Now() to look like dd/MM/yyyy (without time part) as a
default value for a report parameter?Now.ToShortDateString()
or
FormatDateTime(Now(),vbShortDate)
depending on where and when you use it.
"clutch" <clutch@.discussions.microsoft.com> wrote in message
news:62B58716-A425-436F-BD52-73950DD179E8@.microsoft.com...
> How do I format Now() to look like dd/MM/yyyy (without time part) as a
> default value for a report parameter?|||You can use Today() instead of Now(), but that simply displays the full date
& time with the time at midnight.
I was able to get closer with this as the default:
=Format(Today(),"dd/MM/yyyy")
But it's producing a type mismatch error if the parameter is a DateTime.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"clutch" <clutch@.discussions.microsoft.com> wrote in message
news:62B58716-A425-436F-BD52-73950DD179E8@.microsoft.com...
> How do I format Now() to look like dd/MM/yyyy (without time part) as a
> default value for a report parameter?|||If you don't want the user to see the time then you need to change the
parameter type to string. Then what Jeff says below will work. There is no
way if you have set the parameter type to datetime for it to not show the
time.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
news:ej9Akz54EHA.2600@.TK2MSFTNGP09.phx.gbl...
> You can use Today() instead of Now(), but that simply displays the full
date
> & time with the time at midnight.
> I was able to get closer with this as the default:
> =Format(Today(),"dd/MM/yyyy")
> But it's producing a type mismatch error if the parameter is a DateTime.
> --
> Cheers,
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> "clutch" <clutch@.discussions.microsoft.com> wrote in message
> news:62B58716-A425-436F-BD52-73950DD179E8@.microsoft.com...
> > How do I format Now() to look like dd/MM/yyyy (without time part) as a
> > default value for a report parameter?
>|||Use =Today() and set the field format to d.
Patrick
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> schrieb im Newsbeitrag
news:e%23xSbL64EHA.1596@.tk2msftngp13.phx.gbl...
> If you don't want the user to see the time then you need to change the
> parameter type to string. Then what Jeff says below will work. There is no
> way if you have set the parameter type to datetime for it to not show the
> time.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
> news:ej9Akz54EHA.2600@.TK2MSFTNGP09.phx.gbl...
>> You can use Today() instead of Now(), but that simply displays the full
> date
>> & time with the time at midnight.
>> I was able to get closer with this as the default:
>> =Format(Today(),"dd/MM/yyyy")
>> But it's producing a type mismatch error if the parameter is a DateTime.
>> --
>> Cheers,
>> '(' Jeff A. Stucker
>> \
>> Business Intelligence
>> www.criadvantage.com
>> ---
>> "clutch" <clutch@.discussions.microsoft.com> wrote in message
>> news:62B58716-A425-436F-BD52-73950DD179E8@.microsoft.com...
>> > How do I format Now() to look like dd/MM/yyyy (without time part) as a
>> > default value for a report parameter?
>>
>

Format DateTime for Scatter Chart

I am making a scatter chart (not using Dundas) to plot at what time (Y-axis) and what date (X-axis) something happened. The DateTime value is coming from a database. I currently have it working if I use the Hour() for the Y-axis, and the DateValue() for the X-axis.

What I'd like to do is make it so that on the Y-axis, I can display the standard time format (i.e. 7:30am), and on the X-axis, display just the date (6/28/2007) rather then the date with midnight on it.

Thanks.

Try using =FormatDateTime()

Larry

Format DateTime for Scatter Chart

I am making a scatter chart (not using Dundas) to plot at what time (Y-axis) and what date (X-axis) something happened. The DateTime value is coming from a database. I currently have it working if I use the Hour() for the Y-axis, and the DateValue() for the X-axis.

What I'd like to do is make it so that on the Y-axis, I can display the standard time format (i.e. 7:30am), and on the X-axis, display just the date (6/28/2007) rather then the date with midnight on it.

Thanks.

Try using =FormatDateTime()

Larry

Format DateTime attribute in AS 2005

Hi,

I have an attribute a Time dimension which is of the datetime datatype. In the browser and in other tools, the displayed members are formatted like this:

dd-mm-yyyy hh:mm:ss

I want to change this format. How is this done? The format property of the attribute in the dimension editor seems to have no effect... I tried "mm/dd/yyyy" and without quotes.

Thnx... Jeroen.

Hi Jeroen,

If you customize the date format to what you want in the regional settings in the control panel, and refresh the browser, you will get the format you wanted. I've tried this out and it works.

Yan

|||

Hello Yan,

Thnx for your reply. I will try that. However, we want to implement a solution that's independent of the local user settings. For instance, we don't want the time displayed in this case. I figured this would be peanuts with AS 2005. Ofcourse, we can always do it in an extra column in the table or in the data source view.

Regards,

Jeroen

|||these are the value to use instead of "dd/mm/yyyy"

Constant

Description

DateFormat.GeneralDate

Display a date and/or time. Display a date part as a short date. If there is a time part, display it as a long time. If present, both parts display.

DateFormat.LongDate

Display a date using the long date format specified in your computer's regional settings.

DateFormat.ShortDate

Display a date using the short date format specified in your computer's regional settings.

DateFormat.LongTime

Display a time using the time format specified in your computer's regional settings.

DateFormat.ShortTime

Display a time using the 24-hour format (hh:mm).

|||

I am trying to solve the same problem.

So where do you need to set this property? I tried setting it in the NameColumn.Formatting property and in the Value.Nameformating property, but to no avail!

|||

Please could you telll me where to set this constant value?

I'm have tried in the format property but it does not work.

|||

Hello. I would recommend you to add a named calculation to your time dimension and use the TSQL-function CONVERT to transform your date to an appropriate format.

CONVERT(Char(10), GETDATE(), 112) will change a dateformat to '2007-05-25' (ISO-style)

You will find more information about this function in Books OnLine.

Try

select Getdate(), Convert(Char(10),GetDate(), 112)

in a query in management studio on the database engine.

HTH

Thomas Ivarsson

|||

Thank you. If you say that is the only solution, i will do so. But it will be very hard for me to add a named calculation to all date field in my data source view. I have about 50 date field and i was just wondering why it is not possible to set date format in the "format proterty field". The solution Mentionned by Yan is very interring for me, but it does not work. In fact, how to use VB code in AS projet?

|||

I have used the following code in the Calculations tab of Cube Designer in BIDS to acheive the formating for calculated measure. You can try doing the same for dimensions - I guess it would work. Pl change the format string to appropriate one.

FORMAT_STRING([Measures].[MyDimName]) = "#,#;(#,#)";

Format DateTime attribute in AS 2005

Hi,

I have an attribute a Time dimension which is of the datetime datatype. In the browser and in other tools, the displayed members are formatted like this:

dd-mm-yyyy hh:mm:ss

I want to change this format. How is this done? The format property of the attribute in the dimension editor seems to have no effect... I tried "mm/dd/yyyy" and without quotes.

Thnx... Jeroen.

Hi Jeroen,

If you customize the date format to what you want in the regional settings in the control panel, and refresh the browser, you will get the format you wanted. I've tried this out and it works.

Yan

|||

Hello Yan,

Thnx for your reply. I will try that. However, we want to implement a solution that's independent of the local user settings. For instance, we don't want the time displayed in this case. I figured this would be peanuts with AS 2005. Ofcourse, we can always do it in an extra column in the table or in the data source view.

Regards,

Jeroen

|||these are the value to use instead of "dd/mm/yyyy"

Constant

Description

DateFormat.GeneralDate

Display a date and/or time. Display a date part as a short date. If there is a time part, display it as a long time. If present, both parts display.

DateFormat.LongDate

Display a date using the long date format specified in your computer's regional settings.

DateFormat.ShortDate

Display a date using the short date format specified in your computer's regional settings.

DateFormat.LongTime

Display a time using the time format specified in your computer's regional settings.

DateFormat.ShortTime

Display a time using the 24-hour format (hh:mm).

|||

I am trying to solve the same problem.

So where do you need to set this property? I tried setting it in the NameColumn.Formatting property and in the Value.Nameformating property, but to no avail!

|||

Please could you telll me where to set this constant value?

I'm have tried in the format property but it does not work.

|||

Hello. I would recommend you to add a named calculation to your time dimension and use the TSQL-function CONVERT to transform your date to an appropriate format.

CONVERT(Char(10), GETDATE(), 112) will change a dateformat to '2007-05-25' (ISO-style)

You will find more information about this function in Books OnLine.

Try

select Getdate(), Convert(Char(10),GetDate(), 112)

in a query in management studio on the database engine.

HTH

Thomas Ivarsson

|||

Thank you. If you say that is the only solution, i will do so. But it will be very hard for me to add a named calculation to all date field in my data source view. I have about 50 date field and i was just wondering why it is not possible to set date format in the "format proterty field". The solution Mentionned by Yan is very interring for me, but it does not work. In fact, how to use VB code in AS projet?

|||

I have used the following code in the Calculations tab of Cube Designer in BIDS to acheive the formating for calculated measure. You can try doing the same for dimensions - I guess it would work. Pl change the format string to appropriate one.

FORMAT_STRING([Measures].[MyDimName]) = "#,#;(#,#)";

|||

Hi Thomas,

And thanks for this helpful answer : Date labels are well formatted with this conversion.

However, an OLAP client such as Excel 2007 will not recognize the attribute datatype and thus will not display the "date filters" for that dimension.

Moreover, an error occurs when setting the DataType of the NameColumn to something else than WChar (Date, for instance...)

SSAS allows to use a ValueColumn for the attribute, which I set to the CONVERTed date, in both Date and WChar formats, but the client still does not recognize it as a date field...

Any idea ?

Thank you

Greg

|||

Hi all,

Updating the thread with great news :

To have the Date attribute of a Time hierarchy recognized as a Date field in Excel pivot tables and display the Date Filtern, you have to set the ValueColumn of the key attribute (not the Date attribute) to the Date attribute column. Typically, the key attribute is an int32, this is why you don't think about making it point to the date column.

HTH

Greg

|||I've been trying to get this to function right for months to no avail. I've tried setting the key's value column to the date attribute column, It didn't work (excel still thinks my dates are text). My key was already a date type, perhaps that's the issue?|||

Hello Greg. You can set the name property to the named calculation that I have suggested but keep the key column to the date time data type. This is how I hve done it in SSAS2005.

HTH

Thomas Ivarsson

|||The problem I've had with doing that is that Excel 2007 still thinks that the value is text, it doesn't realize it's a date value...hence all the date filters won't work (and graphing by date is not done correctly).|||

Hello Jamie! I have tried this on the time dimension in the Adventure Works cube projects and it works correctly. I have used a proper dateTime column as the key and the output of the Convert function as the name column.

Do not forget to set the order by property to key column.

Regards

Thomas Ivarsson

Format DateTime attribute in AS 2005

Hi,

I have an attribute a Time dimension which is of the datetime datatype. In the browser and in other tools, the displayed members are formatted like this:

dd-mm-yyyy hh:mm:ss

I want to change this format. How is this done? The format property of the attribute in the dimension editor seems to have no effect... I tried "mm/dd/yyyy" and without quotes.

Thnx... Jeroen.

Hi Jeroen,

If you customize the date format to what you want in the regional settings in the control panel, and refresh the browser, you will get the format you wanted. I've tried this out and it works.

Yan

|||

Hello Yan,

Thnx for your reply. I will try that. However, we want to implement a solution that's independent of the local user settings. For instance, we don't want the time displayed in this case. I figured this would be peanuts with AS 2005. Ofcourse, we can always do it in an extra column in the table or in the data source view.

Regards,

Jeroen

|||these are the value to use instead of "dd/mm/yyyy"

Constant

Description

DateFormat.GeneralDate

Display a date and/or time. Display a date part as a short date. If there is a time part, display it as a long time. If present, both parts display.

DateFormat.LongDate

Display a date using the long date format specified in your computer's regional settings.

DateFormat.ShortDate

Display a date using the short date format specified in your computer's regional settings.

DateFormat.LongTime

Display a time using the time format specified in your computer's regional settings.

DateFormat.ShortTime

Display a time using the 24-hour format (hh:mm).

|||

I am trying to solve the same problem.

So where do you need to set this property? I tried setting it in the NameColumn.Formatting property and in the Value.Nameformating property, but to no avail!

|||

Please could you telll me where to set this constant value?

I'm have tried in the format property but it does not work.

|||

Hello. I would recommend you to add a named calculation to your time dimension and use the TSQL-function CONVERT to transform your date to an appropriate format.

CONVERT(Char(10), GETDATE(), 112) will change a dateformat to '2007-05-25' (ISO-style)

You will find more information about this function in Books OnLine.

Try

select Getdate(), Convert(Char(10),GetDate(), 112)

in a query in management studio on the database engine.

HTH

Thomas Ivarsson

|||

Thank you. If you say that is the only solution, i will do so. But it will be very hard for me to add a named calculation to all date field in my data source view. I have about 50 date field and i was just wondering why it is not possible to set date format in the "format proterty field". The solution Mentionned by Yan is very interring for me, but it does not work. In fact, how to use VB code in AS projet?

|||

I have used the following code in the Calculations tab of Cube Designer in BIDS to acheive the formating for calculated measure. You can try doing the same for dimensions - I guess it would work. Pl change the format string to appropriate one.

FORMAT_STRING([Measures].[MyDimName]) = "#,#;(#,#)";

Format DateTime attribute in AS 2005

Hi,

I have an attribute a Time dimension which is of the datetime datatype. In the browser and in other tools, the displayed members are formatted like this:

dd-mm-yyyy hh:mm:ss

I want to change this format. How is this done? The format property of the attribute in the dimension editor seems to have no effect... I tried "mm/dd/yyyy" and without quotes.

Thnx... Jeroen.

Hi Jeroen,

If you customize the date format to what you want in the regional settings in the control panel, and refresh the browser, you will get the format you wanted. I've tried this out and it works.

Yan

|||

Hello Yan,

Thnx for your reply. I will try that. However, we want to implement a solution that's independent of the local user settings. For instance, we don't want the time displayed in this case. I figured this would be peanuts with AS 2005. Ofcourse, we can always do it in an extra column in the table or in the data source view.

Regards,

Jeroen

|||these are the value to use instead of "dd/mm/yyyy"

Constant

Description

DateFormat.GeneralDate

Display a date and/or time. Display a date part as a short date. If there is a time part, display it as a long time. If present, both parts display.

DateFormat.LongDate

Display a date using the long date format specified in your computer's regional settings.

DateFormat.ShortDate

Display a date using the short date format specified in your computer's regional settings.

DateFormat.LongTime

Display a time using the time format specified in your computer's regional settings.

DateFormat.ShortTime

Display a time using the 24-hour format (hh:mm).

|||

I am trying to solve the same problem.

So where do you need to set this property? I tried setting it in the NameColumn.Formatting property and in the Value.Nameformating property, but to no avail!

|||

Please could you telll me where to set this constant value?

I'm have tried in the format property but it does not work.

|||

Hello. I would recommend you to add a named calculation to your time dimension and use the TSQL-function CONVERT to transform your date to an appropriate format.

CONVERT(Char(10), GETDATE(), 112) will change a dateformat to '2007-05-25' (ISO-style)

You will find more information about this function in Books OnLine.

Try

select Getdate(), Convert(Char(10),GetDate(), 112)

in a query in management studio on the database engine.

HTH

Thomas Ivarsson

|||

Thank you. If you say that is the only solution, i will do so. But it will be very hard for me to add a named calculation to all date field in my data source view. I have about 50 date field and i was just wondering why it is not possible to set date format in the "format proterty field". The solution Mentionned by Yan is very interring for me, but it does not work. In fact, how to use VB code in AS projet?

|||

I have used the following code in the Calculations tab of Cube Designer in BIDS to acheive the formating for calculated measure. You can try doing the same for dimensions - I guess it would work. Pl change the format string to appropriate one.

FORMAT_STRING([Measures].[MyDimName]) = "#,#;(#,#)";

Format date time

declare @.d smalldatetime
declare @.e varchar(8)
set @.d='1/8/2005'
set @.e=.........?
I wanna set @.e with format 'YYYYMMDD' of @.d. How?One method:
SET @.e = CONVERT(varchar(8), @.d, 112)
You also might consider using the YYYYMMDD format in your date literal
strings in order to avoid ambiguity.
Hope this helps.
Dan Guzman
SQL Server MVP
"Bpk. Adi Wira Kusuma" <adi_wira_kusuma@.yahoo.com.sg> wrote in message
news:u9UckoYkFHA.1044@.tk2msftngp13.phx.gbl...
> declare @.d smalldatetime
> declare @.e varchar(8)
> set @.d='1/8/2005'
> set @.e=.........?
> I wanna set @.e with format 'YYYYMMDD' of @.d. How?
>