Convert Date (dd-MM-YYYY) to (dd/MM/YYYY)

Question

Convert Date (dd-MM-YYYY) to (dd/MM/YYYY)

gunapriyan

SSC Rookie

Points: 42
More actions
October 17, 2010 at 10:56 pm

#227981

Dear Sir,
In my DB, there is 5000 date values are stored like "DD-MM-YYYY" Format in nvarchar datatype. For Example 18-10-2010
And when i convert into date format for the following purpose.
"Select * from tbl_master order by Convert(datetime,date_of_Appln,103)"
Its not working.
Can you help me to convert? or Can you help me to replace the "-" symbol to "/"?
Thanks in advance.
Regards,
Guna

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004683 More actions · Answer 1

My recommendation is to lookup the word REPLACE in Books Online. It'll show you how to easily do such a thing. I'd show you the actual code but this is simple enough where we can do the "teach a man to fish" thing.

As a sidebar, this is why folks shouldn't store dates as NVARCHAR or anything other than a DATETIME datatype.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

gunapriyan SSC Rookie Points: 42 More actions · Answer 2

Dear Sir,

Thanks for your reply.

Actually I am developing a software with the help of VB 6.0 and SQL Server 2005.

In Date Part, Initially i have put DateTime Picker Control. And my client dont want that. So I have changed to Textbox and I also change the datatype in database.

And nearly 5000 records are added now. Now i am facing that problem.

Can you do favour for me?

Thanks & Regards,

Guna

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 3

Recommendations:

1) Change the data type back to datetime. You're asking for a world of hurt with it a varchar. Validations, date manipulation, etc.

2) Look up CONVERT in SQL Books Online. That will allow you to display the date in any format you like

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Brandie Tarvin SSC Guru Points: 173119 More actions · Answer 4

You need to follow both Gail's and Jeff's advice. You can embed a REPLACE inside of a CONVERT to fix the hypen problem, then switch that column to datetime or smalldatetime.

It's bad to let a date be anything other than a date. If the client wants a text field in the application, you can convert it back to text there. But don't let it reside as such in the database.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Jeff Moden SSC Guru Points: 1004683 More actions · Answer 5

gunapriyan (10/17/2010)
Dear Sir,
Thanks for your reply.
Actually I am developing a software with the help of VB 6.0 and SQL Server 2005.
In Date Part, Initially i have put DateTime Picker Control. And my client dont want that. So I have changed to Textbox and I also change the datatype in database.
And nearly 5000 records are added now. Now i am facing that problem.
Can you do favour for me?
Thanks & Regards,
Guna

As Gail and the others have stated, you've done yourself a great dis-service by changing the datatype to a character based datatype. Formatting of a date should be done by the GUI if you have one so that local date formatting controls can come into play in this global environment we live in.

If you insist on continuing with character based dates (and you really, really shouldn't insist on that), then your original query will do with a slight mod...

Select REPLACE(date_of_Appln.'-','/') AS Display_date_of_Appln, * from tbl_master order by Convert(datetime,date_of_Appln,103)"

Rest assured that the above code will always be relatively slow because there's no chance of an INDEX SEEK ever occuring because of the function you've done an ORDER BY.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)