?
Solved

date conversion

Posted on 2005-02-24
11
Medium Priority
?
463 Views
Last Modified: 2008-02-01
select convert(varchar(12),getdate(),112)
gets me this:  20050224

What's the right way to add the hhmmssms to the end of it?

does anybody have a good reference to all the valid datetime conversion styles?
0
Comment
Question by:RLLewis
[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
  • 3
  • +1
11 Comments
 
LVL 26

Expert Comment

by:Hilaire
ID: 13393743
Could you give this a try ?
select replace(replace(replace(convert(varchar(30), getdate(), 121), '-', ''), ':', ''), '.', '')

URL for convert styles
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_2f3o.asp
0
 
LVL 15

Expert Comment

by:Colosseo
ID: 13393759
Hi

how about this

select convert(varchar(12),getdate(),112) + replace(convert(varchar(12),getdate(),114),':','')

You will find a good reference in SQL Books online just search for CONVERT

Cheers

Scott
0
 
LVL 1

Author Comment

by:RLLewis
ID: 13394306
Yes, Colosseo, I live and breathe in BOL, but I just haven't found what I'm looking for.  
And Hilaire, I'm getting this:  20050224103854363
Can we get this:  20050224.103854363

and thank you very much for the convert styles.  i guess i thought there would be something more out there other than what's in bol.  
0
Get MongoDB database support online, now!

At Percona’s web store you can order your MongoDB database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card. Handle your MongoDB database support now!

 
LVL 26

Expert Comment

by:Hilaire
ID: 13394354
you could modify Colosseo's code as follows to insert the missing '.'

select convert(varchar(10), getdate(), 112)+ '.' + replace(convert(varchar(12), getdate(), 114), ':', '')
0
 
LVL 26

Expert Comment

by:Hilaire
ID: 13394369
note : this is merely a variant of Colosseo's code, please don't accept my comment as an answer.
0
 
LVL 15

Accepted Solution

by:
Colosseo earned 300 total points
ID: 13394371
Hi

Yeah BOL is mucho handy :)

you can do this which should give you what you are after...

select convert(varchar(12),getdate(),112) + '.' + replace(convert(varchar(12),getdate(),114),':','')

Cheers

Scott
0
 

Expert Comment

by:tortega_2001
ID: 13394674
Use this query :
 SELECT convert(varchar(12),getdate(),112) + DATENAME(hour, getdate()) + DATENAME(minute, getdate()) + DATENAME(second, getdate()) + DATENAME(millisecond, getdate())  
Result:
20050224101146827
 Teo.
0
 
LVL 1

Author Comment

by:RLLewis
ID: 13395239
Teo, that's a whole lotta tsql for what gives me precisely the same value as ther other much shorter version.
Colosseo/Hilaire - I'm getting this now:20050224.120705627

why the ':'  ?

0
 
LVL 15

Expert Comment

by:Colosseo
ID: 13395263
can you post the t-sql you are using?

I just used select convert(varchar(12),getdate(),112) + '.' + replace(convert(varchar(12),getdate(),114),':','')

and got

20050224.180902667

Cheers

Scott
0
 
LVL 1

Author Comment

by:RLLewis
ID: 13395740
that is precisely what i got.  i am asking why is the colon there?  
0
 
LVL 1

Author Comment

by:RLLewis
ID: 13399478
thank you hilaire and colosseo
0

Featured Post

How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
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 SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

765 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