Post reply

Format Date to UK Format

  • August 13, 2008 at 5:23 am

    #191234

    I have a field which contains dates in CHAR(8) format e.g. 11/08/08 = 11th August 2008. However, if I use the following to get the month:

    datepart(m, cast(my_date as datetime))

    I will get 11 instead of 8. Can anyone tell me how to do this properly!!!

    Thanks

  • August 13, 2008 at 5:40 am

    #857309

    First if possible i would try and change the data type of that column form char to datetime so you only have to do this once - storing dates in character fields can cause lots of hassle later.

    For fixing the current data, I guess your language setting is set to US english - which takes dates on input as being mdy rather than dmy. Safest by far is have a look at the style options on convert (rather than cast) so you can define the required date format.

    Mike

  • August 13, 2008 at 6:15 am

    #857334

    Hi Mike,

    Unfortunately, I cannot change the column type because it is a third party database. I tried the following:

    select datepart(m,cast(convert(char(8),'11/08/08',103)as datetime))

    but it still returns 11.

    Thanks

  • August 13, 2008 at 6:28 am

    #857342

    David

    CAST and CONVERT provide similar functionality (i.e. converts an expression of one data type to another) but CONVERT also provides a datetime formatter mask. CAST is unnecessary for your requirements.

    Try:

    SELECT DATEPART(m, CONVERT(DATETIME, '11/08/08', 3))

  • August 13, 2008 at 6:36 am

    #857348

    Hi Reeth,

    Thanks for the tip. The example you provided works perfectly!

    Regards

    David

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply