Convert Date "24-Jan-04" to "1/24/2004"

[CGehlhausen]

I've researched the Date Convert function, so I know that the output that I need is 101 for "1/24/2004".

But what do I use to pick an input in the form of "24-Jan-04"? This is SQL 7.0, so that may be the problem, but it seems like it should be possible.

Here's what I found:

Without With
century century
(yy) (yyyy) Standard Input/Output**

- 0 or 100 (*) Default mon dd yyyy hh:miAM (or PM)
1 101 USA mm/dd/yy
2 102 ANSI yy.mm.dd
3 103 British/French dd/mm/yy
4 104 German dd.mm.yy
5 105 Italian dd-mm-yy
6 106 - dd mon yy
7 107 - mon dd, yy
8 108 - hh:mm:ss
- 9 or 109 (*) Default + msecs mon dd yyyy hh:mi:ss:mmmAM (or PM)
10 110 USA mm-dd-yy
11 111 JAPAN yy/mm/dd
12 112 ISO yymmdd
- 13 or 113 (*) Europe default + msecs dd mon yyyy hh:mm:ss:mmm(24h)
14 114 - hh:mi:ss:mmm(24h)
- 20 or 120 (*) ODBC canonical yyyy-mm-dd hh:mi:ss(24h)
- 21 or 121 (*) ODBC canonical (with msecs) yyyy-mm-dd hh:mi:ss.mmm(24h)

* The default values (style 0 or 100, 9 or 109, 13 or 113, 20 or 120, and 21 or 121) always return the century (yyyy).

** Input when converting to datetime; Output when converting to character data.



WHAT DO I USE?

 

[dan1967]

Try doing the following. Create a local variable of the datetime type and populate with your value, then you will be able to convert to American date format.

declare @dt as datetime
select @dt = '24-Jan-04'
select convert(char(20), @dt, 101)

Dan.

 

[dbomrrsm]

without the variable:

select convert(char (20),cast('24-Jan-04' as datetime),101)

[blue]DBomrrsm[/blue]

 

[CGehlhausen]

Thanks, That does the trick!