SQL Server T-SQL：将字符串日期转换为DateTime

ybzsozfc 于 2022-12-10 发布在其他

关注(0)|答案(4)|浏览(134)

My string dates are as follows in YearMonth format:

The following is my CONVERT statement in my stored procedure:

CONVERT (datetime, @FileName, 104)

My results are showing up like:

10/7/2020
10/8/2020
10/9/2020
...

How do I get my results to show up as:

07/2010
08/2010
09/2010
...

Its obviously the 104 code in my CONVERT statement. What code should I use?
Thanks

sql-server

来源：https://stackoverflow.com/questions/22545955/t-sql-convert-string-date-to-datetime

4条答案

按热度按时间

vsnjm48y1#

使用112代替104。从docs：
112 =年年月日
这似乎与您的格式非常匹配。您唯一需要做的就是添加'01'：

CONVERT (datetime, @FileName + '01', 112)

赞(0）回复(0）举报 2022-12-10

bbuxkriu2#

也许你会满意这样的东西：

SELECT CAST(DATEPART(MONTH,CONVERT(DATETIME, @FileName, 104)) AS VARCHAR)+'/'+CAST(DATEPART(YEAR,CONVERT(DATETIME, @FileName, 104)) AS VARCHAR)

赞(0）回复(0）举报 2022-12-10

cyej8jka3#

Run this you will get an entire table of the datetime format constants from T-SQL supported by exec results!

<!DOCTYPE html>

<html lang="en" xmlns="http://www.w3.org/1999/xhtml">
<head>
    <meta charset="utf-8" />
    <title></title>
    <style>
        .sH1 {
            font-size: 18px;
            font-family: inherit;
            margin: 0px;
            padding: 0px 0.5rem;
            font-style: normal;
            font-weight: 700;
            text-align: left;
            text-transform: none;
            color: rgb(68, 68, 68);
            border: 1px solid rgb(221, 221, 221);
            background: rgb(230, 230, 230);
            line-height: 1.5;
            width: 58.025px;
        }
        .sH2 {
            font-size: 18px;
            font-family: inherit;
            margin: 0px;
            padding: 0px 0.5rem;
            font-style: normal;
            font-weight: 700;
            text-align: left;
            text-transform: none;
            color: rgb(68, 68, 68);
            border: 1px solid rgb(221, 221, 221);
            background: rgb(230, 230, 230);
            line-height: 1.5;
            width: 234.438px;
        }
        .sH3 {
            font-size: 18px;
            font-family: inherit;
            margin: 0px;
            padding: 0px 0.5rem;
            font-style: normal;
            font-weight: 700;
            text-align: left;
            text-transform: none;
            color: rgb(68, 68, 68);
            border: 1px solid rgb(221, 221, 221);
            background: rgb(230, 230, 230);
            line-height: 1.5;
            width: 65.7px;
        }
        .sH4 {
            font-size: 18px;
            font-family: inherit;
            margin: 0px;
            padding: 0px 0.5rem;
            font-style: normal;
            font-weight: 700;
            text-align: left;
            text-transform: none;
            color: rgb(68, 68, 68);
            border: 1px solid rgb(221, 221, 221);
            background: rgb(230, 230, 230);
            line-height: 1.5;
            width: 364.837px;
        }
        .sD1 {
            font-size: 18px;
            font-family: inherit;
            margin: 0px;
            padding: 0px 0.5rem;
            white-space: normal;
            vertical-align: top;
            border: 1px solid rgb(221, 221, 221);
            line-height: 1.5;
            width: 58.025px;
        }
        .sD2 {
            font-size: 18px;
            font-family: inherit;
            margin: 0px;
            padding: 0px 0.5rem;
            white-space: normal;
            vertical-align: top;
            border: 1px solid rgb(221, 221, 221);
            line-height: 1.5;
            width: 234.438px;
        }
        .sD3 {
            font-size: 18px;
            font-family: inherit;
            margin: 0px;
            padding: 0px 0.5rem;
            white-space: normal;
            vertical-align: top;
            border: 1px solid rgb(221, 221, 221);
            line-height: 1.5;
            width: 65.7px;
        }
        .sD4 {
            font-size: 18px;
            font-family: inherit;
            margin: 0px;
            padding: 0px 0.5rem;
            white-space: normal;
            vertical-align: top;
            border: 1px solid rgb(221, 221, 221);
            line-height: 1.5;
            width: 364.837px;
        }
        .Hdr4 {
            font-size: 20px;
            font-family: 'Nunito Sans', 'Open Sans', sans-serif;
            margin: 0px;
            padding: 0px;
            font-weight: 700;
            color: rgb(51, 51, 51);
            line-height: 33.4px;
            font-style: normal;
            font-variant-ligatures: normal;
            font-variant-caps: normal;
            letter-spacing: normal;
            orphans: 2;
            text-align: left;
            text-indent: 0px;
            text-transform: none;
            white-space: normal;
            widows: 2;
            word-spacing: 0px;
            -webkit-text-stroke-width: 0px;
            background-color: rgb(255, 255, 255);
            text-decoration-thickness: initial;
            text-decoration-style: initial;
            text-decoration-color: initial;
        }
        .sTH1 {
            font-size: 18px;
            font-family: inherit;
            margin: 0px;
            padding: 0px 0.5rem;
            font-style: normal;
            font-weight: 700;
            text-align: left;
            text-transform: none;
            color: rgb(68, 68, 68);
            border: 1px solid rgb(221, 221, 221);
            background: rgb(230, 230, 230);
            line-height: 1.5;
            width: 57.2875px;
        }
        .sTH2 {
            font-size: 18px;
            font-family: inherit;
            margin: 0px;
            padding: 0px 0.5rem;
            font-style: normal;
            font-weight: 700;
            text-align: left;
            text-transform: none;
            color: rgb(68, 68, 68);
            border: 1px solid rgb(221, 221, 221);
            background: rgb(230, 230, 230);
            line-height: 1.5;
            width: 254.175px;
        }
        .sTH3 {
            font-size: 18px;
            font-family: inherit;
            margin: 0px;
            padding: 0px 0.5rem;
            font-style: normal;
            font-weight: 700;
            text-align: left;
            text-transform: none;
            color: rgb(68, 68, 68);
            border: 1px solid rgb(221, 221, 221);
            background: rgb(230, 230, 230);
            line-height: 1.5;
            width: 58.6375px;
        }
        .sTH4 {
            font-size: 18px;
            font-family: inherit;
            margin: 0px;
            padding: 0px 0.5rem;
            font-style: normal;
            font-weight: 700;
            text-align: left;
            text-transform: none;
            color: rgb(68, 68, 68);
            border: 1px solid rgb(221, 221, 221);
            background: rgb(230, 230, 230);
            line-height: 1.5;
            width: 352.9px;
        }
      .sTD1 {
            font-size: 18px;
            font-family: inherit;
            margin: 0px;
            padding: 0px 0.5rem;
            white-space: normal;
            vertical-align: top;
            border: 1px solid rgb(221, 221, 221);
            line-height: 1.5;
            width: 57.2875px;
        }
        .sTD2 {
            font-size: 18px;
            font-family: inherit;
            margin: 0px;
            padding: 0px 0.5rem;
            white-space: normal;
            vertical-align: top;
            border: 1px solid rgb(221, 221, 221);
            line-height: 1.5;
            width: 254.175px;
        }
        .sTD3 {
            font-size: 18px;
            font-family: inherit;
            margin: 0px;
            padding: 0px 0.5rem;
            white-space: normal;
            vertical-align: top;
            border: 1px solid rgb(221, 221, 221);
            line-height: 1.5;
            width: 58.6375px;
        }
        .sTD4 {
            font-size: 18px;
            font-family: inherit;
            margin: 0px;
            padding: 0px 0.5rem;
            white-space: normal;
            vertical-align: top;
            border: 1px solid rgb(221, 221, 221);
            line-height: 1.5;
            width: 352.9px;
        }
        .sTB {
            font-size: 1.2rem;
            font-family: inherit;
            border-collapse: collapse;
            border-spacing: 0px;
            width: 768px;
            border: 0px;
            empty-cells: show;
            max-width: 100%;
        }
        .sDiv {
            font-size: 18px;

            color: rgb(34, 34, 34);
            font-style: normal;
            font-variant-ligatures: normal;
            font-variant-caps: normal;
            font-weight: 400;
            letter-spacing: normal;
        }
        sH4 {
            font-size: 18px;
            font-family: inherit;
            margin: 0px;
            font-style: normal;
            font-weight: 700;
            text-align: left;
            text-transform: none;
            color: rgb(68, 68, 68);
            border: 1px solid rgb(221, 221, 221);
            background: rgb(230, 230, 230);
            line-height: 1.5;
            width: 426px;
        }
        .sD4 {
            font-size: 18px;
            font-family: inherit;
            margin: 0px;
            white-space: normal;
            vertical-align: top;
            border: 1px solid rgb(221, 221, 221);
            line-height: 1.5;
            width: 426px;
        }
        .sRow {
            font-size: 12px;
            font-family: inherit;
            border-bottom: 1px solid rgb(233, 233, 233);
        }
        .sTbody{font-size: 12px; font-family: inherit;}
    </style>
</head>
<body>
    <div class="tableScroll" class="sDiv">
        <table class="sTB">
            <thead class="sTbody">
                <tr class="sRow">
                    <th class="sH1">Style</th>
                    <th class="sH2">Example</th>
                    <th class="sH3">Length</th>
                    <th class="sH4">Syntax</th>
                </tr>
            </thead>
            <tbody class="sTbody">
                <tr class="sRow">
                    <td class="sD1">112</td>
                    <td class="sD2">20180731</td>
                    <td class="sD3">8</td>
                    <td class="sD4">convert(varchar, {ts'2018-07-31 16:51:38.253'}, 112)</td>
                </tr>
                <tr class="sRow">
                    <td class="sD1">12</td>
                    <td class="sD2">180731</td>
                    <td class="sD3">6</td>
                    <td class="sD4">convert(varchar, {ts'2018-07-31 16:51:38.253'}, 12)</td>
                </tr>
            </tbody>
        </table>
    </div>
    <h4 class="Hdr4">
        <br style="font-size: 20px; font-family: inherit;" />
        / Slash delimited</h4>
    <div class="tableScroll" class="sDiv">
        <table class="sTB">
            <thead class="sTbody">
                <tr class="sRow">
                    <th class="sH1">Style</th>
                    <th class="sH2">Example</th>
                    <th class="sH3">Length</th>
                    <th class="sH4">Syntax</th>
                </tr>
            </thead>
            <tbody class="sTbody">
                <tr style="font-size: 12px; font-family: inherit; border-bottom: 1px solid rgb(233, 233, 233); background-color: rgb(249, 251, 253);">
                    <td class="sD1">111</td>
                    <td class="sD2">2018/07/31</td>
                    <td class="sD3">10</td>
                    <td class="sD4">convert(varchar, {ts'2018-07-31 16:51:38.253'},111)</td>
                </tr>
                <tr class="sRow">
                    <td class="sD1">11</td>
                    <td class="sD2">18/07/31</td>
                    <td class="sD3">8</td>
                    <td class="sD4">convert(varchar, {ts'2018-07-31 16:51:38.253'}, 11)</td>
                </tr>
                <tr class="sRow">
                    <td class="sD1">101</td>
                    <td class="sD2">07/31/2018</td>
                    <td class="sD3">10</td>
                    <td class="sD4">convert(varchar, {ts'2018-07-31 16:51:38.253'}, 101)</td>
                </tr>
                <tr class="sRow">
                    <td class="sD1">1</td>
                    <td class="sD2">07/31/18</td>
                    <td class="sD3">8</td>
                    <td class="sD4">convert(varchar, {ts'2018-07-31 16:51:38.253'}, 1)</td>
                </tr>
                <tr class="sRow">
                    <td class="sD1">103</td>
                    <td class="sD2">31/07/2018</td>
                    <td class="sD3">10</td>
                    <td class="sD4">convert(varchar, {ts'2018-07-31 16:51:38.253'}, 103)</td>
                </tr>
                <tr class="sRow">
                    <td class="sD1">3</td>
                    <td class="sD2">31/07/18</td>
                    <td class="sD3">8</td>
                    <td class="sD4">convert(varchar, {ts'2018-07-31 16:51:38.253'}, 3)</td>
                </tr>
            </tbody>
        </table>
    </div>
    <h4 class="Hdr4">
        <br style="font-size: 20px; font-family: inherit;" />
        . Dot delimited</h4>
    <div class="tableScroll" class="sDiv">
        <table class="sTB">
            <thead class="sTbody">
                <tr class="sRow">
                    <th class="sH1">Style</th>
                    <th class="sH2">Example</th>
                    <th class="sH3">Length</th>
                    <th class="sH4">Syntax</th>
                </tr>
            </thead>
            <tbody class="sTbody">
                <tr class="sRow">
                    <td class="sD1">102</td>
                    <td class="sD2">2018.07.31</td>
                    <td class="sD3">10</td>
                    <td class="sD4">convert(varchar, {ts'2018-07-31 16:51:38.253'}, 102)</td>
                </tr>
                <tr class="sRow">
                    <td class="sD1">2</td>
                    <td class="sD2">18.07.31</td>
                    <td class="sD3">8</td>
                    <td class="sD4">convert(varchar, {ts'2018-07-31 16:51:38.253'}, 2)</td>
                </tr>
                <tr class="sRow">
                    <td class="sD1">104</td>
                    <td class="sD2">31.07.2018</td>
                    <td class="sD3">10</td>
                    <td class="sD4">convert(varchar, {ts'2018-07-31 16:51:38.253'}, 104)</td>
                </tr>
                <tr class="sRow">
                    <td class="sD1">4</td>
                    <td class="sD2">31.07.18</td>
                    <td class="sD3">8</td>
                    <td class="sD4">convert(varchar, {ts'2018-07-31 16:51:38.253'}, 4)</td>
                </tr>
            </tbody>
        </table>
    </div>
    <h4 class="Hdr4">
        <br style="font-size: 20px; font-family: inherit;" />
        - Dash delimited</h4>
    <div class="tableScroll" class="sDiv">
        <table class="sTB">
            <thead class="sTbody">
                <tr class="sRow">
                    <th class="sH1">Style</th>
                    <th class="sH2">Example</th>
                    <th class="sH3">Length</th>
                    <th class="sH4">Syntax</th>
                </tr>
            </thead>
            <tbody class="sTbody">
                <tr class="sRow">
                    <td class="sD1">121</td>
                    <td class="sD2">2018-07-31 16:51:38.253</td>
                    <td class="sD3">23</td>
                    <td class="sD4">convert(varchar, {ts'2018-07-31 16:51:38.253'}, 121)</td>
                </tr>
                <tr class="sRow">
                    <td class="sD1">120</td>
                    <td class="sD2">2018-07-31 16:51:38</td>
                    <td class="sD3">19</td>
                    <td class="sD4">convert(varchar, {ts'2018-07-31 16:51:38.253'}, 120)</td>
                </tr>
                <tr class="sRow">
                    <td class="sD1">110</td>
                    <td class="sD2">07-31-2018</td>
                    <td class="sD3">10</td>
                    <td class="sD4">convert(varchar, {ts'2018-07-31 16:51:38.253'}, 110)</td>
                </tr>
                <tr class="sRow">
                    <td class="sD1">10</td>
                    <td class="sD2">07-31-18</td>
                    <td class="sD3">8</td>
                    <td class="sD4">convert(varchar, {ts'2018-07-31 16:51:38.253'}, 10)</td>
                </tr>
                <tr class="sRow">
                    <td class="sD1">105</td>
                    <td class="sD2">31-07-2018</td>
                    <td class="sD3">10</td>
                    <td class="sD4">convert(varchar, {ts'2018-07-31 16:51:38.253'}, 105)</td>
                </tr>
                <tr class="sRow">
                    <td class="sD1">5</td>
                    <td class="sD2">31-07-18</td>
                    <td class="sD3">8</td>
                    <td class="sD4">convert(varchar, {ts'2018-07-31 16:51:38.253'}, 5)</td>
                </tr>
            </tbody>
        </table>
    </div>
    <h4 class="Hdr4">
        <br style="font-size: 20px; font-family: inherit;" />
        Space delimited</h4>
    <div class="tableScroll" class="sDiv">
        <table class="sTB">
            <thead class="sTbody">
                <tr class="sRow">
                    <th class="sTH1">Style</th>
                    <th class="sTH2">Example</th>
                    <th class="sTH3">Length</th>
                    <th class="sTH4">Syntax</th>
                </tr>
            </thead>
            <tbody class="sTbody">
                <tr class="sRow">
                    <td class="sTD1">9</td>
                    <td class="sTD2">Jul 31 2018 4:51:38:253PM</td>
                    <td class="sTD3">26</td>
                    <td class="sTD4">convert(varchar, {ts'2018-07-31 16:51:38.253'}, 9)</td>
                </tr>
                <tr class="sRow">
                    <td class="sTD1">109</td>
                    <td class="sTD2">Jul 31 2018 4:51:38:253PM</td>
                    <td class="sTD3">26</td>
                    <td class="sTD4">convert(varchar, {ts'2018-07-31 16:51:38.253'},109)</td>
                </tr>
                <tr class="sRow">
                    <td class="sTD1">100</td>
                    <td class="sTD2">Jul 31 2018 4:51PM</td>
                    <td class="sTD3">19</td>
                    <td class="sTD4">convert(varchar, {ts'2018-07-31 16:51:38.253'},100)</td>
                </tr>
                <tr class="sRow">
                    <td class="sTD1">107</td>
                    <td class="sTD2">Jul 31, 2018</td>
                    <td class="sTD3">12</td>
                    <td class="sTD4">convert(varchar, {ts'2018-07-31 16:51:38.253'},107)</td>
                </tr>
                <tr class="sRow">
                    <td class="sTD1">7</td>
                    <td class="sTD2">Jul 31, 18</td>
                    <td class="sTD3">10</td>
                    <td class="sTD4">convert(varchar, {ts'2018-07-31 16:51:38.253'},7)</td>
                </tr>
                <tr class="sRow">
                    <td class="sTD1">13</td>
                    <td class="sTD2">31 Jul 2018 16:51:38:253</td>
                    <td class="sTD3">24</td>
                    <td class="sTD4">convert(varchar, {ts'2018-07-31 16:51:38.253'},13)</td>
                </tr>
                <tr class="sRow">
                    <td class="sTD1">113</td>
                    <td class="sTD2">31 Jul 2018 16:51:38:253</td>
                    <td class="sTD3">24</td>
                    <td class="sTD4">convert(varchar, {ts'2018-07-31 16:51:38.253'},113)</td>
                </tr>
                <tr class="sRow">
                    <td class="sTD1">106</td>
                    <td class="sTD2">31 Jul 2018</td>
                    <td class="sTD3">11</td>
                    <td class="sTD4">convert(varchar, {ts'2018-07-31 16:51:38.253'},106)</td>
                </tr>
                <tr class="sRow">
                    <td class="sTD1">6</td>
                    <td class="sTD2">31 Jul 18</td>
                    <td class="sTD3">9</td>
                    <td class="sTD4">convert(varchar, {ts'2018-07-31 16:51:38.253'},6)</td>
                </tr>
            </tbody>
        </table>
    </div>
    <h4 class="Hdr4">
        <br style="font-size: 20px; font-family: inherit;" />
        Time Only</h4>
    <div class="tableScroll" class="sDiv">
        <table class="sTB">
            <thead class="sTbody">
                <tr class="sRow">
                    <th class="sH1">Style</th>
                    <th class="sH2">Example</th>
                    <th class="sH3">Length</th>
                    <th class="sH4">Syntax</th>
                </tr>
            </thead>
            <tbody class="sTbody">
                <tr class="sRow">
                    <td class="sD1">14</td>
                    <td class="sD2">16:51:38:253</td>
                    <td class="sD3">12</td>
                    <td class="sD4">convert(varchar, {ts'2018-07-31 16:51:38.253'}, 14)</td>
                </tr>
                <tr class="sRow">
                    <td class="sD1">114</td>
                    <td class="sD2">16:51:38:253</td>
                    <td class="sD3">12</td>
                    <td class="sD4">convert(varchar, {ts'2018-07-31 16:51:38.253'}, 114)</td>
                </tr>
                <tr class="sRow">
                    <td class="sD1">8</td>
                    <td class="sD2">16:51:38</td>
                    <td class="sD3">8</td>
                    <td class="sD4">convert(varchar, {ts'2018-07-31 16:51:38.253'}, 8)</td>
                </tr>
                <tr class="sRow">
                    <td class="sD1">108</td>
                    <td class="sD2">16:51:38</td>
                    <td class="sD3">8</td>
                    <td class="sD4">convert(varchar, {ts'2018-07-31 16:51:38.253'}, 108)</td>
                </tr>
            </tbody>
        </table>
    </div>
    <br class="Apple-interchange-newline" />
</body>
</html>

赞(0）回复(0）举报 2022-12-10

qxsslcnc4#

运行此命令，您将从执行结果支持的T-SQL中获得完整的日期时间格式常量表！

<!DOCTYPE html>

<html lang="en" xmlns="http://www.w3.org/1999/xhtml">
<head>
    <meta charset="utf-8" />
    <title></title>
    <style>
        .sH1 {
            font-size: 18px;
            font-family: inherit;
            margin: 0px;
            padding: 0px 0.5rem;
            font-style: normal;
            font-weight: 700;
            text-align: left;
            text-transform: none;
            color: rgb(68, 68, 68);
            border: 1px solid rgb(221, 221, 221);
            background: rgb(230, 230, 230);
            line-height: 1.5;
            width: 58.025px;
        }
        .sH2 {
            font-size: 18px;
            font-family: inherit;
            margin: 0px;
            padding: 0px 0.5rem;
            font-style: normal;
            font-weight: 700;
            text-align: left;
            text-transform: none;
            color: rgb(68, 68, 68);
            border: 1px solid rgb(221, 221, 221);
            background: rgb(230, 230, 230);
            line-height: 1.5;
            width: 234.438px;
        }
        .sH3 {
            font-size: 18px;
            font-family: inherit;
            margin: 0px;
            padding: 0px 0.5rem;
            font-style: normal;
            font-weight: 700;
            text-align: left;
            text-transform: none;
            color: rgb(68, 68, 68);
            border: 1px solid rgb(221, 221, 221);
            background: rgb(230, 230, 230);
            line-height: 1.5;
            width: 65.7px;
        }
        .sH4 {
            font-size: 18px;
            font-family: inherit;
            margin: 0px;
            padding: 0px 0.5rem;
            font-style: normal;
            font-weight: 700;
            text-align: left;
            text-transform: none;
            color: rgb(68, 68, 68);
            border: 1px solid rgb(221, 221, 221);
            background: rgb(230, 230, 230);
            line-height: 1.5;
            width: 364.837px;
        }
        .sD1 {
            font-size: 18px;
            font-family: inherit;
            margin: 0px;
            padding: 0px 0.5rem;
            white-space: normal;
            vertical-align: top;
            border: 1px solid rgb(221, 221, 221);
            line-height: 1.5;
            width: 58.025px;
        }
        .sD2 {
            font-size: 18px;
            font-family: inherit;
            margin: 0px;
            padding: 0px 0.5rem;
            white-space: normal;
            vertical-align: top;
            border: 1px solid rgb(221, 221, 221);
            line-height: 1.5;
            width: 234.438px;
        }
        .sD3 {
            font-size: 18px;
            font-family: inherit;
            margin: 0px;
            padding: 0px 0.5rem;
            white-space: normal;
            vertical-align: top;
            border: 1px solid rgb(221, 221, 221);
            line-height: 1.5;
            width: 65.7px;
        }
        .sD4 {
            font-size: 18px;
            font-family: inherit;
            margin: 0px;
            padding: 0px 0.5rem;
            white-space: normal;
            vertical-align: top;
            border: 1px solid rgb(221, 221, 221);
            line-height: 1.5;
            width: 364.837px;
        }
        .Hdr4 {
            font-size: 20px;
            font-family: &quot;
            Nunito Sans&quot;
            , &quot;
            Open Sans&quot;
            , sans-serif;
            margin: 0px;
            padding: 0px;
            font-weight: 700;
            color: rgb(51, 51, 51);
            line-height: 33.4px;
            font-style: normal;
            font-variant-ligatures: normal;
            font-variant-caps: normal;
            letter-spacing: normal;
            orphans: 2;
            text-align: left;
            text-indent: 0px;
            text-transform: none;
            white-space: normal;
            widows: 2;
            word-spacing: 0px;
            -webkit-text-stroke-width: 0px;
            background-color: rgb(255, 255, 255);
            text-decoration-thickness: initial;
            text-decoration-style: initial;
            text-decoration-color: initial;
        }
        .sTH1 {
            font-size: 18px;
            font-family: inherit;
            margin: 0px;
            padding: 0px 0.5rem;
            font-style: normal;
            font-weight: 700;
            text-align: left;
            text-transform: none;
            color: rgb(68, 68, 68);
            border: 1px solid rgb(221, 221, 221);
            background: rgb(230, 230, 230);
            line-height: 1.5;
            width: 57.2875px;
        }
        .sTH2 {
            font-size: 18px;
            font-family: inherit;
            margin: 0px;
            padding: 0px 0.5rem;
            font-style: normal;
            font-weight: 700;
            text-align: left;
            text-transform: none;
            color: rgb(68, 68, 68);
            border: 1px solid rgb(221, 221, 221);
            background: rgb(230, 230, 230);
            line-height: 1.5;
            width: 254.175px;
        }
        .sTH3 {
            font-size: 18px;
            font-family: inherit;
            margin: 0px;
            padding: 0px 0.5rem;
            font-style: normal;
            font-weight: 700;
            text-align: left;
            text-transform: none;
            color: rgb(68, 68, 68);
            border: 1px solid rgb(221, 221, 221);
            background: rgb(230, 230, 230);
            line-height: 1.5;
            width: 58.6375px;
        }
        .sTH4 {
            font-size: 18px;
            font-family: inherit;
            margin: 0px;
            padding: 0px 0.5rem;
            font-style: normal;
            font-weight: 700;
            text-align: left;
            text-transform: none;
            color: rgb(68, 68, 68);
            border: 1px solid rgb(221, 221, 221);
            background: rgb(230, 230, 230);
            line-height: 1.5;
            width: 352.9px;
        }
      .sTD1 {
            font-size: 18px;
            font-family: inherit;
            margin: 0px;
            padding: 0px 0.5rem;
            white-space: normal;
            vertical-align: top;
            border: 1px solid rgb(221, 221, 221);
            line-height: 1.5;
            width: 57.2875px;
        }
        .sTD2 {
            font-size: 18px;
            font-family: inherit;
            margin: 0px;
            padding: 0px 0.5rem;
            white-space: normal;
            vertical-align: top;
            border: 1px solid rgb(221, 221, 221);
            line-height: 1.5;
            width: 254.175px;
        }
        .sTD3 {
            font-size: 18px;
            font-family: inherit;
            margin: 0px;
            padding: 0px 0.5rem;
            white-space: normal;
            vertical-align: top;
            border: 1px solid rgb(221, 221, 221);
            line-height: 1.5;
            width: 58.6375px;
        }
        .sTD4 {
            font-size: 18px;
            font-family: inherit;
            margin: 0px;
            padding: 0px 0.5rem;
            white-space: normal;
            vertical-align: top;
            border: 1px solid rgb(221, 221, 221);
            line-height: 1.5;
            width: 352.9px;
        }
        .sTB {
            font-size: 1.2rem;
            font-family: inherit;
            border-collapse: collapse;
            border-spacing: 0px;
            width: 768px;
            border: 0px;
            empty-cells: show;
            max-width: 100%;
        }
        .sDiv {
            font-size: 18px;
            font-family: &quot;
            Nunito Sans&quot;
            , &quot;
            Open Sans&quot;
            , sans-serif;
            margin: 0px;
            padding: 0px;
            width: 768px;
            max-width: 768px;
            overflow-x: scroll;
            color: rgb(34, 34, 34);
            font-style: normal;
            font-variant-ligatures: normal;
            font-variant-caps: normal;
            font-weight: 400;
            letter-spacing: normal;
            orphans: 2;
            text-align: left;
            text-indent: 0px;
            text-transform: none;
            white-space: normal;
            widows: 2;
            word-spacing: 0px;
            -webkit-text-stroke-width: 0px;
            background-color: rgb(255, 255, 255);
            text-decoration-thickness: initial;
            text-decoration-style: initial;
            text-decoration-color: initial;
        }
        sH4 {
            font-size: 18px;
            font-family: inherit;
            margin: 0px;
            font-style: normal;
            font-weight: 700;
            text-align: left;
            text-transform: none;
            color: rgb(68, 68, 68);
            border: 1px solid rgb(221, 221, 221);
            background: rgb(230, 230, 230);
            line-height: 1.5;
            width: 426px;
        }
        .sD4 {
            font-size: 18px;
            font-family: inherit;
            margin: 0px;
            white-space: normal;
            vertical-align: top;
            border: 1px solid rgb(221, 221, 221);
            line-height: 1.5;
            width: 426px;
        }
    </style>
</head>
<body>
    <div class="tableScroll" class="sDiv">
        <table class="sTB">
            <thead style="font-size: 12px; font-family: inherit;">
                <tr style="font-size: 12px; font-family: inherit; border-bottom: 1px solid rgb(233, 233, 233);">
                    <th class="sH1">Style</th>
                    <th class="sH2">Example</th>
                    <th class="sH3">Length</th>
                    <th class="sH4">Syntax</th>
                </tr>
            </thead>
            <tbody style="font-size: 12px; font-family: inherit;">
                <tr style="font-size: 12px; font-family: inherit; border-bottom: 1px solid rgb(233, 233, 233);">
                    <td class="sD1">112</td>
                    <td class="sD2">20180731</td>
                    <td class="sD3">8</td>
                    <td class="sD4">convert(varchar, {ts'2018-07-31 16:51:38.253'}, 112)</td>
                </tr>
                <tr style="font-size: 12px; font-family: inherit; border-bottom: 1px solid rgb(233, 233, 233);">
                    <td class="sD1">12</td>
                    <td class="sD2">180731</td>
                    <td class="sD3">6</td>
                    <td class="sD4">convert(varchar, {ts'2018-07-31 16:51:38.253'}, 12)</td>
                </tr>
            </tbody>
        </table>
    </div>
    <h4 class="Hdr4">
        <br style="font-size: 20px; font-family: inherit;" />
        / Slash delimited</h4>
    <div class="tableScroll" class="sDiv">
        <table class="sTB">
            <thead style="font-size: 12px; font-family: inherit;">
                <tr style="font-size: 12px; font-family: inherit; border-bottom: 1px solid rgb(233, 233, 233);">
                    <th class="sH1">Style</th>
                    <th class="sH2">Example</th>
                    <th class="sH3">Length</th>
                    <th class="sH4">Syntax</th>
                </tr>
            </thead>
            <tbody style="font-size: 12px; font-family: inherit;">
                <tr style="font-size: 12px; font-family: inherit; border-bottom: 1px solid rgb(233, 233, 233); background-color: rgb(249, 251, 253);">
                    <td class="sD1">111</td>
                    <td class="sD2">2018/07/31</td>
                    <td class="sD3">10</td>
                    <td class="sD4">convert(varchar, {ts'2018-07-31 16:51:38.253'},111)</td>
                </tr>
                <tr style="font-size: 12px; font-family: inherit; border-bottom: 1px solid rgb(233, 233, 233);">
                    <td class="sD1">11</td>
                    <td class="sD2">18/07/31</td>
                    <td class="sD3">8</td>
                    <td class="sD4">convert(varchar, {ts'2018-07-31 16:51:38.253'}, 11)</td>
                </tr>
                <tr style="font-size: 12px; font-family: inherit; border-bottom: 1px solid rgb(233, 233, 233);">
                    <td class="sD1">101</td>
                    <td class="sD2">07/31/2018</td>
                    <td class="sD3">10</td>
                    <td class="sD4">convert(varchar, {ts'2018-07-31 16:51:38.253'}, 101)</td>
                </tr>
                <tr style="font-size: 12px; font-family: inherit; border-bottom: 1px solid rgb(233, 233, 233);">
                    <td class="sD1">1</td>
                    <td class="sD2">07/31/18</td>
                    <td class="sD3">8</td>
                    <td class="sD4">convert(varchar, {ts'2018-07-31 16:51:38.253'}, 1)</td>
                </tr>
                <tr style="font-size: 12px; font-family: inherit; border-bottom: 1px solid rgb(233, 233, 233);">
                    <td class="sD1">103</td>
                    <td class="sD2">31/07/2018</td>
                    <td class="sD3">10</td>
                    <td class="sD4">convert(varchar, {ts'2018-07-31 16:51:38.253'}, 103)</td>
                </tr>
                <tr style="font-size: 12px; font-family: inherit; border-bottom: 1px solid rgb(233, 233, 233);">
                    <td class="sD1">3</td>
                    <td class="sD2">31/07/18</td>
                    <td class="sD3">8</td>
                    <td class="sD4">convert(varchar, {ts'2018-07-31 16:51:38.253'}, 3)</td>
                </tr>
            </tbody>
        </table>
    </div>
    <h4 class="Hdr4">
        <br style="font-size: 20px; font-family: inherit;" />
        . Dot delimited</h4>
    <div class="tableScroll" class="sDiv">
        <table class="sTB">
            <thead style="font-size: 12px; font-family: inherit;">
                <tr style="font-size: 12px; font-family: inherit; border-bottom: 1px solid rgb(233, 233, 233);">
                    <th class="sH1">Style</th>
                    <th class="sH2">Example</th>
                    <th class="sH3">Length</th>
                    <th class="sH4">Syntax</th>
                </tr>
            </thead>
            <tbody style="font-size: 12px; font-family: inherit;">
                <tr style="font-size: 12px; font-family: inherit; border-bottom: 1px solid rgb(233, 233, 233);">
                    <td class="sD1">102</td>
                    <td class="sD2">2018.07.31</td>
                    <td class="sD3">10</td>
                    <td class="sD4">convert(varchar, {ts'2018-07-31 16:51:38.253'}, 102)</td>
                </tr>
                <tr style="font-size: 12px; font-family: inherit; border-bottom: 1px solid rgb(233, 233, 233);">
                    <td class="sD1">2</td>
                    <td class="sD2">18.07.31</td>
                    <td class="sD3">8</td>
                    <td class="sD4">convert(varchar, {ts'2018-07-31 16:51:38.253'}, 2)</td>
                </tr>
                <tr style="font-size: 12px; font-family: inherit; border-bottom: 1px solid rgb(233, 233, 233);">
                    <td class="sD1">104</td>
                    <td class="sD2">31.07.2018</td>
                    <td class="sD3">10</td>
                    <td class="sD4">convert(varchar, {ts'2018-07-31 16:51:38.253'}, 104)</td>
                </tr>
                <tr style="font-size: 12px; font-family: inherit; border-bottom: 1px solid rgb(233, 233, 233);">
                    <td class="sD1">4</td>
                    <td class="sD2">31.07.18</td>
                    <td class="sD3">8</td>
                    <td class="sD4">convert(varchar, {ts'2018-07-31 16:51:38.253'}, 4)</td>
                </tr>
            </tbody>
        </table>
    </div>
    <h4 class="Hdr4">
        <br style="font-size: 20px; font-family: inherit;" />
        - Dash delimited</h4>
    <div class="tableScroll" class="sDiv">
        <table class="sTB">
            <thead style="font-size: 12px; font-family: inherit;">
                <tr style="font-size: 12px; font-family: inherit; border-bottom: 1px solid rgb(233, 233, 233);">
                    <th class="sH1">Style</th>
                    <th class="sH2">Example</th>
                    <th class="sH3">Length</th>
                    <th class="sH4">Syntax</th>
                </tr>
            </thead>
            <tbody style="font-size: 12px; font-family: inherit;">
                <tr style="font-size: 12px; font-family: inherit; border-bottom: 1px solid rgb(233, 233, 233);">
                    <td class="sD1">121</td>
                    <td class="sD2">2018-07-31 16:51:38.253</td>
                    <td class="sD3">23</td>
                    <td class="sD4">convert(varchar, {ts'2018-07-31 16:51:38.253'}, 121)</td>
                </tr>
                <tr style="font-size: 12px; font-family: inherit; border-bottom: 1px solid rgb(233, 233, 233);">
                    <td class="sD1">120</td>
                    <td class="sD2">2018-07-31 16:51:38</td>
                    <td class="sD3">19</td>
                    <td class="sD4">convert(varchar, {ts'2018-07-31 16:51:38.253'}, 120)</td>
                </tr>
                <tr style="font-size: 12px; font-family: inherit; border-bottom: 1px solid rgb(233, 233, 233);">
                    <td class="sD1">110</td>
                    <td class="sD2">07-31-2018</td>
                    <td class="sD3">10</td>
                    <td class="sD4">convert(varchar, {ts'2018-07-31 16:51:38.253'}, 110)</td>
                </tr>
                <tr style="font-size: 12px; font-family: inherit; border-bottom: 1px solid rgb(233, 233, 233);">
                    <td class="sD1">10</td>
                    <td class="sD2">07-31-18</td>
                    <td class="sD3">8</td>
                    <td class="sD4">convert(varchar, {ts'2018-07-31 16:51:38.253'}, 10)</td>
                </tr>
                <tr style="font-size: 12px; font-family: inherit; border-bottom: 1px solid rgb(233, 233, 233);">
                    <td class="sD1">105</td>
                    <td class="sD2">31-07-2018</td>
                    <td class="sD3">10</td>
                    <td class="sD4">convert(varchar, {ts'2018-07-31 16:51:38.253'}, 105)</td>
                </tr>
                <tr style="font-size: 12px; font-family: inherit; border-bottom: 1px solid rgb(233, 233, 233);">
                    <td class="sD1">5</td>
                    <td class="sD2">31-07-18</td>
                    <td class="sD3">8</td>
                    <td class="sD4">convert(varchar, {ts'2018-07-31 16:51:38.253'}, 5)</td>
                </tr>
            </tbody>
        </table>
    </div>
    <h4 class="Hdr4">
        <br style="font-size: 20px; font-family: inherit;" />
        Space delimited</h4>
    <div class="tableScroll" class="sDiv">
        <table class="sTB">
            <thead style="font-size: 12px; font-family: inherit;">
                <tr style="font-size: 12px; font-family: inherit; border-bottom: 1px solid rgb(233, 233, 233);">
                    <th class="sTH1">Style</th>
                    <th class="sTH2">Example</th>
                    <th class="sTH3">Length</th>
                    <th class="sTH4">Syntax</th>
                </tr>
            </thead>
            <tbody style="font-size: 12px; font-family: inherit;">
                <tr style="font-size: 12px; font-family: inherit; border-bottom: 1px solid rgb(233, 233, 233);">
                    <td class="sTD1">9</td>
                    <td class="sTD2">Jul 31 2018 4:51:38:253PM</td>
                    <td class="sTD3">26</td>
                    <td class="sTD4">convert(varchar, {ts'2018-07-31 16:51:38.253'}, 9)</td>
                </tr>
                <tr style="font-size: 12px; font-family: inherit; border-bottom: 1px solid rgb(233, 233, 233);">
                    <td class="sTD1">109</td>
                    <td class="sTD2">Jul 31 2018 4:51:38:253PM</td>
                    <td class="sTD3">26</td>
                    <td class="sTD4">convert(varchar, {ts'2018-07-31 16:51:38.253'},109)</td>
                </tr>
                <tr style="font-size: 12px; font-family: inherit; border-bottom: 1px solid rgb(233, 233, 233);">
                    <td class="sTD1">100</td>
                    <td class="sTD2">Jul 31 2018 4:51PM</td>
                    <td class="sTD3">19</td>
                    <td class="sTD4">convert(varchar, {ts'2018-07-31 16:51:38.253'},100)</td>
                </tr>
                <tr style="font-size: 12px; font-family: inherit; border-bottom: 1px solid rgb(233, 233, 233);">
                    <td class="sTD1">107</td>
                    <td class="sTD2">Jul 31, 2018</td>
                    <td class="sTD3">12</td>
                    <td class="sTD4">convert(varchar, {ts'2018-07-31 16:51:38.253'},107)</td>
                </tr>
                <tr style="font-size: 12px; font-family: inherit; border-bottom: 1px solid rgb(233, 233, 233);">
                    <td class="sTD1">7</td>
                    <td class="sTD2">Jul 31, 18</td>
                    <td class="sTD3">10</td>
                    <td class="sTD4">convert(varchar, {ts'2018-07-31 16:51:38.253'},7)</td>
                </tr>
                <tr style="font-size: 12px; font-family: inherit; border-bottom: 1px solid rgb(233, 233, 233);">
                    <td class="sTD1">13</td>
                    <td class="sTD2">31 Jul 2018 16:51:38:253</td>
                    <td class="sTD3">24</td>
                    <td class="sTD4">convert(varchar, {ts'2018-07-31 16:51:38.253'},13)</td>
                </tr>
                <tr style="font-size: 12px; font-family: inherit; border-bottom: 1px solid rgb(233, 233, 233);">
                    <td class="sTD1">113</td>
                    <td class="sTD2">31 Jul 2018 16:51:38:253</td>
                    <td class="sTD3">24</td>
                    <td class="sTD4">convert(varchar, {ts'2018-07-31 16:51:38.253'},113)</td>
                </tr>
                <tr style="font-size: 12px; font-family: inherit; border-bottom: 1px solid rgb(233, 233, 233);">
                    <td class="sTD1">106</td>
                    <td class="sTD2">31 Jul 2018</td>
                    <td class="sTD3">11</td>
                    <td class="sTD4">convert(varchar, {ts'2018-07-31 16:51:38.253'},106)</td>
                </tr>
                <tr style="font-size: 12px; font-family: inherit; border-bottom: 1px solid rgb(233, 233, 233);">
                    <td class="sTD1">6</td>
                    <td class="sTD2">31 Jul 18</td>
                    <td class="sTD3">9</td>
                    <td class="sTD4">convert(varchar, {ts'2018-07-31 16:51:38.253'},6)</td>
                </tr>
            </tbody>
        </table>
    </div>
    <h4 class="Hdr4">
        <br style="font-size: 20px; font-family: inherit;" />
        Time Only</h4>
    <div class="tableScroll" class="sDiv">
        <table class="sTB">
            <thead style="font-size: 12px; font-family: inherit;">
                <tr style="font-size: 12px; font-family: inherit; border-bottom: 1px solid rgb(233, 233, 233);">
                    <th class="sH1">Style</th>
                    <th class="sH2">Example</th>
                    <th class="sH3">Length</th>
                    <th class="sH4">Syntax</th>
                </tr>
            </thead>
            <tbody style="font-size: 12px; font-family: inherit;">
                <tr style="font-size: 12px; font-family: inherit; border-bottom: 1px solid rgb(233, 233, 233);">
                    <td class="sD1">14</td>
                    <td class="sD2">16:51:38:253</td>
                    <td class="sD3">12</td>
                    <td class="sD4">convert(varchar, {ts'2018-07-31 16:51:38.253'}, 14)</td>
                </tr>
                <tr style="font-size: 12px; font-family: inherit; border-bottom: 1px solid rgb(233, 233, 233);">
                    <td class="sD1">114</td>
                    <td class="sD2">16:51:38:253</td>
                    <td class="sD3">12</td>
                    <td class="sD4">convert(varchar, {ts'2018-07-31 16:51:38.253'}, 114)</td>
                </tr>
                <tr style="font-size: 12px; font-family: inherit; border-bottom: 1px solid rgb(233, 233, 233);">
                    <td class="sD1">8</td>
                    <td class="sD2">16:51:38</td>
                    <td class="sD3">8</td>
                    <td class="sD4">convert(varchar, {ts'2018-07-31 16:51:38.253'}, 8)</td>
                </tr>
                <tr style="font-size: 12px; font-family: inherit; border-bottom: 1px solid rgb(233, 233, 233);">
                    <td class="sD1">108</td>
                    <td class="sD2">16:51:38</td>
                    <td class="sD3">8</td>
                    <td class="sD4">convert(varchar, {ts'2018-07-31 16:51:38.253'}, 108)</td>
                </tr>
            </tbody>
        </table>
    </div>
    <br class="Apple-interchange-newline" />
</body>
</html>

赞(0）回复(0）举报 2022-12-10

我来回答

SQL Server T-SQL：将字符串日期转换为DateTime

4条答案

相关问题

热门标签

最新问答