<p>I am trying to write a stored procedure which selects columns from a table and adds 2 extra columns to the ResultSet. These 2 extra columns are the result of conversions on a field in the table which is a Datetime field.</p>

<p>The Datetime format field has the following format 'YYYY-MM-DD HH:MM:SS.S'</p>

<p>The 2 additional fields which should be in the following format:</p>

<ol>
<li>DDMMM</li>
<li>HHMMT, where T is 'A' for a.m. and 'P' for p.m.</li>
</ol>

<p>Example: If the data in the field was '2008-10-12 13:19:12.0' then the extracted fields should contain:</p>

<ol>
<li>12OCT</li>
<li>0119P</li>
</ol>

<p>I have tried using CONVERT string formats, but none of the formats match the output I want to get. I am thinking along the lines of extracting the field data via CONVERT and then using REPLACE, but I surely need some help here, as I am no sure.</p>

<p>Could anyone well versed in stored procedures help me out here?
Thanks!</p>

If dt is your datetime column, then
For 1:
```
```
SUBSTRING(CONVERT(varchar, dt, 13), 1, 2)
    + UPPER(SUBSTRING(CONVERT(varchar, dt, 13), 4, 3))
```
```
For 2:
```
```
SUBSTRING(CONVERT(varchar, dt, 100), 13, 2)
    + SUBSTRING(CONVERT(varchar, dt, 100), 16, 3)
```
```