Solved

Using CAST to turn dates into DD/MM/YYY

Posted on 2010-09-02
9
525 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 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL trigger 5 48
(sql serv16)ssis 2016 question/check 1 123
SQL Server maintenance plan 8 61
Question about Common Table Expressions 3 39
Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.

738 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