Post reply

ISDATE (date in UK format DB in US format)

  • December 7, 2011 at 2:45 am

    #391056

    I am using the function ISDATE against column of type varchar which is a date in UK date format (dd/mm/yyyy) but the Database ‘s collation has the date format (mm/dd/yyyy). This means I have a lot of dates showing as non-dates.

    How can I get ISDATE to work with the UK format on a US database without changing the collation?

    Many Thanks

  • December 7, 2011 at 2:52 am

    #1417882

    first of all, can you detail why you are storing dates as varchar and not datetime and what is the business requirements for doing this

    secondly you could set dateformat = dmy and then convert the varchar to a db date, then do isdate

    eg

    set dateformat dmy

    select isdate(convert(date,'01/01/2011'))

  • December 7, 2011 at 2:59 am

    #1417885

    Edward-445599 (12/7/2011)

    I am using the function ISDATE against column of type varchar which is a date in UK date format (dd/mm/yyyy) but the Database ‘s collation has the date format (mm/dd/yyyy). This means I have a lot of dates showing as non-dates.

    How can I get ISDATE to work with the UK format on a US database without changing the collation?

    Many Thanks

    You can use DATEFORMAT to set the date format of your choice for the current execution.

    May be this can help:

    SET DATEFORMAT 'dmy'

    GO

    DECLARE @UKFormatDateTime DATETIME

    SET @UKFormatDateTime = '21/12/2011'

    PRINT @UKFormatDateTime

    Sujeet Singh

  • December 7, 2011 at 3:12 am

    #1417896

    thanks all

  • December 7, 2011 at 3:16 am

    #1417902

    happy to help.

    as i said in my original post, what is the business requirement for having dates as varchars? if there isnt any, I would recommend putting a task together to store them as datetime and use an ISO standard, that way you dont have regional issues.

  • December 7, 2011 at 3:28 am

    #1417908

    I am in the process of doing that but I want to make sure all the values are valid dates first

  • December 7, 2011 at 3:35 am

    #1417914

    excellent, thats good to hear

Viewing 7 posts - 1 through 7 (of 7 total)

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

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy