Formatting Date/Time

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

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

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

- Thanks in advance.

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

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

-- Robert

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

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

-- Robert

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!!