Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How to define the datetime format ?

Posted on 2003-12-12
14
Medium Priority
?
934 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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:
Dishan Fernando earned 672 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
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.

 
LVL 8

Expert Comment

by:Dishan Fernando
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
 
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 664 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 664 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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

705 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