Date format in Report Design

[JohnOB]

I am new to SQL report writing services, and am designing a report using the Microsoft Visual Studio add-in that comes with it.

I have pulled in the date, using the function =DateString however it is displaying in the format MM/DD/YYYY but I would like it to display DD/MM/YYYY but I cannot find a format option for this.

Thanks


"Stupid isn't not knowing the answer, it's not asking the question

 

[andrea96]

You should be able to change the Format property of the textbox in the report to dd/MM/yyyy.

 

[JohnOB]

I have tried typing dd/mm/yyyy in the format properties but this does not work, it seems to be looking for function formula in here. I cannot see anywhere else where I can enter dd/mm/yyyy

Thanks

"Stupid isn't not knowing the answer, it's not asking the question

 

[xlbo]

The format properties is exactly where it needs to go

If ity is not applying the format properly, maybe you don;t have a real date - a string masquarading as a date perhaps....


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[JohnOB]

The date isn't part of my data, it is appearing as a result of the =DateString function, which I understood shows todays date, though not sure if it shows it as a string or not.

"Stupid isn't not knowing the answer, it's not asking the question

 

[xlbo]

Google "DateString"

1st hit:

http://msdn2.microsoft.com/en-us/library/96b2b77h(VS.80).aspx

Visual Basic Language Reference
DateString Property

Returns or sets a String value representing the current date according to your system.

It is therefore pretty obvious that you have a STRING which cannot be formatted

As per the page referenced:

To get the current system date or time in the format of your locale, or in a custom format, supply the Now Property to the Format Function, specifying either Predefined Date/Time Formats (Format Function) or User-Defined Date/Time Formats (Format Function). The following example demonstrates this.

MsgBox("The formatted date is " & Format(Now, "dddd, d MMM yyyy"))

To access the current system date as a Date, use the Today Property.

Please try searching and doing some research before posting

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[ablackwe]

In the format propoerty, you need to type dd/MM/yyyy. Remember to use uppercase MM

Thanks

Adam Blackwell
Business Intelligence Analyst

http://www.uk.insight.com/

 

[JohnOB]

I tried this as you suggest but unfortunately this has not worked, perhaps it is because this is simply a string as the previous comments suggest.

Thanks anyway

"Stupid isn't not knowing the answer, it's not asking the question

 

[compuveg]

Personally I would have the SP or other datasource return the date in the desired format. Here's a little script I wroteup to quickly show the different formats available.
It works on SQL 2000


declare @FmtInt as Int
SET @FmtInt = 100
print 'date format = ' + cast(@FmtInt as varchar(4))
print convert(varchar(20),getdate(),@FmtInt)
print ' '
SET @FmtInt = @FmtInt + 1
print 'date format = ' + cast(@FmtInt as varchar(4))
print convert(varchar(20),getdate(),@FmtInt)
print ' '
SET @FmtInt = @FmtInt + 1
print 'date format = ' + cast(@FmtInt as varchar(4))
print convert(varchar(20),getdate(),@FmtInt)
print ' '
SET @FmtInt = @FmtInt + 1
print 'date format = ' + cast(@FmtInt as varchar(4))
print convert(varchar(20),getdate(),@FmtInt)
print ' '
SET @FmtInt = @FmtInt + 1
print 'date format = ' + cast(@FmtInt as varchar(4))
print convert(varchar(20),getdate(),@FmtInt)
print ' '
SET @FmtInt = @FmtInt + 1
print 'date format = ' + cast(@FmtInt as varchar(4))
print convert(varchar(20),getdate(),@FmtInt)
print ' '
SET @FmtInt = @FmtInt + 1
print 'date format = ' + cast(@FmtInt as varchar(4))
print convert(varchar(20),getdate(),@FmtInt)
print ' '
SET @FmtInt = @FmtInt + 1
print 'date format = ' + cast(@FmtInt as varchar(4))
print convert(varchar(20),getdate(),@FmtInt)
print ' '
SET @FmtInt = @FmtInt + 1
print 'date format = ' + cast(@FmtInt as varchar(4))
print convert(varchar(20),getdate(),@FmtInt)
print ' '
SET @FmtInt = @FmtInt + 1
print 'date format = ' + cast(@FmtInt as varchar(4))
print convert(varchar(20),getdate(),@FmtInt)
print ' '
SET @FmtInt = @FmtInt + 1
print 'date format = ' + cast(@FmtInt as varchar(4))
print convert(varchar(20),getdate(),@FmtInt)
print ' '
SET @FmtInt = @FmtInt + 1
print 'date format = ' + cast(@FmtInt as varchar(4))
print convert(varchar(20),getdate(),@FmtInt)
print ' '
SET @FmtInt = @FmtInt + 1
print 'date format = ' + cast(@FmtInt as varchar(4))
print convert(varchar(20),getdate(),@FmtInt)
print ' '
SET @FmtInt = @FmtInt + 1
print 'date format = ' + cast(@FmtInt as varchar(4))
print convert(varchar(20),getdate(),@FmtInt)
print ' '
SET @FmtInt = @FmtInt + 1
print 'date format = ' + cast(@FmtInt as varchar(4))
print convert(varchar(20),getdate(),@FmtInt)
print ' '
SET @FmtInt = @FmtInt + 1
print 'date format = ' + cast(@FmtInt as varchar(4))
print convert(varchar(20),getdate(),@FmtInt)
print ' '
SET @FmtInt = @FmtInt + 1
print 'date format = ' + cast(@FmtInt as varchar(4))
print convert(varchar(20),getdate(),@FmtInt)
print ' '
SET @FmtInt = @FmtInt + 1
print 'date format = ' + cast(@FmtInt as varchar(4))
print convert(varchar(20),getdate(),@FmtInt)
print ' '
SET @FmtInt = @FmtInt + 1
print 'date format = ' + cast(@FmtInt as varchar(4))
print convert(varchar(20),getdate(),@FmtInt)
print ' '
SET @FmtInt = @FmtInt + 1
print 'date format = ' + cast(@FmtInt as varchar(4))
print convert(varchar(20),getdate(),@FmtInt)
print ' '
SET @FmtInt = @FmtInt + 1
print 'date format = ' + cast(@FmtInt as varchar(4))
print convert(varchar(20),getdate(),@FmtInt)
print ' '
SET @FmtInt = @FmtInt + 1
print 'date format = ' + cast(@FmtInt as varchar(4))
print convert(varchar(20),getdate(),@FmtInt)
print ' '
SET @FmtInt = @FmtInt + 1
print 'date format = ' + cast(@FmtInt as varchar(4))
print convert(varchar(20),getdate(),@FmtInt)
print ' '
SET @FmtInt = @FmtInt + 1
print 'date format = ' + cast(@FmtInt as varchar(4))
print convert(varchar(20),getdate(),@FmtInt)
print ' '
SET @FmtInt = @FmtInt + 1
print 'date format = ' + cast(@FmtInt as varchar(4))
print convert(varchar(20),getdate(),@FmtInt)
print ' '
SET @FmtInt = @FmtInt + 1
print 'date format = ' + cast(@FmtInt as varchar(4))
print convert(varchar(20),getdate(),@FmtInt)
print ' '
SET @FmtInt = @FmtInt + 1
print 'date format = ' + cast(@FmtInt as varchar(4))
print convert(varchar(20),getdate(),@FmtInt)
print ' '
SET @FmtInt = @FmtInt + 1
print 'date format = ' + cast(@FmtInt as varchar(4))
print convert(varchar(20),getdate(),@FmtInt)
print ' '
SET @FmtInt = @FmtInt + 1
print 'date format = ' + cast(@FmtInt as varchar(4))
print convert(varchar(20),getdate(),@FmtInt)
print ' '
SET @FmtInt = @FmtInt + 1
print 'date format = ' + cast(@FmtInt as varchar(4))
print convert(varchar(20),getdate(),@FmtInt)
print ' '
SET @FmtInt = @FmtInt + 1