Formula in flaot field

I have a float field and need the data to present itself as a datetime. I have been unsuccessful at converting the entire field to datetime so I thought I would try a formula but have had no success. Can this be done?

I have data like so:

100599
10699
120597
etc...

and need to hit them as though they were datetime for order by and such...

or an 'in query' conversion...

ThanksI came up with a solution, thanks anyway.sql

Formatting Dates

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

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

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

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

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

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

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

Format of DateTime types in Select vs Open Table

From within the SQL Server Management Studio, if I select a table and displa
y
it (right click Open Table), columns of type DateTime are displayed with the
mm/dd/yyyy hh:mm:ss format. However, if I SELECT * FROM the same table from
a
query in the Query Analyzer (or New Query in 2005), the same column is
displayed with a yyyy-mm-dd hh:mm:ss format.
Why is there a difference in display format between opening the table in the
Management Studio and SELECT'ing in a query?
Short of using a CONVERT in the SELECT, is there a way of controlling the
display format of DateTime types in both scenarios?
Michael
--
Michael Hocksteinmichael (howlinghound@.nospam.nospam) writes:
> From within the SQL Server Management Studio, if I select a table and
> display it (right click Open Table), columns of type DateTime are
> displayed with the mm/dd/yyyy hh:mm:ss format. However, if I SELECT *
> FROM the same table from a query in the Query Analyzer (or New Query in
> 2005), the same column is displayed with a yyyy-mm-dd hh:mm:ss format.
> Why is there a difference in display format between opening the table in
> the Management Studio and SELECT'ing in a query?
Open Table respects the regional settings, while by default Query Analyzer
does not. You can change this for QA by going into Tools->Options->
Connections and check "Use regional settings...". There does not seem to be
any similar option for Management Studio. but query results always
apparently uses ISO format. (I can't really tell from here, because my
regional settings are the ISO format.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thank you!
Michael Hockstein
"Erland Sommarskog" wrote:

> michael (howlinghound@.nospam.nospam) writes:
> Open Table respects the regional settings, while by default Query Analyzer
> does not. You can change this for QA by going into Tools->Options->
> Connections and check "Use regional settings...". There does not seem to b
e
> any similar option for Management Studio. but query results always
> apparently uses ISO format. (I can't really tell from here, because my
> regional settings are the ISO format.)
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>

Format of DateTime Column in SELECT query

I am using SQL2005 and ASP.NET 2.0

I have one column in database called DateTime and it is defined like type datetime.It is formated like: day.month.year hour:minutes:seconds

My question is: I want to get date from Column DateTime in format day.month.year in SELECT query, not in stored procedure.

thanks

SELECT

CONVERT(NVARCHAR(10),getdate(), 104)as yourDate

replace the getdate() function with your datetime field in your select statement.

|||I just want to remind you that Column Name DateTime is the keyword of sql. You have to put it in [] or ""|||thanx, i managed it somehow.I found instructions in book about CONVERT so i saw how it works

format datetime without characters

I need to convert the date format with an output of yyyymmdd'
I have tried the following code but it doesn't produce the desired output
CONVERT(VARCHAR(8),supp_creation_date , 112 ) ,
Any suggestions?
Thanks in advanceThat should work if supp_creation_date is a DATETIME or SMALLDATETIME. What
result do you get? Can you post some code to reproduce it? What version of
SQL Server? Here's an example:
SELECT CONVERT(VARCHAR(8),CURRENT_TIMESTAMP,112
)
Result:
20050502
(1 row(s) affected)
If you get something else then I'd guess that in the context in which you
are using it the result is being implicitly cast to some other datatype.
David Portas
SQL Server MVP
--|||That seems to work for me:
Declare @.Date DateTime
Set @.Date = Current_TimeStamp
Select Convert(VarChar(8), @.Date, 112)
Produces:
20050502
What output are you getting?
Thomas
"Sherry" <Sherry@.discussions.microsoft.com> wrote in message
news:99360DE6-995D-45B2-80E0-744A800DE396@.microsoft.com...
>I need to convert the date format with an output of yyyymmdd'
> I have tried the following code but it doesn't produce the desired output
> CONVERT(VARCHAR(8),supp_creation_date , 112 ) ,
> Any suggestions?
> Thanks in advance|||Sherry,
What is the type of column [supp_creation_date]?. If it is not a datetime
then you have to convert it to, before using the statement you posted.
Example:
declare @.s varchar(10)
set @.s = '05/02/2005'
select convert(char(8), convert(datetime, @.s, 101), 112)
go
-- this works
select convert(char(8), getdate(), 112)
go
AMB
"Sherry" wrote:

> I need to convert the date format with an output of yyyymmdd'
> I have tried the following code but it doesn't produce the desired output
> CONVERT(VARCHAR(8),supp_creation_date , 112 ) ,
> Any suggestions?
> Thanks in advance|||Sherry, Your column in your table must not be a datetime... you'll have to
cast it to a datetime first.
Try this
Select CONVERT(VARCHAR(8), Cast(supp_creation_date As DateTime) , 112 )
From YourTable
"Sherry" wrote:

> I need to convert the date format with an output of yyyymmdd'
> I have tried the following code but it doesn't produce the desired output
> CONVERT(VARCHAR(8),supp_creation_date , 112 ) ,
> Any suggestions?
> Thanks in advance|||Does it produce the proper format in Query Analyzer? This is always the
first place to try stuff out, as QA is very good not to insert it's own
formatting to the output:
create table test
(
supp_creation_date datetime
)
insert into test
select getdate()
go
select CONVERT(VARCHAR(8),supp_creation_date , 112 )
from test
returns:
20050502
However, if this value gets put back into a datetime variable before the
client recieves it, or the client puts it inot a date container, the
formatting goes away. Formatting only works on textual values, not date
values.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Sherry" <Sherry@.discussions.microsoft.com> wrote in message
news:99360DE6-995D-45B2-80E0-744A800DE396@.microsoft.com...
>I need to convert the date format with an output of yyyymmdd'
> I have tried the following code but it doesn't produce the desired output
> CONVERT(VARCHAR(8),supp_creation_date , 112 ) ,
> Any suggestions?
> Thanks in advance

Format Datetime type in column

Hi
Can you please help me on this matter please?

I am trying to input UK(for example: 25/09/2007 ) format datetime in sql server. Is there any change that I can format my column to UK datetime. At the moment there is in US format(09/25/2007).

I have a table, one of the column has type datetime.

I use the insert command and the data i want to into is '25/09/2007'

It come back with error that the date is out of range. I change that to 09/09/2007 then it work.
The currently date and time format is MM/DD/YYYY.
I don't know how can i define the format to be DD/MM/YYYY

Thank you very much for your help
i really do appreciate that.

Best regards

Your question is not clear to me .Can you elaborate your question little bit more.
thanx|||Thanks a lot for your reply

I have a table, one of the column has type datetime.

I use the insert command and the data i want to into is '25/09/2007'

It come back with error that the date is out of range. I change that to 09/09/2007 then it work.
The currently date and time format is MM/DD/YYYY.
I don't know how can i define the format to be DD/MM/YYYY

Hope that make it clear.
Once again thanks a lot|||

Have a look at SET DATEFORMAT in Books Online. This sets the order of the dateparts in your date string.

eg

Code Snippet

set dateformat dmy

declare @.datetime datetime

set @.datetime = '12/05/2007'

select datepart(day, @.datetime) as day, datepart(month, @.datetime) as month

set dateformat mdy

declare @.datetime datetime

set @.datetime = '12/05/2007'

select datepart(day, @.datetime) as day, datepart(month, @.datetime) as month

Note, its a good idea to use the ISO format for dates to remove any ambiguity ie 20070512.

HTH!

|||

It's a common misunderstanding that you can insert and store a date into a datetime datatype in a certain format or style.
You can't, so it's actually a waste of time trying to 'force' a specific format such as '12/05/2007' or '20071205' or any other variation.

Whichever format you send to the server for storage, will not be stored in anyting that resembles a date to us humans.

A datetime is stored as two integers internally, it doesn't look like a date at all.

So, whatever date we send, it's not going to be stored in that format.

What is important, though, is how a date is seen and parsed by the server. We want to be sure that there's no misunderstandings - that the server understands the format we send the date in to be the same as we intend.

Just about all formats, such as '12/05/2007' or '05/12/2007' (btw - which date *is* that anyway? may or december?)
are dependant on language and/or datesettings. This is pretty unsafe and opens up for unexpected conversions.

The only 'safe format' is the one mentioned earlier in the thread, ssyymmdd - if you use that as input, you can be sure that nothing weird may happen that cause the date to change. However, it's still stored as two integers...

To answer the question: if you need a special format when displaying a date, you don't do this at insert time, you do it when it's retrieved for display. In T-SQL you use CONVERT with the style parameter of your choice. If you don't use CONVERT and supply a style, then the format you'll see will be in the style that is the default for the tool that is used.

/Kenneth

|||

Kenneth,

Dates can be a little tricky to graspe sometimes due to the varied formats in which they come in. I think the problem is often that people believe what they put in is what they'll get out.

Typically, the actual battle is "how to i get SQL Server to correctly understand my date input/output" and this is where there is a valid use for CONVERT, DATEFORMAT etc. Hence, I used 05/12/2007 specifically to illustrate the point of how DATEFORMAT can effect the output (Of course, its Eddie the Eagle Edwards birthday in Britain )

You're right to point out the difference between storing and parsing dates and the effects of local/global settings and i agree its important to understand this.

Cheers

Format Datetime type in column

Hi
Can you please help me on this matter please?

I am trying to input UK(for example: 25/09/2007 ) format datetime in sql server. Is there any change that I can format my column to UK datetime. At the moment there is in US format(09/25/2007).

I have a table, one of the column has type datetime.

I use the insert command and the data i want to into is '25/09/2007'

It come back with error that the date is out of range. I change that to 09/09/2007 then it work.
The currently date and time format is MM/DD/YYYY.
I don't know how can i define the format to be DD/MM/YYYY

Thank you very much for your help
i really do appreciate that.

Best regards

Your question is not clear to me .Can you elaborate your question little bit more.
thanx|||Thanks a lot for your reply

I have a table, one of the column has type datetime.

I use the insert command and the data i want to into is '25/09/2007'

It come back with error that the date is out of range. I change that to 09/09/2007 then it work.
The currently date and time format is MM/DD/YYYY.
I don't know how can i define the format to be DD/MM/YYYY

Hope that make it clear.
Once again thanks a lot|||

Have a look at SET DATEFORMAT in Books Online. This sets the order of the dateparts in your date string.

eg

Code Snippet

set dateformat dmy

declare @.datetime datetime

set @.datetime = '12/05/2007'

select datepart(day, @.datetime) as day, datepart(month, @.datetime) as month

set dateformat mdy

declare @.datetime datetime

set @.datetime = '12/05/2007'

select datepart(day, @.datetime) as day, datepart(month, @.datetime) as month

Note, its a good idea to use the ISO format for dates to remove any ambiguity ie 20070512.

HTH!

|||

It's a common misunderstanding that you can insert and store a date into a datetime datatype in a certain format or style.
You can't, so it's actually a waste of time trying to 'force' a specific format such as '12/05/2007' or '20071205' or any other variation.

Whichever format you send to the server for storage, will not be stored in anyting that resembles a date to us humans.

A datetime is stored as two integers internally, it doesn't look like a date at all.

So, whatever date we send, it's not going to be stored in that format.

What is important, though, is how a date is seen and parsed by the server. We want to be sure that there's no misunderstandings - that the server understands the format we send the date in to be the same as we intend.

Just about all formats, such as '12/05/2007' or '05/12/2007' (btw - which date *is* that anyway? may or december?)
are dependant on language and/or datesettings. This is pretty unsafe and opens up for unexpected conversions.

The only 'safe format' is the one mentioned earlier in the thread, ssyymmdd - if you use that as input, you can be sure that nothing weird may happen that cause the date to change. However, it's still stored as two integers...

To answer the question: if you need a special format when displaying a date, you don't do this at insert time, you do it when it's retrieved for display. In T-SQL you use CONVERT with the style parameter of your choice. If you don't use CONVERT and supply a style, then the format you'll see will be in the style that is the default for the tool that is used.

/Kenneth

|||

Kenneth,

Dates can be a little tricky to graspe sometimes due to the varied formats in which they come in. I think the problem is often that people believe what they put in is what they'll get out.

Typically, the actual battle is "how to i get SQL Server to correctly understand my date input/output" and this is where there is a valid use for CONVERT, DATEFORMAT etc. Hence, I used 05/12/2007 specifically to illustrate the point of how DATEFORMAT can effect the output (Of course, its Eddie the Eagle Edwards birthday in Britain )

You're right to point out the difference between storing and parsing dates and the effects of local/global settings and i agree its important to understand this.

Cheers

Format Datetime in SQL syntax

Hi,
Does anyone know how I can format DateTme value in sql database to Short
DateTime using SQL syntax?
Thank you,
SunnyDo you mean you want to change the data type of the column from DateTime
to smalldatetime? Or you would like the returned results of a query to
have the datetime column with less information displayed.
Simon Worth
Sansanee wrote:
> Hi,
> Does anyone know how I can format DateTme value in sql database to Short
> DateTime using SQL syntax?
> Thank you,
> Sunny
>|||To change the ACTUAL datatype persisted (stored) in the database, use the
Cast operator, but ensure that all the actual date values are between 1 jan
1900 and 6 Jun 2079.
If you just want to format the output of a query, (and you can;t or don't
want to o this on client) you can use Convert() function and convert to a
varchar(), using one of a group of various format types Look up COnvert in
Books OnLine...
"Sansanee" wrote:

> Hi,
> Does anyone know how I can format DateTme value in sql database to Short
> DateTime using SQL syntax?
> Thank you,
> Sunny
>
>|||I recommend you check out:
http://www.karaszi.com/SQLServer/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Sansanee" <sansanee@.nospam.com> wrote in message news:elIrk2xNFHA.3880@.tk2msftngp13.phx.gb
l...
> Hi,
> Does anyone know how I can format DateTme value in sql database to Short
> DateTime using SQL syntax?
> Thank you,
> Sunny
>|||Thank you. It works!
Sunny
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:56BDBAEA-F16B-4447-9145-E70699BE6AEC@.microsoft.com...
> To change the ACTUAL datatype persisted (stored) in the database, use the
> Cast operator, but ensure that all the actual date values are between 1
jan
> 1900 and 6 Jun 2079.
> If you just want to format the output of a query, (and you can;t or don't
> want to o this on client) you can use Convert() function and convert to a
> varchar(), using one of a group of various format types Look up COnvert in
> Books OnLine...
>
> "Sansanee" wrote:
>

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 column

Hello,

I am wondering if someone could help me with formatting datatime column.

Goal - keep column in datatime instead of convert to varchar

Current - '2006-06-21 16:54:33.000'

Wants - '2006-06-21 16:54:33'

Any help is appreciated!

-Lawrence

Code Bits

declare @.time datetime

set @.time = '2006-06-21 16:54:33.000'

select @.time, convert(varchar(255), @.time, 20)

You usually do not want to format the output in the database. This would lead to the question why do you need to do this in T-SQL?

The front-end should be able to format the date to a proper format instead of relying on the database to do it. The database gives the data and the front-end presents it. Skipping the milliseconds are most likely the task of the front-end.

|||

I know what you are saying, but the reason is that the format from this t-sql goes into a transformation and oddly enough the transformation does not accept datetime format with millisecond. I thought SET DateFormat would be able to set any datetime format, but I guess that feature is very limited.

-Lawrence

|||declare @.time datetime
set @.time = '2006-06-21 16:54:33.000'
select @.time, convert(varchar(19), @.time, 121)|||What transformation are you referring to? Is it a DTS/SSIS package? If so that can format the data accordingly. Please elaborate on your problem. The datetime value is stored in native format on the server and if you want to format it as string you need to use CONVERT & other string functions to do it. Typically you will send the values as is to the client and format on the client side using richer mechanisms.

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 Dates in ISO 8601

Hi,

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

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

DatePick.Value.ToString("s")

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

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

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

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

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

I really appreciate your help.

George

Hi,

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

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

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

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

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

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

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

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

I suggest you modify your query to...

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

To determine your language settings, you can run:

sp_configure 'default language'

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

sp_helplanguage

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

Format date parameters

Hi,
I have a start date (date/time) parameter with a default value of
=DateTime.Today & a end date (date/time) parameter with a default value of
=DateTime.Today.adddays(7)
These parameters query a short date field in an access database
Could i please have some assistance around taking off the time element in
the parameters.
ThankyouI have an example at www.msbicentral.com under downloads, RS, RDLs... The
file name is Parameters.FormattedDates.RDL
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Tango" <Tango@.discussions.microsoft.com> wrote in message
news:4F30DFD9-897B-495A-B75D-A043E75A61D3@.microsoft.com...
> Hi,
> I have a start date (date/time) parameter with a default value of
> =DateTime.Today & a end date (date/time) parameter with a default value of
> =DateTime.Today.adddays(7)
> These parameters query a short date field in an access database
> Could i please have some assistance around taking off the time element in
> the parameters.
> Thankyou|||Thanks Wayne.
Nice site...
"Wayne Snyder" wrote:
> I have an example at www.msbicentral.com under downloads, RS, RDLs... The
> file name is Parameters.FormattedDates.RDL
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Tango" <Tango@.discussions.microsoft.com> wrote in message
> news:4F30DFD9-897B-495A-B75D-A043E75A61D3@.microsoft.com...
> > Hi,
> >
> > I have a start date (date/time) parameter with a default value of
> > =DateTime.Today & a end date (date/time) parameter with a default value of
> > =DateTime.Today.adddays(7)
> > These parameters query a short date field in an access database
> > Could i please have some assistance around taking off the time element in
> > the parameters.
> > Thankyou
>
>

Format date parameter input

Hi,
I have a report with 2 datetime parameters (a between). The report users
need to be able
to input the date in the format dd/mm/yyyy istead of mm/dd/yyyy. Does anyone
know how this can be done?
I have looked for this in all the topics about this at the forum, but there
is nobody with an answer. Is this possible at all in Reporting Services?
Gr.
Mike KlaarhamerThere must be several possible solutions. One is to define the parameter in
the report as a string type, then use it accordingly in your dataset query.
Charles Kangai, MCT, MCDBA
"Mike Klaarhamer" wrote:
> Hi,
> I have a report with 2 datetime parameters (a between). The report users
> need to be able
> to input the date in the format dd/mm/yyyy istead of mm/dd/yyyy. Does anyone
> know how this can be done?
> I have looked for this in all the topics about this at the forum, but there
> is nobody with an answer. Is this possible at all in Reporting Services?
> Gr.
> Mike Klaarhamer
>|||Open report in visual studio, in the property grid select "report" and change
the "language" property to "English(United Kingdom)". That should do it.
"Mike Klaarhamer" wrote:
> Hi,
> I have a report with 2 datetime parameters (a between). The report users
> need to be able
> to input the date in the format dd/mm/yyyy istead of mm/dd/yyyy. Does anyone
> know how this can be done?
> I have looked for this in all the topics about this at the forum, but there
> is nobody with an answer. Is this possible at all in Reporting Services?
> Gr.
> Mike Klaarhamer
>|||I had this problem once and I had to monkey around with both Paulb's
suggestion and setting the SQL Server up in as 'British' a way possible.
"Paulb" wrote:
> Open report in visual studio, in the property grid select "report" and change
> the "language" property to "English(United Kingdom)". That should do it.
> "Mike Klaarhamer" wrote:
> > Hi,
> >
> > I have a report with 2 datetime parameters (a between). The report users
> > need to be able
> > to input the date in the format dd/mm/yyyy istead of mm/dd/yyyy. Does anyone
> > know how this can be done?
> > I have looked for this in all the topics about this at the forum, but there
> > is nobody with an answer. Is this possible at all in Reporting Services?
> >
> > Gr.
> >
> > Mike Klaarhamer
> >|||Hi,
Non of the suggested solutions worked :(
The report-language has nothing to do with the parameter-language, it stays
British.
Also when i convert the date to a string which has the good date-value, it
stays British in the parameter :(
Has anyone an other answer to this problem'
Thanx,
Mike Klaarhamer
"CraigyBoop" wrote:
> I had this problem once and I had to monkey around with both Paulb's
> suggestion and setting the SQL Server up in as 'British' a way possible.
> "Paulb" wrote:
> > Open report in visual studio, in the property grid select "report" and change
> > the "language" property to "English(United Kingdom)". That should do it.
> >
> > "Mike Klaarhamer" wrote:
> >
> > > Hi,
> > >
> > > I have a report with 2 datetime parameters (a between). The report users
> > > need to be able
> > > to input the date in the format dd/mm/yyyy istead of mm/dd/yyyy. Does anyone
> > > know how this can be done?
> > > I have looked for this in all the topics about this at the forum, but there
> > > is nobody with an answer. Is this possible at all in Reporting Services?
> > >
> > > Gr.
> > >
> > > Mike Klaarhamer
> > >|||Mike, I'm relatively new to RS (like a lot of people) but what worked for me
was the following SQL statement:
SELECT CONVERT(varchar, GETDATE() - 30, 1) AS StartTimeWanted,
CONVERT(varchar, GETDATE(), 1) AS StopTimeWanted
First, I put this in dataset called dsDateSelect. Then I went to the Report
Parameters screen and added two report parameters. One named StartTimeWanted
and the other StopTimeWanted. These parameters are strings on the Report
Parameters screen and my dataset query is being used to get the default
values. (The first is 30 days prior to whatever is the current date and the
second is today's date)
Please note that I am using a Transact SQL statement against a SQL Server
2000 database and the supported function CONVERT uses the style 1 to format
the date in mm/dd/yy format. If I wanted four digits for the year, I would
change the style to 101. My language is set to US.
If you are using a database (Oracle, DB2, Informix, MySQL, etc.), I am not
sure what functions you can use to format the date in your dataset's SQL
statement but the above worked for me.
Thanks.
"Mike Klaarhamer" wrote:
> Hi,
> Non of the suggested solutions worked :(
> The report-language has nothing to do with the parameter-language, it stays
> British.
> Also when i convert the date to a string which has the good date-value, it
> stays British in the parameter :(
> Has anyone an other answer to this problem'
> Thanx,
> Mike Klaarhamer
> "CraigyBoop" wrote:
> > I had this problem once and I had to monkey around with both Paulb's
> > suggestion and setting the SQL Server up in as 'British' a way possible.
> >
> > "Paulb" wrote:
> >
> > > Open report in visual studio, in the property grid select "report" and change
> > > the "language" property to "English(United Kingdom)". That should do it.
> > >
> > > "Mike Klaarhamer" wrote:
> > >
> > > > Hi,
> > > >
> > > > I have a report with 2 datetime parameters (a between). The report users
> > > > need to be able
> > > > to input the date in the format dd/mm/yyyy istead of mm/dd/yyyy. Does anyone
> > > > know how this can be done?
> > > > I have looked for this in all the topics about this at the forum, but there
> > > > is nobody with an answer. Is this possible at all in Reporting Services?
> > > >
> > > > Gr.
> > > >
> > > > Mike Klaarhamer
> > > >|||Mike, you also might check to see if Reporting Services Service Pack 1 has
been installed. I can't find the reference for it at this moment, but I did
read that SP1 does correct something with regard to the dd/mm/yy format.
Good luck and let us know how things turn out.
"Pat R." wrote:
> Mike, I'm relatively new to RS (like a lot of people) but what worked for me
> was the following SQL statement:
> SELECT CONVERT(varchar, GETDATE() - 30, 1) AS StartTimeWanted,
> CONVERT(varchar, GETDATE(), 1) AS StopTimeWanted
> First, I put this in dataset called dsDateSelect. Then I went to the Report
> Parameters screen and added two report parameters. One named StartTimeWanted
> and the other StopTimeWanted. These parameters are strings on the Report
> Parameters screen and my dataset query is being used to get the default
> values. (The first is 30 days prior to whatever is the current date and the
> second is today's date)
> Please note that I am using a Transact SQL statement against a SQL Server
> 2000 database and the supported function CONVERT uses the style 1 to format
> the date in mm/dd/yy format. If I wanted four digits for the year, I would
> change the style to 101. My language is set to US.
> If you are using a database (Oracle, DB2, Informix, MySQL, etc.), I am not
> sure what functions you can use to format the date in your dataset's SQL
> statement but the above worked for me.
> Thanks.
> "Mike Klaarhamer" wrote:
> > Hi,
> >
> > Non of the suggested solutions worked :(
> > The report-language has nothing to do with the parameter-language, it stays
> > British.
> > Also when i convert the date to a string which has the good date-value, it
> > stays British in the parameter :(
> > Has anyone an other answer to this problem'
> >
> > Thanx,
> >
> > Mike Klaarhamer
> >
> > "CraigyBoop" wrote:
> >
> > > I had this problem once and I had to monkey around with both Paulb's
> > > suggestion and setting the SQL Server up in as 'British' a way possible.
> > >
> > > "Paulb" wrote:
> > >
> > > > Open report in visual studio, in the property grid select "report" and change
> > > > the "language" property to "English(United Kingdom)". That should do it.
> > > >
> > > > "Mike Klaarhamer" wrote:
> > > >
> > > > > Hi,
> > > > >
> > > > > I have a report with 2 datetime parameters (a between). The report users
> > > > > need to be able
> > > > > to input the date in the format dd/mm/yyyy istead of mm/dd/yyyy. Does anyone
> > > > > know how this can be done?
> > > > > I have looked for this in all the topics about this at the forum, but there
> > > > > is nobody with an answer. Is this possible at all in Reporting Services?
> > > > >
> > > > > Gr.
> > > > >
> > > > > Mike Klaarhamer
> > > > >|||Hi I also use the CONVERT function the following works for me: -
I Create an input paramter as string type linked to a dataset which provides
the source with the string formated as follows dd MMM YYYY
Then CONVERT(DATETIME,@.fDate,102) or CONVERT(DATETIME,@.fDate,103)
These tend to format the parameter as follows: -
YYYY/MM/DD or YYY/DD/MM depending on input
Regards
"Pat R." wrote:
> Mike, you also might check to see if Reporting Services Service Pack 1 has
> been installed. I can't find the reference for it at this moment, but I did
> read that SP1 does correct something with regard to the dd/mm/yy format.
> Good luck and let us know how things turn out.
> "Pat R." wrote:
> > Mike, I'm relatively new to RS (like a lot of people) but what worked for me
> > was the following SQL statement:
> >
> > SELECT CONVERT(varchar, GETDATE() - 30, 1) AS StartTimeWanted,
> > CONVERT(varchar, GETDATE(), 1) AS StopTimeWanted
> >
> > First, I put this in dataset called dsDateSelect. Then I went to the Report
> > Parameters screen and added two report parameters. One named StartTimeWanted
> > and the other StopTimeWanted. These parameters are strings on the Report
> > Parameters screen and my dataset query is being used to get the default
> > values. (The first is 30 days prior to whatever is the current date and the
> > second is today's date)
> >
> > Please note that I am using a Transact SQL statement against a SQL Server
> > 2000 database and the supported function CONVERT uses the style 1 to format
> > the date in mm/dd/yy format. If I wanted four digits for the year, I would
> > change the style to 101. My language is set to US.
> >
> > If you are using a database (Oracle, DB2, Informix, MySQL, etc.), I am not
> > sure what functions you can use to format the date in your dataset's SQL
> > statement but the above worked for me.
> >
> > Thanks.
> >
> > "Mike Klaarhamer" wrote:
> >
> > > Hi,
> > >
> > > Non of the suggested solutions worked :(
> > > The report-language has nothing to do with the parameter-language, it stays
> > > British.
> > > Also when i convert the date to a string which has the good date-value, it
> > > stays British in the parameter :(
> > > Has anyone an other answer to this problem'
> > >
> > > Thanx,
> > >
> > > Mike Klaarhamer
> > >
> > > "CraigyBoop" wrote:
> > >
> > > > I had this problem once and I had to monkey around with both Paulb's
> > > > suggestion and setting the SQL Server up in as 'British' a way possible.
> > > >
> > > > "Paulb" wrote:
> > > >
> > > > > Open report in visual studio, in the property grid select "report" and change
> > > > > the "language" property to "English(United Kingdom)". That should do it.
> > > > >
> > > > > "Mike Klaarhamer" wrote:
> > > > >
> > > > > > Hi,
> > > > > >
> > > > > > I have a report with 2 datetime parameters (a between). The report users
> > > > > > need to be able
> > > > > > to input the date in the format dd/mm/yyyy istead of mm/dd/yyyy. Does anyone
> > > > > > know how this can be done?
> > > > > > I have looked for this in all the topics about this at the forum, but there
> > > > > > is nobody with an answer. Is this possible at all in Reporting Services?
> > > > > >
> > > > > > Gr.
> > > > > >
> > > > > > Mike Klaarhamer
> > > > > >

format date parameter

Hi,
I need to format a datetime parameter to date only but it does not seem to
work for me. My parameter = DateAdd("d",-5,Format(Now(),"d")) but it it
includes time. I need to convert it to MM d yyyy.
Thanks,
JimJim,
If the parameter type is set to DateTime, it will always display the time
even if not given. This is because the SQL database type datetime always
includes this data. If you want, you can change the parameter type to
string, and it will be displayed however you format the date in your default
calculation.
David
"Jim" wrote:
> Hi,
> I need to format a datetime parameter to date only but it does not seem to
> work for me. My parameter = DateAdd("d",-5,Format(Now(),"d")) but it it
> includes time. I need to convert it to MM d yyyy.
> Thanks,
> Jim|||That does the trick. Thanks David.
"David Siebert" wrote:
> Jim,
> If the parameter type is set to DateTime, it will always display the time
> even if not given. This is because the SQL database type datetime always
> includes this data. If you want, you can change the parameter type to
> string, and it will be displayed however you format the date in your default
> calculation.
> David
> "Jim" wrote:
> > Hi,
> >
> > I need to format a datetime parameter to date only but it does not seem to
> > work for me. My parameter = DateAdd("d",-5,Format(Now(),"d")) but it it
> > includes time. I need to convert it to MM d yyyy.
> >
> > Thanks,
> > Jim

Format date in a Stored Procedure

Dear friends,

I have a stored procedure that returns some fiels. One of the fields is a datetime type.

The field return in the follow format : 2006-11-13 0:00:00

How can I return only 2006-11-13? How can I use the format function?

regards!!!

declare @.someDate datetime
set @.someDate = getdate()
select cast(datepart(yyyy, @.someDate) as varchar) + '-' + cast(datepart(mm, @.someDate) as varchar) + '-' + cast(datepart(dd, @.someDate) as varchar)

result: 2006-11-13

|||

Or...

Select Convert(varchar(10), GetDate(), 120)

Lookup the convert function in Books On Line for more formats.

|||

Here is another method:

select DATEADD(DAY, 0, DATEDIFF(DAY, 0, GETDATE()))

This keeps the datatype as a datetime while removing the date from the string. It is best practice to format the data in the UI.

|||

Dear friens,

First, let me thank for all your support.

And the last question about this problem, How can get the system current time in format hh:mm? (ex: 12:30)

Thanks

|||You should not care about formatting the date on the server this is a thing for the presentation layer.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||ok, but how I return the time value of the system?|||Do you mean at the presentation layer ? That depends on your used coding language, with .NET you will date various options on the Date type.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

For example: I want to create the follow stored procedure:

CREATE PROCEDURE TEST

@.ID INT

AS

UPDATE TABLE1 SET MyFieldTime=@.MySystemTime WHERE MyFieldID=@.ID

Understood?

I want to save in my database th system time...

Thanks!!

|||Depending on which datatype you use in the column you can′t separate the date and the time. Datetime is a combined type storing date as well as time. Is it against any rules storing the date additionally ?

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

I save the time field in teh database is a nchar(5) as for example: 12:30, 22:30, 08h00

Thanks!!