asked on

Using CAST to turn dates into DD/MM/YYY

Hi team,

I'm stuck on a query function. My query can only use CAST and cannot use CONVERT to convert the GETDATE() function into a character format DD/MM/YYY. I can get the following code

,'NBL '+'0'+CAST(DAY(GETDATE()) AS VARCHAR(2))+'/'+'0'+CAST(MONTH(GETDATE()) AS VARCHAR(2))+'/'+cast(CAST(GETDATE() AS date) as varchar(4)) as DD/MM/YY

to return

03/09/2010 ( where this is 3rd September 2010 (today))

This won't work when the day and month are greater than 9.

I can only use this code in the 'WHERE' section of the query.

Any pointers?
Cheers
Bernard

Ephraim Wangoya

use
SELECT CONVERT(VARCHAR(10), GETDATE(), 103)

BernardGBailey

ASKER

Thanks for your prompt response ewangoya, but you forgot about this

"My query can only use CAST and cannot use CONVERT".

Cheers

ASKER CERTIFIED SOLUTION

Ephraim Wangoya

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

BernardGBailey

ASKER

Again this can only be put in the WHERE section of a query

Ephraim Wangoya

select your fields
where field x > dbo.fnFormatDate(GETDATE(), 'DD/MM/YYYY')

Ephraim Wangoya

The function can be used in any part of your query
Thanks

BernardGBailey

ASKER

Thanks again ewangoya,

But this query is not going to run in SQL Server, so I can't use the function you have provided.

It would be great if I could use this, but I can't.

Cheers
Bernard

Ephraim Wangoya

Whats the target database?
I kind of assumed it was SQl Server

BernardGBailey

ASKER

Its your lucky day ewangoya

I worked it out

'NBL '+SubString(cast(CAST(getdate() as date)as varchar(10)),9,2)+'/'+
SubString(cast(CAST(getdate() as date)as varchar(10)),6,2)+'/'+
SubString(cast(CAST(getdate() as date)as varchar(10)),1,4)

This returns the right character format for me.

The application is GoldBox 7, a middleware application which works on a SQL Server 2008 database. It essentially provides a medium to import/export xBase files for reprocessing in GoldMine.

Unfortunately itt doesn't have CONVERT, but does have CAST.

Thanks for your assistance.

Cheers
Bernard