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!