[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Using CAST to turn dates into DD/MM/YYY

Posted on 2010-09-02
9
Medium Priority
?
535 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 32

Expert Comment

by:Ephraim Wangoya
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:
Ephraim Wangoya earned 2000 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
Industry Leaders: 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!

 

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:Ephraim Wangoya
ID: 33591499
select your fields
where field x > dbo.fnFormatDate(GETDATE(), 'DD/MM/YYYY')
0
 
LVL 32

Expert Comment

by:Ephraim Wangoya
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:Ephraim Wangoya
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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

649 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