Solved

How to define the datetime format ?

Posted on 2003-12-12
14
845 Views
Last Modified: 2012-05-04
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
0
Comment
Question by:Richard Coffre
  • 4
  • 3
  • 2
  • +3
14 Comments
 
LVL 6

Expert Comment

by:rajesh009
Comment Utility
Hi,

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

Rajesh.
0
 
LVL 8

Accepted Solution

by:
dishanf earned 168 total points
Comment Utility
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
 
LVL 1

Author Comment

by:Richard Coffre
Comment Utility
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
 
LVL 8

Expert Comment

by:dishanf
Comment Utility
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
 
LVL 18

Expert Comment

by:ShogunWade
Comment Utility
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
 
LVL 18

Expert Comment

by:ShogunWade
Comment Utility
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 5

Expert Comment

by:cryptosid
Comment Utility
i think there is a function in VBA called format() use this function to display date the way u wish
0
 
LVL 15

Assisted Solution

by:namasi_navaretnam
namasi_navaretnam earned 166 total points
Comment Utility
0
 
LVL 15

Expert Comment

by:namasi_navaretnam
Comment Utility
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
 
LVL 15

Expert Comment

by:namasi_navaretnam
Comment Utility
To set it permanently on the server do:
sp_configure SET DATEFORMAT dmy
GO
Then Restart SQL server

HTH

Namasi
0
 
LVL 15

Expert Comment

by:namasi_navaretnam
Comment Utility
To set it permanently on the server do:
sp_configure SET DATEFORMAT ymd
GO
Then Restart SQL server

HTH

Namasi
0
 
LVL 18

Assisted Solution

by:ShogunWade
ShogunWade earned 166 total points
Comment Utility
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

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now