Solved

changing format of getdate to include yyy-mon-....

Posted on 2014-09-16
5
170 Views
Last Modified: 2014-09-16
how can you change the normal getdate() to the a format like:
2014Aug1808301403394

specifically, how to change 08 to Aug like in the above example and how to more precision beyond millisecond?

this is for sql 2012

thanks
0
Comment
Question by:25112
5 Comments
 
LVL 24

Assisted Solution

by:Phillip Burton
Phillip Burton earned 167 total points
ID: 40325361
1. To get "Aug", you should use:

select left(datename(mm,getdate()),3)

2. To get more milliseconds, instead of using Getdate(), you should use:

select sysdatetime()
0
 
LVL 48

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 166 total points
ID: 40325365
It is this that you want?
SELECT CONVERT(VARCHAR, GETDATE(), 109)

Open in new window

0
 
LVL 21

Accepted Solution

by:
Randy Poole earned 167 total points
ID: 40325372
Select format(getdate(),'yyyy MMM dd HH mm ss FFFFFFF')

Open in new window

This will also give you up to 6 digits 3 for milliseconds and 3 for macroseconds
0
 
LVL 5

Author Comment

by:25112
ID: 40325421
thanks - we are getting close.
 
    Select format(sysdatetime() ,'yyyy MMM dd HH mm ss FFFFFFF')
  Select format(GETDATE() ,'yyyy MMM dd HH mm ss FFFFFFF')
  gives
2014 Sep 16 09 46 43 7699724
2014 Sep 16 09 46 43 767 (4 precision less than the above)

what we need is ( without the 2 extra digits with sysdatetime)
2014 Sep 16 09 46 43 767__
0
 
LVL 5

Author Comment

by:25112
ID: 40325478
sorry- I do see it now.


   Select format(sysdatetime() ,'yyyy MMM dd HH mm ss FFFFF')

thanks all.
0

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

861 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