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?
 
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
0
 
rajesh009Commented:
Hi,

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

Rajesh.
0
 
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
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
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??
0
 
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.  
0
 
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 ?
0
 
cryptosidCommented:
i think there is a function in VBA called format() use this function to display date the way u wish
0
 
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.
0
 
namasi_navaretnamCommented:
To set it permanently on the server do:
sp_configure SET DATEFORMAT dmy
GO
Then Restart SQL server

HTH

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

HTH

Namasi
0
 
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.