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

Question

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.

Answer 1

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?

Answer 2

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)