B****a
发帖数: 154 | 1 Need to convert a varchar column to datetime format.
The column is like:
monthid
200811
200711
200610
200510
I need them to be like
2008/11/15
2007/11/15
2006/10/15
2005/10/15
i.e., convert it to the 15th date of the month...how can I do this in T-SQL?
thanks a lot!!! |
j*****n
发帖数: 1781 | 2 SELECT convert(datetime, monthid + '15')
FROM table |
B****a
发帖数: 154 | 3 this doesnot work...
e.g., it returns 2449-07-26 for 200710
【在 j*****n 的大作中提到】
: SELECT convert(datetime, monthid + '15')
: FROM table
|
j*****n
发帖数: 1781 | 4 en, then try this:
SELECT convert(datetime, convert(int, monthid + '15'))
FROM table
【在 B****a 的大作中提到】
: this doesnot work...
: e.g., it returns 2449-07-26 for 200710
|
B****a
发帖数: 154 | 5 thanks for your reply...but it is still the same...
【在 j*****n 的大作中提到】
: en, then try this:
: SELECT convert(datetime, convert(int, monthid + '15'))
: FROM table
|
j*****n
发帖数: 1781 | 6 shot, then go this way...
SELECT convert(datetime, left(monthid,4) + '/' + right(monthid,2) + '/15')
FROM table |
B****a
发帖数: 154 | 7 OMG, it works this time!! :)
thanks a lot for your time and patience! Thanks!!!!
【在 j*****n 的大作中提到】
: shot, then go this way...
: SELECT convert(datetime, left(monthid,4) + '/' + right(monthid,2) + '/15')
: FROM table
|
n********6
发帖数: 1511 | 8 substring()
updates: Use convert.
Sorry. Should use convert (to datetime format). Jackrun is right. Substring
will return varchar type.
【在 B****a 的大作中提到】
: Need to convert a varchar column to datetime format.
: The column is like:
: monthid
: 200811
: 200711
: 200610
: 200510
: I need them to be like
: 2008/11/15
: 2007/11/15
|
