• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 536
  • Last Modified:

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
0
BernardGBailey
Asked:
BernardGBailey
  • 5
  • 4
1 Solution
 
Ephraim WangoyaCommented:
use
SELECT CONVERT(VARCHAR(10), GETDATE(), 103)
0
 
BernardGBaileyAuthor Commented:
Thanks for your prompt response ewangoya, but you forgot about this

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

Cheers
0
 
Ephraim WangoyaCommented:
Here is a nice function I came across

CREATE FUNCTION fnFormatDate (@Datetime DATETIME, @FormatMask VARCHAR(32))

RETURNS VARCHAR(32)

AS

BEGIN

    DECLARE @StringDate VARCHAR(32)

    SET @StringDate = @FormatMask

    IF (CHARINDEX ('YYYY',@StringDate) > 0)

       SET @StringDate = REPLACE(@StringDate, 'YYYY',

                         DATENAME(YY, @Datetime))

    IF (CHARINDEX ('YY',@StringDate) > 0)

       SET @StringDate = REPLACE(@StringDate, 'YY',

                         RIGHT(DATENAME(YY, @Datetime),2))

    IF (CHARINDEX ('Month',@StringDate) > 0)

       SET @StringDate = REPLACE(@StringDate, 'Month',

                         DATENAME(MM, @Datetime))

    IF (CHARINDEX ('MON',@StringDate COLLATE SQL_Latin1_General_CP1_CS_AS)>0)

       SET @StringDate = REPLACE(@StringDate, 'MON',

                         LEFT(UPPER(DATENAME(MM, @Datetime)),3))

    IF (CHARINDEX ('Mon',@StringDate) > 0)

       SET @StringDate = REPLACE(@StringDate, 'Mon',

                                     LEFT(DATENAME(MM, @Datetime),3))

    IF (CHARINDEX ('MM',@StringDate) > 0)

       SET @StringDate = REPLACE(@StringDate, 'MM',

                  RIGHT('0'+CONVERT(VARCHAR,DATEPART(MM, @Datetime)),2))

    IF (CHARINDEX ('M',@StringDate) > 0)

       SET @StringDate = REPLACE(@StringDate, 'M',

                         CONVERT(VARCHAR,DATEPART(MM, @Datetime)))

    IF (CHARINDEX ('DD',@StringDate) > 0)

       SET @StringDate = REPLACE(@StringDate, 'DD',

                         RIGHT('0'+DATENAME(DD, @Datetime),2))

    IF (CHARINDEX ('D',@StringDate) > 0)

       SET @StringDate = REPLACE(@StringDate, 'D',

                                     DATENAME(DD, @Datetime))  

RETURN @StringDate

END

use as
select dbo.fnFormatDate(GETDATE(), 'DD/MM/YYYY')

0
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

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

0
 
Ephraim WangoyaCommented:
select your fields
where field x > dbo.fnFormatDate(GETDATE(), 'DD/MM/YYYY')
0
 
Ephraim WangoyaCommented:
The function can be used in any part of your query
Thanks
0
 
BernardGBaileyAuthor Commented:
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
0
 
Ephraim WangoyaCommented:
Whats the target database?
I kind of assumed it was SQl Server
0
 
BernardGBaileyAuthor Commented:
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
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now