Q:

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", but what about the code for "yyyy.mm.dd.hh.mm.ss"?
This is on SQL 2005.

A:

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?

A:

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)

