Solved

How to define the datetime format ?

Posted on 2003-12-12
14
865 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
ID: 9927315
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
ID: 9927337
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
ID: 9927340
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
ID: 9927376
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
ID: 9927380
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
ID: 9927393
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 5

Expert Comment

by:cryptosid
ID: 9927449
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
ID: 9927900
0
 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 9927932
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
ID: 9928011
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
ID: 9928015
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
ID: 9941210
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

895 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

16 Experts available now in Live!

Get 1:1 Help Now