J.S.Orris
Reputation: 4821
T-SQL: Convert String Date to DateTime
My string dates are as follows in YearMonth format:
201008
201009
201010
201011
201012
...
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
Upvotes: 0
Views: 155
Answers (3)
Jenna Leaf
Reputation: 2452
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>Upvotes: 1
Mike
Reputation: 132
Maybe you will be satisfied something like this:
SELECT CAST(DATEPART(MONTH,CONVERT(DATETIME, @FileName, 104)) AS VARCHAR)+'/'+CAST(DATEPART(YEAR,CONVERT(DATETIME, @FileName, 104)) AS VARCHAR)
Upvotes: 1
Joel Coehoorn
Reputation: 415735
Use 112 instead of 104. From the docs:
112 = yyyymmdd
That seems to match your format pretty well. The only thing you need to do is add the '01':
CONVERT (datetime, @FileName + '01', 112)
Upvotes: 3
Related Questions
- How to convert a datetime to string in T-SQL
- Convert format date string to datetime using T-SQL in SQL Server 2008
- Convert from string to Datetime T-SQL
- SQL Convert String into DATETIME
- Convert String to datetime in SQL Server 2008
- Converting a string to a datetime in SQL Server 2008
- DateTime convert in SQL
- TSQL - converting date string to datetime
- How to convert a string to datetime using T-SQL?
- T-SQL String to DateTime-conversion