Datetime pattern for yyyy.mm.dd.hh.mm.ss pattern code?

What is the DATE FORMAT CODE for "yyyy.mm.dd.hh.mm.ss"?

I know that 34 (date format code) is "yyyymmddhhmmss", b开发者_如何学JAVAut what about the code for "yyyy.mm.dd.hh.mm.ss"?

This is on SQL 2005.

---

CAST and CONVERT on MSDN says "no".

You have to CONVERT twice with styles 102 and 108, with a concatenation and REPLACE.

Where did you get the "34" date format code from?

---

As gbn said, using one of the existing formats with some string concatenation would work. Another option is:

SELECT
    CAST(YEAR(my_date) AS CHAR(4)) + '.' +
    RIGHT('0' + CAST(MONTH(my_date) AS VARCHAR(2)), 2) + '.' +
    RIGHT('0' + CAST(DAY(my_date) AS VARCHAR(2)), 2) + '.' +
    RIGHT('0' + CAST(DATEPART(HOUR, my_date) AS VARCHAR(2)), 2) + '.' +
    RIGHT('0' + CAST(DATEPART(MINUTE, my_date) AS VARCHAR(2)), 2) + '.' +
    RIGHT('0' + CAST(DATEPART(SECOND, my_date) AS VARCHAR(2)), 2)

---

Considering

 SELECT CONVERT(VARCHAR(16), GETDATE(), 120) AS [YYYY-MM-DD] 
 ---returns--- yyyy-mm-dd hh:ss

and you can use REPLACE to convert strings

 REPLACE('a b c',' ','.')  
 ----returns--- a.b.c

and you can recursively stack things you get to this

Select (
    replace((replace((replace(CONVERT(VARCHAR(16), GETDATE(), 120),' ','.')), ':', '.')), '-', '.')
    )

which returns: yyyy.mm.dd.hh.ss

works great for datetime stamps or filenames!