How to define the datetime format ?

Hi,

I think that to define the datetime format I should use sp_configure or something like that.
So how I can do to set the datetime format such as YYYYMMDDhhmmss ?

TIA
LVL 1
Richard Coffree-commerce Product ManagerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

rajesh009Commented:
Hi,

    Why don't you have the format as it is? You can define the column datatype as just datetime....

Rajesh.
Dishan FernandoSoftware Engineer / DBACommented:
HI
SELECT REPLACE(REPLACE(REPLACE(CONVERT( varchar(20),GETDATE(),120),'-',''),' ',''),':','')

from MSDN

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_2f3o.asp

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Richard Coffree-commerce Product ManagerAuthor Commented:
Because I just defined my column as a smalldatetime and when I display the values of the table, the format used is : MM/DD/YYYY hh AM/PM
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Dishan FernandoSoftware Engineer / DBACommented:
Not clear what u r saying :(
If the Col is smalldatetime and It shows as follows..
2003-03-21 00:00:00 (when u selecting)

Time part is discard. What is the format u want to get??
ShogunWadeCommented:
the default display format of a date is dependend on the user's language setting.

I have spend countless hours (and countless discussions) in the past trying to find a way to modify the date format for specific languages (which are held in syslanguages) but IMHO it is impossible.

I may be mistaken, but i dont belive that YYYYMMDDhhmmss  is a recognised datetime format in SQL anyway. so you will probably need to implement something like dish has suggested.  
ShogunWadeCommented:
one interesting question is why to you want to include seconds in your custom format, since your field is stored as smalldatetime and the resolution of that datatype is 1 minute ?
cryptosidCommented:
i think there is a function in VBA called format() use this function to display date the way u wish
namasi_navaretnamCommented:
But the display on the client is controled by controlpanel date settings.

Goto Control Panel. Select "Regional Options" and then select "Date" tab to set prefered date format. Othersise, you will need to convert the dates yourself using convert functions.
namasi_navaretnamCommented:
To set it permanently on the server do:
sp_configure SET DATEFORMAT dmy
GO
Then Restart SQL server

HTH

Namasi
namasi_navaretnamCommented:
To set it permanently on the server do:
sp_configure SET DATEFORMAT ymd
GO
Then Restart SQL server

HTH

Namasi
ShogunWadeCommented:
surely using SET DATEFORMAT only applies when setting dates  ie parsing strings to dates  not the opposite way.  which is by default from the lanauage settings.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.