help me to add 01-JAN-13 12.00.00 AM this type of format

Question

2.50/5 (2 votes)

See more:

SQL

I want to add 01-JAN-13 12.00.00 AM this type of format in my columns .. i have take Date time as my datatype ...

Posted 16-May-13 7:39am

Neil Sukalikar

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2013-05-16T08:15:00

Solution 2

It's probably not a good idea to do it in SQL - this kind of thing is normally better done in the presentation software, because it can tell what type of format the user expects, if nothing else in terms of month names, which your indicated format indicates.

But, if you must do it, then I would suggest to write a stored procedure (or better a user function) to do the conversion, because it is going to require a couple of function calls. The easiest way to do it is to convert the DateTime value to a VARCHAR then use the SUBSTRING function repeatedly to get exactly the format you want - there is not standard format that shows the way you want.

I would suggest you start with

SQL

CONVERT(VARCHAR(26), dateValue, 109)

As this gives you all the bits you need, but you may find a more suitable format style here: http://www.sql-server-helper.com/tips/date-formats.aspx[^]

Style 109 will give you

Jan 01 2013 12:00:00.000AM

So it's a relatively trivial matter to carve out the bits you want with SUBSTRING and reorder them into a result string.

As I said, it's better done in the presentation software.

Posted 16-May-13 8:15am

OriginalGriff

Comments

Corporal Agarn 16-May-13 15:29pm

Well said, a lot better than mine.

[no name] 16-May-13 21:20pm

The one you suggest @OriginalGriff ... its giving me an error

i tried this query

Insert into Employee
values('Jhon','Abraham',1000000,CONVERT (varchar(20),1-1-2013,107),'Banking')

I am using sql server 2008 r2
and here this is my created table query

Create Table Employee
(
Employee_Id int Primary key identity(1,1),
First_Name varchar(50),
Last_Name varchar(50),
Salary decimal,
Joining_Date DateTime,
Department varchar(50)
)

i used date time for that perticular column

OriginalGriff 17-May-13 3:38am

You do not need to use CONVERT in order to save data into a DateTime field - you do not want to do any formatting on it at all!
A DateTime is stored as a number of milliseconds since a specific time in the past, and has no format associated with it. All you have to do is supply it to SQL as a string (or preferably as a DateTime Value via a parameterized query)

So
INSERT INTO Employee (myDateColumn) VALUES ('2013-01-01')
will work fine since SQL accepts ISO (yyyy-MM-dd) format dates by default. If you have to, you can use SET DATEFORMAT to specify it, but using ISO is a much better idea.

It is a much better practice to do conversion of dates to DateTime elements in the presentation software, because it can tell if 09-11-2001 is 9th Nov or 11th Sep by looking at the locale set on the users equipment: SQL can't because it generally isn't on that machine!

[no name] 17-May-13 5:12am

k Thank you so much

OriginalGriff 17-May-13 5:22am

You're welcome!

Corporal Agarn · Answer 2 · 2013-05-16T08:03:00

Solution 1

SQL server saves that data as it wishes. The default display is 2013-01-01 12.00.00.
You can change that by using CONVERT

Posted 16-May-13 8:03am

Corporal Agarn