Date Formats/Styles

Hi, Is there any list available of style codes used in formatting dates
e.g Convert(Varchar(11),GetDate(),106)
I cant find these style codes or this method documented in BOL.
I want to format a date like this: 09 August 2005. How?
Thanks.
hals_leftBetter to do the formating in the client side.
CAST and CONVERT
http://msdn.microsoft.com/library/d...br />
2f3o.asp
AMB
"hals_left" wrote:

> Hi, Is there any list available of style codes used in formatting dates
> e.g Convert(Varchar(11),GetDate(),106)
> I cant find these style codes or this method documented in BOL.
> I want to format a date like this: 09 August 2005. How?
> Thanks.
> hals_left
>|||Did you look up CONVERT in BOL? But there is not a format like that
available. You could write a pretty easy function to return the data like
that using DATEPART and DATENAME, but unless you really have to use SQL, as
Alejandro said, use the client to change the appearance of the data.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"hals_left" <cc900630@.ntu.ac.uk> wrote in message
news:1123616217.059593.295580@.g44g2000cwa.googlegroups.com...
> Hi, Is there any list available of style codes used in formatting dates
> e.g Convert(Varchar(11),GetDate(),106)
> I cant find these style codes or this method documented in BOL.
> I want to format a date like this: 09 August 2005. How?
> Thanks.
> hals_left
>

Date formats in SSIS

Hi once again guys,

I seem to be struggling with everything in SSIS these days!

I have a datetime field and I want to convert it to the following format in my derived column component :

yyyy.mm.dd

I also have another datetime field but this time I am only interested in the time values and I want to get :

HH:MM

How do I go about doing this in the SSIS expression builder?

Please help.

Sometime is easier to perform this kind of transforms right on the source query; but that depends on your level of confidence when writing SQL Vs SSIS expressions. In general I find SQL syntax more readable than its equivalent of SSIS expression.|||

Hi Rafael,

You are correct of course.

I do find it a lot easier to do this kind of thing in T-SQL but I am trying to use as much of SSIS as possible.

T-SQL :

convert(char(5), getdate(), 114) gets me the time in format HH:MM

convert(varchar, getdate(), 102) gets me the date format I desire

BUT this is about SSIS expressions not T-SQL for me :)

Anyway, in the end I used a series of DATEPART functions to get the year, month and days and concatenated together to form my YYYY.MM.DD string.

Thanks for your input.

|||

dreameR.78 wrote:

the year, month and days and concatenated together to form my YYYY.MM.DD string.

I was just about to suggest that :)

its the best way when using expressions.

-Jamie

|||

Hi Jamie,

Long time no see!

Could you please tell me why the following expression doesn't get validated?!!!!!! It's driving me crazy!!!!!

LEN((dt_str,2,1252)DATEPART("mi",btg_opty_start_datetime)) == 1 ? "0" + (dt_str,2,1252)DATEPART("mi",btg_opty_start_datetime) : (dt_str,2,1252)DATEPART("mi",btg_opty_start_datetime) + ":" +

LEN((dt_str,2,1252)DATEPART("ss",btg_opty_start_datetime)) == 1 ? "0" + (dt_str,2,1252)DATEPART("ss",btg_opty_start_datetime) : (dt_str,2,1252)DATEPART("ss",btg_opty_start_datetime)

|||Your first "else" has a string component in it (":") and cannot be evaluated to the integer of 1.

LEN((dt_str,2,1252)DATEPART("mi",btg_opty_start_datetime)) == 1 ?

"0" + (dt_str,2,1252)DATEPART("mi",btg_opty_start_datetime) :

(dt_str,2,1252)DATEPART("mi",btg_opty_start_datetime) + ":" + LEN((dt_str,2,1252)DATEPART("ss",btg_opty_start_datetime)) == 1 ?

"0" + (dt_str,2,1252)DATEPART("ss",btg_opty_start_datetime) :

(dt_str,2,1252)DATEPART("ss",btg_opty_start_datetime)|||

dreameR.78 wrote:

Hi Jamie,

Long time no see!

Could you please tell me why the following expression doesn't get validated?!!!!!! It's driving me crazy!!!!!

LEN((dt_str,2,1252)DATEPART("mi",btg_opty_start_datetime)) == 1 ? "0" + (dt_str,2,1252)DATEPART("mi",btg_opty_start_datetime) : (dt_str,2,1252)DATEPART("mi",btg_opty_start_datetime) + ":" +

LEN((dt_str,2,1252)DATEPART("ss",btg_opty_start_datetime)) == 1 ? "0" + (dt_str,2,1252)DATEPART("ss",btg_opty_start_datetime) : (dt_str,2,1252)DATEPART("ss",btg_opty_start_datetime)

Error message?

|||

Phil Brammer wrote:

Your first "else" has a string component in it (":") and cannot be evaluated to the integer of 1.

LEN((dt_str,2,1252)DATEPART("mi",btg_opty_start_datetime)) == 1 ?

"0" + (dt_str,2,1252)DATEPART("mi",btg_opty_start_datetime) :

(dt_str,2,1252)DATEPART("mi",btg_opty_start_datetime) + ":" + LEN((dt_str,2,1252)DATEPART("ss",btg_opty_start_datetime)) == 1 ?

"0" + (dt_str,2,1252)DATEPART("ss",btg_opty_start_datetime) :

(dt_str,2,1252)DATEPART("ss",btg_opty_start_datetime)

Perhaps parenthesis around the last if-then-else statement?

LEN((dt_str,2,1252)DATEPART("mi",btg_opty_start_datetime)) == 1 ?

"0" + (dt_str,2,1252)DATEPART("mi",btg_opty_start_datetime) :

(dt_str,2,1252)DATEPART("mi",btg_opty_start_datetime) + ":" + (LEN((dt_str,2,1252)DATEPART("ss",btg_opty_start_datetime)) == 1 ?

"0" + (dt_str,2,1252)DATEPART("ss",btg_opty_start_datetime) :

(dt_str,2,1252)DATEPART("ss",btg_opty_start_datetime))|||

Hi Phil,

Actually I made a formatting mistake,

The exprerssion is all the 4 lines!

LEN((dt_str,2,1252)DATEPART("mi",btg_opty_start_datetime)) == 1 ? "0" + (dt_str,2,1252)DATEPART("mi",btg_opty_start_datetime) : (dt_str,2,1252)DATEPART("mi",btg_opty_start_datetime)

gets validated correctly.

All the above is aying is get the minues datepart from the datetime field and if it is on digit value then prefix it with a zero else return it the way it is.

The second part of the xpression is pretty much idetical to the above only it rteurns the seconds datepart from my datetime field.

I am concatenating : so that my time appears as MM:SS

Should be simple really but my expression is highlighted in red for some reason. But as I said, the above code on it's on, i.e. just the MM works fine, it's only when I include the second part that the expression fails to evaluate.

|||Hi again Phil, that's exactly what I tried before posting this but even though the expression evaluated. The return result was not showing the seconds. Not even a doble zero!|||

Hi Jamie,

The error message isn't very specific to be honest, but the expression is highlihted in red so obviously it doesn't like something about it.

I'm getting depressed now.

|||You have to surround your conditional statements with parenthesis and then concatenate them with the "+" symbol:

(LEN((dt_str,2,1252)DATEPART("mi",btg_opty_start_datetime)) == 1 ? "0" + (dt_str,2,1252)DATEPART("mi",btg_opty_start_datetime) : (dt_str,2,1252)DATEPART("mi",btg_opty_start_datetime))

+ ":" +

(LEN((dt_str,2,1252)DATEPART("ss",btg_opty_start_datetime)) == 1 ? "0" + (dt_str,2,1252)DATEPART("ss",btg_opty_start_datetime) : (dt_str,2,1252)DATEPART("ss",btg_opty_start_datetime))

Also, ensure that the output type of the column is string.|||

Hi Phil,

Ah sport on. Well done my friend! It's now working.

I only had brackets for the second part but when I also included them for the first part, the result came out as expected.

A good way to end the week.

Have a good weekend all.

Date formats in SQL

I need to compare a date field in a WHERE clause and I'm finding it very
difficult to do so.
I have a date stored in tables as integers like so:
20060317
etc
I need to compare todays date (less a day) to get the data out of the
table...so my query looks like this:
WHERE
IDPERI > 200601 and ididat =
case when UPPER(datename(dw,getdate())) = 'MONDAY'
then
cast(dateadd(dd,-3,getdate()) as int)
--and ididat < 20060320
else
cast(dateadd(dd,-1,getdate()) as int)
--and ididat < 20060320
end
However, the GetDate() statements between the then and else clauses return
the following:
2006-03-17 15:08:15.663
Now I just want the date to be yyyymmdd so I can compare. I have tried just
about everything I know and I cannot get the format to be yyyymmdd.
Thx
BillyBil, CAST your integer AS CHAR(8) il will workd fine.
A +
Billy a écrit :
> I need to compare a date field in a WHERE clause and I'm finding it very
> difficult to do so.
> I have a date stored in tables as integers like so:
> 20060317
> etc
> I need to compare todays date (less a day) to get the data out of the
> table...so my query looks like this:
> WHERE
> IDPERI > 200601 and ididat =
> case when UPPER(datename(dw,getdate())) = 'MONDAY'
> then
> cast(dateadd(dd,-3,getdate()) as int)
> --and ididat < 20060320
> else
> cast(dateadd(dd,-1,getdate()) as int)
> --and ididat < 20060320
> end
> However, the GetDate() statements between the then and else clauses return
> the following:
> 2006-03-17 15:08:15.663
> Now I just want the date to be yyyymmdd so I can compare. I have tried jus
t
> about everything I know and I cannot get the format to be yyyymmdd.
> Thx
> Billy
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************|||SET @.DT_CURDATE = CAST(FLOOR(CAST(getdate() AS DECIMAL(15, 8))) AS DATETIME)
And then use @.DT_CURDATE instead of getdate()
Hope that helps
"Billy" wrote:

> I need to compare a date field in a WHERE clause and I'm finding it very
> difficult to do so.
> I have a date stored in tables as integers like so:
> 20060317
> etc
> I need to compare todays date (less a day) to get the data out of the
> table...so my query looks like this:
> WHERE
> IDPERI > 200601 and ididat =
> case when UPPER(datename(dw,getdate())) = 'MONDAY'
> then
> cast(dateadd(dd,-3,getdate()) as int)
> --and ididat < 20060320
> else
> cast(dateadd(dd,-1,getdate()) as int)
> --and ididat < 20060320
> end
> However, the GetDate() statements between the then and else clauses return
> the following:
> 2006-03-17 15:08:15.663
> Now I just want the date to be yyyymmdd so I can compare. I have tried jus
t
> about everything I know and I cannot get the format to be yyyymmdd.
> Thx
> Billy|||On Mon, 20 Mar 2006 07:57:32 -0800, Billy wrote:

>I need to compare a date field in a WHERE clause and I'm finding it very
>difficult to do so.
>I have a date stored in tables as integers like so:
>20060317
Hi Billy,
Don't do this.
Change your DB. Store the date as a datetime or a smalldatetime. It'll
save you lots of headaches.
(snip)
>However, the GetDate() statements between the then and else clauses return
>the following:
>2006-03-17 15:08:15.663
>Now I just want the date to be yyyymmdd so I can compare. I have tried just
>about everything I know and I cannot get the format to be yyyymmdd.
Ugly kludge:
SELECT CAST(CONVERT(char(8), CURRENT_TIMESTAMP, 112) AS int)
Hugo Kornelis, SQL Server MVP

Date Formats In Analysis Services 2005

Hello,

I've been having an issue trying to display anything other then a Long Date in Analysis Services. I've used the Server based Time Dimension and I've also tried creating a dimension for a User Created Date Table.

It seems no matter what I do the date attribute always displays as a long date.

Any Ideas?

If you need more flexibility (and most applications), what I typically recommend is to create a standalone time dimension table. For example, if you look at Project REAL, we use surrogate keys for all of our dimensions except time -- and for it we use YYYYMMDD as an integer. And use it as the surrogate key between the fact tables and a standalone time dimension.

See http://www.microsoft.com/sql/bi/ProjectREAL

_-_-_

Date Formats and local IE settings

We have reports available on the web that take begin and end dates in US
format (i.e. 08/02/2004 for August 2, 2004). When our international
customers try to render the report the dates get converted to the date
setting they have on their local IE, so the date then looks like 02/08/2004
for August 2, 2004. The report then pulls back data for February 8, 2004.
Does anyone know of a work around for this?SP1 solves this with rs:ParameterLanguage URL access parameter. See:
http://download.microsoft.com/download/7/f/b/7fb1a251-13ad-404c-a034-10d79ddaa510/SP1Readme_EN.htm#_new_url_access_parameters
Just install SP1 and it will work, you don't need to do any changes in the
reports.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jessica C" <jesscobbe@.hotmail.com> wrote in message
news:%23ORisPleEHA.592@.TK2MSFTNGP11.phx.gbl...
> We have reports available on the web that take begin and end dates in US
> format (i.e. 08/02/2004 for August 2, 2004). When our international
> customers try to render the report the dates get converted to the date
> setting they have on their local IE, so the date then looks like
02/08/2004
> for August 2, 2004. The report then pulls back data for February 8, 2004.
> Does anyone know of a work around for this?
>|||Thanks for the quick response... I didn't know about that....
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:OzE4e4leEHA.3928@.TK2MSFTNGP11.phx.gbl...
> SP1 solves this with rs:ParameterLanguage URL access parameter. See:
>
http://download.microsoft.com/download/7/f/b/7fb1a251-13ad-404c-a034-10d79ddaa510/SP1Readme_EN.htm#_new_url_access_parameters
> Just install SP1 and it will work, you don't need to do any changes in the
> reports.
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Jessica C" <jesscobbe@.hotmail.com> wrote in message
> news:%23ORisPleEHA.592@.TK2MSFTNGP11.phx.gbl...
> > We have reports available on the web that take begin and end dates in US
> > format (i.e. 08/02/2004 for August 2, 2004). When our international
> > customers try to render the report the dates get converted to the date
> > setting they have on their local IE, so the date then looks like
> 02/08/2004
> > for August 2, 2004. The report then pulls back data for February 8,
2004.
> > Does anyone know of a work around for this?
> >
> >
>|||We tried setting rs:ParameterLanguage=en-US in the URL so international
users dates would stay in the MM/DD/YYYY format but it isn't working. We
have SP1 installed. Do you have any suggestions? Please help, our
customers in the UK would like to be able to view their reports. Thanks!
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:OzE4e4leEHA.3928@.TK2MSFTNGP11.phx.gbl...
> SP1 solves this with rs:ParameterLanguage URL access parameter. See:
>
http://download.microsoft.com/download/7/f/b/7fb1a251-13ad-404c-a034-10d79ddaa510/SP1Readme_EN.htm#_new_url_access_parameters
> Just install SP1 and it will work, you don't need to do any changes in the
> reports.
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Jessica C" <jesscobbe@.hotmail.com> wrote in message
> news:%23ORisPleEHA.592@.TK2MSFTNGP11.phx.gbl...
> > We have reports available on the web that take begin and end dates in US
> > format (i.e. 08/02/2004 for August 2, 2004). When our international
> > customers try to render the report the dates get converted to the date
> > setting they have on their local IE, so the date then looks like
> 02/08/2004
> > for August 2, 2004. The report then pulls back data for February 8,
2004.
> > Does anyone know of a work around for this?
> >
> >
>|||I assume you are talking about the date format in the parameters area. You
need to make sure the report language (a report property) is set to english
as well. Otherwise, it will adapt to the user locale.
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jessica C" <jesscobbe@.hotmail.com> wrote in message
news:eef80tneEHA.3028@.TK2MSFTNGP12.phx.gbl...
> We tried setting rs:ParameterLanguage=en-US in the URL so international
> users dates would stay in the MM/DD/YYYY format but it isn't working. We
> have SP1 installed. Do you have any suggestions? Please help, our
> customers in the UK would like to be able to view their reports. Thanks!
>
> "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
> news:OzE4e4leEHA.3928@.TK2MSFTNGP11.phx.gbl...
>> SP1 solves this with rs:ParameterLanguage URL access parameter. See:
> http://download.microsoft.com/download/7/f/b/7fb1a251-13ad-404c-a034-10d79ddaa510/SP1Readme_EN.htm#_new_url_access_parameters
>> Just install SP1 and it will work, you don't need to do any changes in
>> the
>> reports.
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>> "Jessica C" <jesscobbe@.hotmail.com> wrote in message
>> news:%23ORisPleEHA.592@.TK2MSFTNGP11.phx.gbl...
>> > We have reports available on the web that take begin and end dates in
>> > US
>> > format (i.e. 08/02/2004 for August 2, 2004). When our international
>> > customers try to render the report the dates get converted to the date
>> > setting they have on their local IE, so the date then looks like
>> 02/08/2004
>> > for August 2, 2004. The report then pulls back data for February 8,
> 2004.
>> > Does anyone know of a work around for this?
>> >
>> >
>>
>

Date Formats

Hi all,
In our whole system (Sql server, forms applications etc) I have configured
our date formats to be dd/mm/yyyy, as this is the easiest for my users. My
problem is that Reporting services only respond to the format mm/dd/yyyy
... How can I configure reporting services to only use the format
dd/mm/yyyy?
Thanks alot in advance
RThere is a language property for the report and a textbox. You can for
example select English (United Kingdom), and the date will display as
dd/mm/yy. There is also an International Settings option in Tools,Options of
the development enviromnent.
The report property setting overrides the global setting, and the textbox
property overrides the report property.
The language property can also be left as Default on the report or textbox.
If left as Default, the more global settings takes effect.
Finally, you can use custom expressions such as
=Format(Fields!MyDateField.Value, "dd/MMM/yy")
HTH
Charles Kangai, MCT, MCDBA
"Rudi Groenewald" wrote:
> Hi all,
> In our whole system (Sql server, forms applications etc) I have configured
> our date formats to be dd/mm/yyyy, as this is the easiest for my users. My
> problem is that Reporting services only respond to the format mm/dd/yyyy
> .... How can I configure reporting services to only use the format
> dd/mm/yyyy?
> Thanks alot in advance
> R
>
>|||Hi
I'm using the product in the UK so the international settings are important.
On the Tools, Options route I only get the choice of 'English (United States)
or 'Same as Microsoft Office (Language Neutral)'. I'm not sure if this is
because I'm an MSDN subscriber or not.
I'm not keen on having to manually change the properties in every field on a
report so I can get dates, currencies etc in the right format. Is there a way
of using the machine regional settings as is done by most other products?
If this is just a problem because I'm using an MSDN version then I'll have
to bite the bullet and get rid of my MSDN developer subscription.
Cheers
Dave
"Charles Kangai" wrote:
> There is a language property for the report and a textbox. You can for
> example select English (United Kingdom), and the date will display as
> dd/mm/yy. There is also an International Settings option in Tools,Options of
> the development enviromnent.
> The report property setting overrides the global setting, and the textbox
> property overrides the report property.
> The language property can also be left as Default on the report or textbox.
> If left as Default, the more global settings takes effect.
> Finally, you can use custom expressions such as
> =Format(Fields!MyDateField.Value, "dd/MMM/yy")
> HTH
> Charles Kangai, MCT, MCDBA
> "Rudi Groenewald" wrote:
> > Hi all,
> >
> > In our whole system (Sql server, forms applications etc) I have configured
> > our date formats to be dd/mm/yyyy, as this is the easiest for my users. My
> > problem is that Reporting services only respond to the format mm/dd/yyyy
> > .... How can I configure reporting services to only use the format
> > dd/mm/yyyy?
> >
> > Thanks alot in advance
> >
> > R
> >
> >
> >|||There is no need to change the Language property of every field. Just change
it for the report. The Language property of the report takes effect unless
you change it at textbox level. Textbox setting overrides Report setting.
You are obviously aware of the Microsoft Office language setting in Tools,
Options. Have you tried using it and seeing whether new reports automatically
take the language that is set in Microsoft office? I am also in the UK, but
unfortunately have the US language installed as I am working for a US company.
Failing that, use the Report language property and not have to change it for
each textbox.
Cheers
Charles Kangai, MCT, MCDBA
"Dave Phillimore" wrote:
> Hi
> I'm using the product in the UK so the international settings are important.
> On the Tools, Options route I only get the choice of 'English (United States)
> or 'Same as Microsoft Office (Language Neutral)'. I'm not sure if this is
> because I'm an MSDN subscriber or not.
> I'm not keen on having to manually change the properties in every field on a
> report so I can get dates, currencies etc in the right format. Is there a way
> of using the machine regional settings as is done by most other products?
> If this is just a problem because I'm using an MSDN version then I'll have
> to bite the bullet and get rid of my MSDN developer subscription.
> Cheers
> Dave
> "Charles Kangai" wrote:
> > There is a language property for the report and a textbox. You can for
> > example select English (United Kingdom), and the date will display as
> > dd/mm/yy. There is also an International Settings option in Tools,Options of
> > the development enviromnent.
> >
> > The report property setting overrides the global setting, and the textbox
> > property overrides the report property.
> >
> > The language property can also be left as Default on the report or textbox.
> > If left as Default, the more global settings takes effect.
> >
> > Finally, you can use custom expressions such as
> > =Format(Fields!MyDateField.Value, "dd/MMM/yy")
> >
> > HTH
> >
> > Charles Kangai, MCT, MCDBA
> >
> > "Rudi Groenewald" wrote:
> >
> > > Hi all,
> > >
> > > In our whole system (Sql server, forms applications etc) I have configured
> > > our date formats to be dd/mm/yyyy, as this is the easiest for my users. My
> > > problem is that Reporting services only respond to the format mm/dd/yyyy
> > > .... How can I configure reporting services to only use the format
> > > dd/mm/yyyy?
> > >
> > > Thanks alot in advance
> > >
> > > R
> > >
> > >
> > >|||Hello All,
This message appears to be related to a problem I have with date formats,
however the solution given only makes the report display in one format or
another. My problem is that I have users all over the world that want to
display the same report. I would like to have the same report display for
them all without having to specify one language over another and have it
automatically handled by Reporting Services. Does anyone know of a way to do
this? or am I stuck creating multiple reports, one for standard date format
and the other for each different format?
Thanks
"Charles Kangai" wrote:
> There is no need to change the Language property of every field. Just change
> it for the report. The Language property of the report takes effect unless
> you change it at textbox level. Textbox setting overrides Report setting.
> You are obviously aware of the Microsoft Office language setting in Tools,
> Options. Have you tried using it and seeing whether new reports automatically
> take the language that is set in Microsoft office? I am also in the UK, but
> unfortunately have the US language installed as I am working for a US company.
> Failing that, use the Report language property and not have to change it for
> each textbox.
> Cheers
> Charles Kangai, MCT, MCDBA
> "Dave Phillimore" wrote:
> > Hi
> >
> > I'm using the product in the UK so the international settings are important.
> > On the Tools, Options route I only get the choice of 'English (United States)
> > or 'Same as Microsoft Office (Language Neutral)'. I'm not sure if this is
> > because I'm an MSDN subscriber or not.
> >
> > I'm not keen on having to manually change the properties in every field on a
> > report so I can get dates, currencies etc in the right format. Is there a way
> > of using the machine regional settings as is done by most other products?
> >
> > If this is just a problem because I'm using an MSDN version then I'll have
> > to bite the bullet and get rid of my MSDN developer subscription.
> >
> > Cheers
> > Dave
> >
> > "Charles Kangai" wrote:
> >
> > > There is a language property for the report and a textbox. You can for
> > > example select English (United Kingdom), and the date will display as
> > > dd/mm/yy. There is also an International Settings option in Tools,Options of
> > > the development enviromnent.
> > >
> > > The report property setting overrides the global setting, and the textbox
> > > property overrides the report property.
> > >
> > > The language property can also be left as Default on the report or textbox.
> > > If left as Default, the more global settings takes effect.
> > >
> > > Finally, you can use custom expressions such as
> > > =Format(Fields!MyDateField.Value, "dd/MMM/yy")
> > >
> > > HTH
> > >
> > > Charles Kangai, MCT, MCDBA
> > >
> > > "Rudi Groenewald" wrote:
> > >
> > > > Hi all,
> > > >
> > > > In our whole system (Sql server, forms applications etc) I have configured
> > > > our date formats to be dd/mm/yyyy, as this is the easiest for my users. My
> > > > problem is that Reporting services only respond to the format mm/dd/yyyy
> > > > .... How can I configure reporting services to only use the format
> > > > dd/mm/yyyy?
> > > >
> > > > Thanks alot in advance
> > > >
> > > > R
> > > >
> > > >
> > > >|||Apologies in advance if I have not properly understood. If you want the users
to see exactly the same thing, then you hard-code the date format in the
Format property of the field, e.g. dd-MMM-yyyy. If you want their Regional
Settings to be used, you can just enter a single letter "d" in the Format
property or "D". "d" stands for short date format as indicated in Regional
Settings; "D" stands for long date.
As far as I can see you can do anything you want: have the date display
according to the user's locale, or have a fixed display irrespective of
user's locale.
Charles Kangai, MCT, MCDBA
"Support Mechanic" wrote:
> Hello All,
> This message appears to be related to a problem I have with date formats,
> however the solution given only makes the report display in one format or
> another. My problem is that I have users all over the world that want to
> display the same report. I would like to have the same report display for
> them all without having to specify one language over another and have it
> automatically handled by Reporting Services. Does anyone know of a way to do
> this? or am I stuck creating multiple reports, one for standard date format
> and the other for each different format?
> Thanks
>
> "Charles Kangai" wrote:
> > There is no need to change the Language property of every field. Just change
> > it for the report. The Language property of the report takes effect unless
> > you change it at textbox level. Textbox setting overrides Report setting.
> >
> > You are obviously aware of the Microsoft Office language setting in Tools,
> > Options. Have you tried using it and seeing whether new reports automatically
> > take the language that is set in Microsoft office? I am also in the UK, but
> > unfortunately have the US language installed as I am working for a US company.
> >
> > Failing that, use the Report language property and not have to change it for
> > each textbox.
> >
> > Cheers
> >
> > Charles Kangai, MCT, MCDBA
> >
> > "Dave Phillimore" wrote:
> >
> > > Hi
> > >
> > > I'm using the product in the UK so the international settings are important.
> > > On the Tools, Options route I only get the choice of 'English (United States)
> > > or 'Same as Microsoft Office (Language Neutral)'. I'm not sure if this is
> > > because I'm an MSDN subscriber or not.
> > >
> > > I'm not keen on having to manually change the properties in every field on a
> > > report so I can get dates, currencies etc in the right format. Is there a way
> > > of using the machine regional settings as is done by most other products?
> > >
> > > If this is just a problem because I'm using an MSDN version then I'll have
> > > to bite the bullet and get rid of my MSDN developer subscription.
> > >
> > > Cheers
> > > Dave
> > >
> > > "Charles Kangai" wrote:
> > >
> > > > There is a language property for the report and a textbox. You can for
> > > > example select English (United Kingdom), and the date will display as
> > > > dd/mm/yy. There is also an International Settings option in Tools,Options of
> > > > the development enviromnent.
> > > >
> > > > The report property setting overrides the global setting, and the textbox
> > > > property overrides the report property.
> > > >
> > > > The language property can also be left as Default on the report or textbox.
> > > > If left as Default, the more global settings takes effect.
> > > >
> > > > Finally, you can use custom expressions such as
> > > > =Format(Fields!MyDateField.Value, "dd/MMM/yy")
> > > >
> > > > HTH
> > > >
> > > > Charles Kangai, MCT, MCDBA
> > > >
> > > > "Rudi Groenewald" wrote:
> > > >
> > > > > Hi all,
> > > > >
> > > > > In our whole system (Sql server, forms applications etc) I have configured
> > > > > our date formats to be dd/mm/yyyy, as this is the easiest for my users. My
> > > > > problem is that Reporting services only respond to the format mm/dd/yyyy
> > > > > .... How can I configure reporting services to only use the format
> > > > > dd/mm/yyyy?
> > > > >
> > > > > Thanks alot in advance
> > > > >
> > > > > R
> > > > >
> > > > >
> > > > >|||question sort of related, how would i format the date on a parameter so that
when it is entered "1/1/2005" or "01/01/2005" that it will print January 1,
2005.
thanks in advance
"Rudi Groenewald" wrote:
> Hi all,
> In our whole system (Sql server, forms applications etc) I have configured
> our date formats to be dd/mm/yyyy, as this is the easiest for my users. My
> problem is that Reporting services only respond to the format mm/dd/yyyy
> .... How can I configure reporting services to only use the format
> dd/mm/yyyy?
> Thanks alot in advance
> R
>
>|||Hi,
=Format(DateValue,"MMMM d, yyyy")
HTH,
Eric

Date Formats

Hi,
While I'm trying to execute the SQL statement "SELECT STAFFNO FROM
MASTER WHERE DATEOFLEAVING = "08/31/2003" " from my application I'm getting
the following error :
"The conversion of a char data type to a datetime data type
resulted in an out-of-range datetime value"
But the same statement executes without any error if I connnect the
application to a different SQL Server.
I'm using SQL Server 7.
Why this error, do I need to set anything the server or the client ?
Thanks
Rajeev RIs DATEOFLEAVING of datetime datatype?
If so try the following:
set dateformat dmy
SELECT STAFFNO
FROM MASTER
WHERE DATEOFLEAVING = '08/31/2003'
or this
SELECT STAFFNO
FROM MASTER
WHERE DATEOFLEAVING = convert(datetime, '08/31/2003', 103)
--
Dean Savovic
www.teched.hr
"Rajeev Ramanujan" <rajeevramanujan@.hotmail.com> wrote in message news:%23QZaDW3SDHA.2188@.TK2MSFTNGP10.phx.gbl...
> Hi,
> While I'm trying to execute the SQL statement "SELECT STAFFNO FROM
> MASTER WHERE DATEOFLEAVING = "08/31/2003" " from my application I'm getting
> the following error :
> "The conversion of a char data type to a datetime data type
> resulted in an out-of-range datetime value"
> But the same statement executes without any error if I connnect the
> application to a different SQL Server.
> I'm using SQL Server 7.
> Why this error, do I need to set anything the server or the client ?
> Thanks
> Rajeev R
>|||Try changing the statement to:
SELECT STAFFNO FROM MASTER WHERE DATEOFLEAVING = CAST('2003-08-31 00:00:00'
AS DATETIME)
Pete
"Rajeev Ramanujan" <rajeevramanujan@.hotmail.com> wrote in message
news:%23QZaDW3SDHA.2188@.TK2MSFTNGP10.phx.gbl...
> Hi,
> While I'm trying to execute the SQL statement "SELECT STAFFNO FROM
> MASTER WHERE DATEOFLEAVING = "08/31/2003" " from my application I'm
getting
> the following error :
> "The conversion of a char data type to a datetime data type
> resulted in an out-of-range datetime value"
> But the same statement executes without any error if I connnect
the
> application to a different SQL Server.
> I'm using SQL Server 7.
> Why this error, do I need to set anything the server or the client
?
> Thanks
> Rajeev R
>

Date Formats

We have recently upgraded from SQL 6.5 to SQL 2000, and are having problems
with the date format in BCP'd files.
Previously 6.5 would output dates such as 1 Jan 2004 and 20 Jan 2004, but
SQL 2000, leaves a leading zero on 01 jan 2004.
I have used the convert function to make to date to 01 jan 2004, but how do
I suppress the leading zero on the date ?
Any help would be appreciarted
MarkYou could make your own function, along these lines:
http://www.aspfaq.com/2460
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Flems" <Mark.Flemans@.apakgroup.com> wrote in message
news:O5JaVwdAEHA.2480@.TK2MSFTNGP12.phx.gbl...
> We have recently upgraded from SQL 6.5 to SQL 2000, and are having
problems
> with the date format in BCP'd files.
> Previously 6.5 would output dates such as 1 Jan 2004 and 20 Jan 2004, but
> SQL 2000, leaves a leading zero on 01 jan 2004.
> I have used the convert function to make to date to 01 jan 2004, but how
do
> I suppress the leading zero on the date ?
> Any help would be appreciarted
> Mark
>|||Thanks for that, I'd just discovered that you can create a function. It all
now works fine !!
Cheers
Mark
"Aaron Bertrand [MVP]" <aaron@.TRASHaspfaq.com> wrote in message
news:OarRSRgAEHA.808@.TK2MSFTNGP12.phx.gbl...
> You could make your own function, along these lines:
> http://www.aspfaq.com/2460
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>
> "Flems" <Mark.Flemans@.apakgroup.com> wrote in message
> news:O5JaVwdAEHA.2480@.TK2MSFTNGP12.phx.gbl...
> > We have recently upgraded from SQL 6.5 to SQL 2000, and are having
> problems
> > with the date format in BCP'd files.
> >
> > Previously 6.5 would output dates such as 1 Jan 2004 and 20 Jan 2004,
but
> > SQL 2000, leaves a leading zero on 01 jan 2004.
> >
> > I have used the convert function to make to date to 01 jan 2004, but how
> do
> > I suppress the leading zero on the date ?
> >
> > Any help would be appreciarted
> >
> > Mark
> >
> >
>

Date formats

I am currently supporting an application that runs in SQL
Server 2000. One of our customers is having a problem
with filtering items from the database using dates. The
problem is that they can only filter on dates using US
date format, ie mm/dd/yy, yet once the day exceeds 12 they
can no longer view any data from the database. So if they
filter on 09/12/03 that will work fine and pull back data
from the 12th of September. If they filter on 09/13/03 it
does not pull anything back, even though there is data
from that day. Using UK format dates does not pull any
data back at all. This does not happen for any of my
other customers who are running the same application in
SQL Server 2000. Is there an option that can be set
either on the database or on the server that can affect
this?
Any ideas would be greatly appreciated.Ideally this requires a code change in your application.
Your app needs to pass dates around in a non ambiguous
format eg :- '13 Mar 2003', this format can cause problems
if you are running multiple languages though. The safeest
bet is yyyymmdd SQL will always interpret this as ymd.
If a code change isn't possible and you want a quick fix
for this client, compare the regional settings in control
panel to the troublesome machine to a known good one. I
imagine it's running English (US) and everyone else is
using English (UK) or vice versa.
HTH
Ryan
>--Original Message--
>I am currently supporting an application that runs in SQL
>Server 2000. One of our customers is having a problem
>with filtering items from the database using dates. The
>problem is that they can only filter on dates using US
>date format, ie mm/dd/yy, yet once the day exceeds 12
they
>can no longer view any data from the database. So if
they
>filter on 09/12/03 that will work fine and pull back data
>from the 12th of September. If they filter on 09/13/03
it
>does not pull anything back, even though there is data
>from that day. Using UK format dates does not pull any
>data back at all. This does not happen for any of my
>other customers who are running the same application in
>SQL Server 2000. Is there an option that can be set
>either on the database or on the server that can affect
>this?
>Any ideas would be greatly appreciated.
>.
>

Date formats

I'm running the same insert 'insert into jftemp values ('2006/09/11')' on 2
different servers, one saves this date as 11th Sep the other saves it as 9th
Nov. Both servers have the same regional settings, language settings, default
language - both are SQL Server 2000 (SP4). Can someone suggest why the dates
are being stored differently?
thanks, JohnJohn - have a look at the default languages of the Logins you are using
(sp_helplogins) - are they different on each server?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||that's it! Thanks very much Paul, it's obvious when you know the answer!
"Paul Ibison" wrote:
> John - have a look at the default languages of the Logins you are using
> (sp_helplogins) - are they different on each server?
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
>|||On Fri, 25 Aug 2006 02:20:01 -0700, John wrote:
>I'm running the same insert 'insert into jftemp values ('2006/09/11')' on 2
>different servers, one saves this date as 11th Sep the other saves it as 9th
>Nov. Both servers have the same regional settings, language settings, default
>language - both are SQL Server 2000 (SP4). Can someone suggest why the dates
>are being stored differently?
>thanks, John
Hi John,
They are not stored differently, the string constant is interpreted in
different ways.
To prevent this in the future, always use one of the unambiguous
formats. For date only, use yyyymmdd (i.e. '20060825' for 11th Sep). For
date plus time, use yyyy-mm-ddThh:mm:ss, optionally followed by .mmm for
milliseconds (i.e. '2006-08-25T02:20:01' or '2006-08-25T02:20:01.373').
--
Hugo Kornelis, SQL Server MVP|||>> (i.e. '20060825' for 11th Sep) <<
This may be non-ambiguous, but it's also very non-intuitive.
:-)
--
HTH
Kalen Delaney, SQL Server MVP
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:t1uue21q4qa741hvpb898lnv55ennsehs3@.4ax.com...
> On Fri, 25 Aug 2006 02:20:01 -0700, John wrote:
>>I'm running the same insert 'insert into jftemp values ('2006/09/11')' on
>>2
>>different servers, one saves this date as 11th Sep the other saves it as
>>9th
>>Nov. Both servers have the same regional settings, language settings,
>>default
>>language - both are SQL Server 2000 (SP4). Can someone suggest why the
>>dates
>>are being stored differently?
>>thanks, John
> Hi John,
> They are not stored differently, the string constant is interpreted in
> different ways.
> To prevent this in the future, always use one of the unambiguous
> formats. For date only, use yyyymmdd (i.e. '20060825' for 11th Sep). For
> date plus time, use yyyy-mm-ddThh:mm:ss, optionally followed by .mmm for
> milliseconds (i.e. '2006-08-25T02:20:01' or '2006-08-25T02:20:01.373').
> --
> Hugo Kornelis, SQL Server MVP|||On Mon, 28 Aug 2006 15:14:35 -0700, Kalen Delaney wrote:
>> (i.e. '20060825' for 11th Sep) <<
>This may be non-ambiguous, but it's also very non-intuitive.
>:-)
Ouch!
Good catch, Kalen.
--
Hugo Kornelis, SQL Server MVP

Date Formats

We have recently upgraded from SQL 6.5 to SQL 2000, and are having problems
with the date format in BCP'd files.
Previously 6.5 would output dates such as 1 Jan 2004 and 20 Jan 2004, but
SQL 2000, leaves a leading zero on 01 jan 2004.
I have used the convert function to make to date to 01 jan 2004, but how do
I suppress the leading zero on the date ?
Any help would be appreciarted
MarkYou could make your own function, along these lines:
http://www.aspfaq.com/2460
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Flems" <Mark.Flemans@.apakgroup.com> wrote in message
news:O5JaVwdAEHA.2480@.TK2MSFTNGP12.phx.gbl...
> We have recently upgraded from SQL 6.5 to SQL 2000, and are having
problems
> with the date format in BCP'd files.
> Previously 6.5 would output dates such as 1 Jan 2004 and 20 Jan 2004, but
> SQL 2000, leaves a leading zero on 01 jan 2004.
> I have used the convert function to make to date to 01 jan 2004, but how
do
> I suppress the leading zero on the date ?
> Any help would be appreciarted
> Mark
>|||Thanks for that, I'd just discovered that you can create a function. It all
now works fine !!
Cheers
Mark
"Aaron Bertrand [MVP]" <aaron@.TRASHaspfaq.com> wrote in message
news:OarRSRgAEHA.808@.TK2MSFTNGP12.phx.gbl...
> You could make your own function, along these lines:
> http://www.aspfaq.com/2460
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>
> "Flems" <Mark.Flemans@.apakgroup.com> wrote in message
> news:O5JaVwdAEHA.2480@.TK2MSFTNGP12.phx.gbl...
> problems
but
> do
>

Date formats

I'm running the same insert 'insert into jftemp values ('2006/09/11')' on 2
different servers, one saves this date as 11th Sep the other saves it as 9th
Nov. Both servers have the same regional settings, language settings, defaul
t
language - both are SQL Server 2000 (SP4). Can someone suggest why the dates
are being stored differently?
thanks, JohnJohn - have a look at the default languages of the Logins you are using
(sp_helplogins) - are they different on each server?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||that's it! Thanks very much Paul, it's obvious when you know the answer!
"Paul Ibison" wrote:

> John - have a look at the default languages of the Logins you are using
> (sp_helplogins) - are they different on each server?
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
>|||On Fri, 25 Aug 2006 02:20:01 -0700, John wrote:

>I'm running the same insert 'insert into jftemp values ('2006/09/11')' on 2
>different servers, one saves this date as 11th Sep the other saves it as 9t
h
>Nov. Both servers have the same regional settings, language settings, defau
lt
>language - both are SQL Server 2000 (SP4). Can someone suggest why the date
s
>are being stored differently?
>thanks, John
Hi John,
They are not stored differently, the string constant is interpreted in
different ways.
To prevent this in the future, always use one of the unambiguous
formats. For date only, use yyyymmdd (i.e. '20060825' for 11th Sep). For
date plus time, use yyyy-mm-ddThh:mm:ss, optionally followed by .mmm for
milliseconds (i.e. '2006-08-25T02:20:01' or '2006-08-25T02:20:01.373').
Hugo Kornelis, SQL Server MVP|||>> (i.e. '20060825' for 11th Sep) <<
This may be non-ambiguous, but it's also very non-intuitive.
:-)
HTH
Kalen Delaney, SQL Server MVP
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:t1uue21q4qa741hvpb898lnv55ennsehs3@.
4ax.com...
> On Fri, 25 Aug 2006 02:20:01 -0700, John wrote:
>
> Hi John,
> They are not stored differently, the string constant is interpreted in
> different ways.
> To prevent this in the future, always use one of the unambiguous
> formats. For date only, use yyyymmdd (i.e. '20060825' for 11th Sep). For
> date plus time, use yyyy-mm-ddThh:mm:ss, optionally followed by .mmm for
> milliseconds (i.e. '2006-08-25T02:20:01' or '2006-08-25T02:20:01.373').
> --
> Hugo Kornelis, SQL Server MVP|||On Mon, 28 Aug 2006 15:14:35 -0700, Kalen Delaney wrote:

>This may be non-ambiguous, but it's also very non-intuitive.
>:-)
Ouch!
Good catch, Kalen.
Hugo Kornelis, SQL Server MVP

Date formats

I read this and got confused...

I use PHP and got this:
$sql = "delete from blabla where startdate< =
to_date('2000.05.05','YYYY-MM-DD')";
$result=odbc_exec($conn, $sql);

Result: Warning: odbc_exec() [function.odbc-exec]: SQL error:
[Microsoft][ODBC SQL Server Driver][SQL Server]'to_date' is not a
recognized function name.

to_date should work?

At the other end i'd like to read out startdate as a date var or format
it as I need... (dd.mm.yyyy). Convert makes me confused... help
please?

SHi

to_date is for Oracle

Pull out the date and do the conversion in php. Let the database do
data things

Sam

On Dec 4, 2:02 pm, "Sonnich" <sonnich.jen...@.elektrobit.comwrote:

Quote:

Originally Posted by

I read this and got confused...
>
I use PHP and got this:
$sql = "delete from blabla where startdate< =
to_date('2000.05.05','YYYY-MM-DD')";
$result=odbc_exec($conn, $sql);
>
Result: Warning: odbc_exec() [function.odbc-exec]: SQL error:
[Microsoft][ODBC SQL Server Driver][SQL Server]'to_date' is not a
recognized function name.
>
to_date should work?
>
At the other end i'd like to read out startdate as a date var or format
it as I need... (dd.mm.yyyy). Convert makes me confused... help
please?
>
S

|||

Quote:

Originally Posted by

I use PHP and got this:
$sql = "delete from blabla where startdate< =
to_date('2000.05.05','YYYY-MM-DD')";

Quote:

Originally Posted by

At the other end i'd like to read out startdate as a date var or format
it as I need... (dd.mm.yyyy). Convert makes me confused... help
please?

Checkout the "mssql.datetimeconvert" option in PHP.INI.

Regards,
lucm|||Sonnich wrote:

Quote:

Originally Posted by

I read this and got confused...
>
I use PHP and got this:
$sql = "delete from blabla where startdate< =
to_date('2000.05.05','YYYY-MM-DD')";
$result=odbc_exec($conn, $sql);
>
Result: Warning: odbc_exec() [function.odbc-exec]: SQL error:
[Microsoft][ODBC SQL Server Driver][SQL Server]'to_date' is not a
recognized function name.
>
to_date should work?
>
At the other end i'd like to read out startdate as a date var or format
it as I need... (dd.mm.yyyy). Convert makes me confused... help
please?
>
S

You should post at Oracle or Mysql forums

Madhivanan

Date format in ms sql

I have computer with Serbian standards and formats (Regional Settings).

I use datepicker control to select date, but when I try to insert it into database (DatePicker1.Value) it throws error.

What should I set up in order to be able to insert date into ms sql 2000 database, without having to change Regional settings?

This is last question for tonight

Thanks,
Milan

If you use parameters, rather than trying to build up the SQL INSERT into a string.

http://aspnet101.com/aspnet101/tutorials.aspx?id=1

|||Very simple. nice and clean solution.

You just make me close bunch of open browser tabs and windows with Google's results

Thanks a lot.

Date Dimension in SSAS2005

Dear all,

In AS2000 when we use a date field of the dimesion table as a date dimension and defines the level (ex:Year,Month and dat) AS2000 formats the date field as given format.

In SSAS2005 i used the same methord but SSAS2005 does goes in that way.It simply gives me all the date as in database (10/10/2005)

How can i do it in level and hirarchy (year,month and day)

Hope u understand the Question

from

sufian

Dear all,

AS i studied in and tested i come to know that we can use two type of time dimesnion in SSAS2005 beased on the user requirement.

(1) Time Dimension (2) Server Time Dimension

Time Dimesion : Is similar to other dimension and supplies the attributes of the dimension.Each column with define an attribute for a specific time period.

Server Time Period: This is the one which defines the attribute on Year,Month Day and much more.

Thanx

Regards

Mohd Sufian

Date Conversion help

Hi,

I have my dates in DB2 source in two formats -

Format 1 - char(5) - Example - 10305. 1 indicates century,03 indicates year and 05 indicates month. The day is not stored. So this is 2003,May 1

Format 2 - char(7) - Example - 1030525. 1 indicates century,03 indicates year, 05 indicates month,25 indicates day. 2003,May 25

I want to convert the above two formats to SQL Server smalldatetime and I only need the DATEPART. The date needs to be in the format mm/dd/yyyy. The default day would be 01 when the day is not specified.

If the format is 00305 then the 0 indicated 19th century. So this is 1903, May 1.

Any help is appreciated.

Thanks,

Vivek1 indicates century? What century is it supposed to indicate? Are we talking the year 1903? I am assuming it is supposed to be 1=2000 in the code below:

CREATE FUNCTION fn_convert_DB@._date (@.db2_date VARCHAR(7))

RETURNS SMALLDATETIME AS

DECLARE @.new_date AS SMALLDATETIME

IF LEN(@.db2_date) = 5
SELECT @.new_date = CAST(RIGHT(@.db2_date,2) + '/1/' + CAST(CAST(LEFT(@.db2_date, 1) AS INT) + 1 AS VARCHAR(1)) + '0' + SUBSTRING(@.db2_date, 2, 2) AS DATETIME)

IF LEN(@.db2_date) = 7
SELECT @.new_date = CAST(SUBSTRING(@.db2_date,4, 2) + '/' + RIGHT(@.db2_date, 2) + '/' + CAST(CAST(LEFT(@.db2_date, 1) AS INT) + 1 AS VARCHAR(1)) + '0' + SUBSTRING(@.db2_date, 2, 2) AS DATETIME)

RETURN @.new_date|||Yes 1 indicates century 2000 and 0 indicated 1900. So 10305 would be 2003. From first 3 digits. And 19505 would be 1995.|||You should be able to use the function inline in your queries:

SELECT dbo.fn_convert_db2_date(the_db2_date_field)

will return the converted SMALLDATETIME field|||Hi,

I am not able to convert 09525 using the function. Also the date I need is of the format - mm/dd/yyyy ex: 5/21/2003. When I execute the function for '10325', I get 2004-05-01 00:00:00. I do not need the time part. Also the format is different.

Thanks|||The date format is always store in the same fashion in SQL Server. What you want to do is alter the format when you display the date in your UI/report/whatever it is.

CONVERT(the_date_field, 101) will give you a VARCHAR of the date in mm/dd/yyyy.

'10325', according to your definition, would be Year=2003, Month = 25.

Reworking the year conversion. Sorry about the shoddy workmanship there.|||CREATE FUNCTION fn_convert_DB@._date (@.db2_date VARCHAR(7))

RETURNS SMALLDATETIME AS

DECLARE @.new_date AS SMALLDATETIME

IF LEN(@.db2_date) = 5 AND ISDATE(RIGHT(@.db2_date,2) + '/1/' + CAST(CAST(LEFT(@.db2_date, 1) AS INT) + 19 AS VARCHAR(2)) + SUBSTRING(@.db2_date, 2, 2))=1
SELECT @.new_date = CAST(RIGHT(@.db2_date,2) + '/1/' + CAST(CAST(LEFT(@.db2_date, 1) AS INT) + 19 AS VARCHAR(2)) + SUBSTRING(@.db2_date, 2, 2) AS SMALLDATETIME)

IF LEN(@.db2_date) = 7 AND ISDATE(SUBSTRING(@.db2_date,4, 2) + '/' + RIGHT(@.db2_date, 2) + '/' + CAST(CAST(LEFT(@.db2_date, 1) AS INT) + 19 AS VARCHAR(2)) + SUBSTRING(@.db2_date, 2, 2))=1
SELECT @.new_date = CAST(SUBSTRING(@.db2_date,4, 2) + '/' + RIGHT(@.db2_date, 2) + '/' + CAST(CAST(LEFT(@.db2_date, 1) AS INT) + 19 AS VARCHAR(2)) + SUBSTRING(@.db2_date, 2, 2) AS DATETIME)

RETURN @.new_date|||New function checks for proper date format before converting, and returns NULL if format is wrong. Oh, and it deals properly with centuries.

Date & time formats

I'd like to be able to display date/time in a format appropriate for the location of the viewer. (i.e., for March 15th the date would be 3/15/2006 for US viewers and 15/3/2006 for UK viewers.) What would be the simplest way of implementing this?

Thanks!

Try setting the Report Language to an expression that equals the language of the user running the report. The expression looks like this. I haven't tested this so let me know if it works.

=User!Language