Solved

Using CAST to turn dates into DD/MM/YYY

Posted on 2010-09-02
9
521 Views
Last Modified: 2012-05-10
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
Comment
Question by:BernardGBailey
  • 5
  • 4
9 Comments
 
LVL 32

Expert Comment

by:ewangoya
ID: 33591287
use
SELECT CONVERT(VARCHAR(10), GETDATE(), 103)
0
 

Author Comment

by:BernardGBailey
ID: 33591302
Thanks for your prompt response ewangoya, but you forgot about this

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

Cheers
0
 
LVL 32

Accepted Solution

by:
ewangoya earned 500 total points
ID: 33591434
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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

Author Comment

by:BernardGBailey
ID: 33591487
Again this can only be put in the WHERE section of a query

0
 
LVL 32

Expert Comment

by:ewangoya
ID: 33591499
select your fields
where field x > dbo.fnFormatDate(GETDATE(), 'DD/MM/YYYY')
0
 
LVL 32

Expert Comment

by:ewangoya
ID: 33591506
The function can be used in any part of your query
Thanks
0
 

Author Comment

by:BernardGBailey
ID: 33591631
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
 
LVL 32

Expert Comment

by:ewangoya
ID: 33591676
Whats the target database?
I kind of assumed it was SQl Server
0
 

Author Comment

by:BernardGBailey
ID: 33591731
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question