• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 465
  • Last Modified:

date conversion

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
RLLewis
Asked:
RLLewis
  • 4
  • 3
  • 3
  • +1
1 Solution
 
HilaireCommented:
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
 
ColosseoCommented:
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
 
RLLewisAuthor Commented:
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
HilaireCommented:
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
 
HilaireCommented:
note : this is merely a variant of Colosseo's code, please don't accept my comment as an answer.
0
 
ColosseoCommented:
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
 
tortega_2001Commented:
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
 
RLLewisAuthor Commented:
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
 
ColosseoCommented:
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
 
RLLewisAuthor Commented:
that is precisely what i got.  i am asking why is the colon there?  
0
 
RLLewisAuthor Commented:
thank you hilaire and colosseo
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 4
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now