Date Format -YYYYMMDD HH24MISS.MS4

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Date Format -YYYYMMDD HH24MISS.MS4

Author

Topic

shanmugaraj
Posting Yak Master

219 Posts

Posted - 2012-09-11 : 01:44:47

Hi,
Need Convert Function convert the date format of an field as below
Select OrderDate from Table

OrderDate : 2012-08-21 17:45:24.557

Expected Data : 20120821 174524.5570

YYYYMMDD HH24MISS.MS4 where
YYYY - Year
MM - Month
DD - Day
HH24 - Hours in the 24hr format
MI - Minutes
SS - Seconds
MS4 - Milliseconds which is rounded to 4 decimal places

-Thanks in advance
Raj

mani_1234
Starting Member

24 Posts

Posted - 2012-09-11 : 03:12:52

select replace(replace(CONVERT(varchar(23),getdate(),121),'-',''),':','')

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2012-09-11 : 08:59:04

[code]select DT=right(replace(replace(convert(varchar(30),convert(datetime2(4),'2012-08-21 17:45:24.557'),121),'-',''),':',''),21)[/code]
Results:
[code]DT
---------------------
20120821 174524.5570[/code]

CODO ERGO SUM

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-09-13 : 03:19:56

Where do you want to show data? If you use front end application, do formation there.

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources