DateTime Format Problem: Setting to 16/Mar/2006 8:50:00 AM

my Current DateTime Format is 01/08/2006 9:15:00 AM

i want to set it to 01/Aug/2006 9:15:00 AM

what parameter will be pass in "SET DATEFORMAT"?

plz help and give me a chance of thanks.

You can't.

Shouldn't be trying to use SQL Server to do presentation formating anyhow.

|||

thanksMotley !

Idon't want to use SQL Server to do presentation formating . I want to fix this format for storage of DateTime data any time. I mean that any time any one tries to insert the DateTime data in my Database, that data should be store in my required format.

|||

Datetimes don't have a "format" (Ok, technically they do, but not a string format). They are stored as the number the days (and fractions there of) since the epoch. It does not store the months, years, hours, minutes, or seconds, let alone store them in any user-specified "format".

The fact that query analyzer/management studio/visual studio presents that value to you in a particular string format is for your benefit as a matter of presentation (AKA presentation formatting). SQL Server does have some capability in being able to convert to/from a datetime and varchar, but it's fairly rudimentary since you really shouldn't be doing presentation formatting in the database.

It's similiar to wanting to store the number of dollars of something into a table and asking how to get an integer field to accept the format "$5,000". It can't. That's not the databases job, that's for the presentation layer of your application to do (Convert $5,000 to 5000).

|||

again Thanks Lot Motley!

I have 2 different machines. Both having the SQL Server 2000 Professional Editon and OS = Windows XP.

One of the machine(M1) displays datetime like 16/Mar/2006 8:50:00 AM

while another machine(M2) has format 16/03/2006 8:50:00 AM

how to set same format for M2 as M1.

|||

As far as how the system displays it, it is probably a Window's setting. Have a look in Regional and Language Options from Control Panel - there is one for Long Formats, and one for Short. I don't think that how it is displayed is controlled by SQL Server, since as the other chap mentioned, SQL Server doesn't store dates in any kind of "format".

Otherwise, you can specify a format from SQL Server, but it will convert it from the datetime object to a varchar. You do this using the CONVERT function:

CONVERT

(varchar(12),p.date_effective,106)

The final parameter, 106 in this case, is the datetime format you want to use. A complete list of them is available at this site:

http://sqljunkies.com/Article/6676BEAE-1967-402D-9578-9A1C7FD826E5.scuk

Hope this helps!

|||As dominic mentioned, whatever application you are using to view the data is probably getting your preferred date format from the system settings of the client machine. You can set that in the control panel->Regional and Language Options->Customize->Date(Tab)->Short Date Format->dd-MMM-yyyy|||

yeah this was the solution.

Thanks Motley.