Post reply

nvarchar to smalldatetime

  • March 7, 2019 at 4:03 am

    #2023770

    Having done more work on this the most effective way to update the records is the way in which rodders recommend;

    This updated all formats of dates such as
    12 May 2006
    05/06/2002
    1/24/2011
    06/06/2006  11:39:20 (mm/dd/yyyy)

    The only format that hasn't changed format is the format
    29/01/2001 08:51:56  (dd/mm/yyyy)

    Any ideas from the script what could be changed, altered or added to fix this issue.

    Kind  Reagrds!!

  • March 7, 2019 at 4:30 am

    #2023777

    UCDA2019 - Thursday, March 7, 2019 4:03 AM

    Having done more work on this the most effective way to update the records is the way in which rodders recommend;

    This updated all formats of dates such as
    12 May 2006
    05/06/2002
    1/24/2011
    06/06/2006  11:39:20 (mm/dd/yyyy)

    The only format that hasn't changed format is the format
    29/01/2001 08:51:56  (dd/mm/yyyy)

    Any ideas from the script what could be changed, altered or added to fix this issue.

    Kind  Reagrds!!

    Both Rods method and my method have the potential to incorrectly convert the 11 October vs 10 November dates.  So you may want to validate those carefully.
    That said, I believe that the following SQL should happily convert your remaining format
    CONVERT(SMALLDATETIME, CreateDt, 103)

    How to post data/code on a forum to get the best help.

  • March 7, 2019 at 4:33 am

    #2023778

    For more information on the CONVERT function, take a look at the "Date and Time Styles" section of the Microsoft Docs

    How to post data/code on a forum to get the best help.

  • March 7, 2019 at 4:36 am

    #2023779

    DesNorton - Thursday, March 7, 2019 4:30 AM

    UCDA2019 - Thursday, March 7, 2019 4:03 AM

    Having done more work on this the most effective way to update the records is the way in which rodders recommend;

    This updated all formats of dates such as
    12 May 2006
    05/06/2002
    1/24/2011
    06/06/2006  11:39:20 (mm/dd/yyyy)

    The only format that hasn't changed format is the format
    29/01/2001 08:51:56  (dd/mm/yyyy)

    Any ideas from the script what could be changed, altered or added to fix this issue.

    Kind  Reagrds!!

    Both Rods method and my method have the potential to incorrectly convert the 11 October vs 10 November dates.  So you may want to validate those carefully.
    That said, I believe that the following SQL should happily convert your remaining format
    CONVERT(SMALLDATETIME, CreateDt, 103)

    Des, 
    This has sorted the the rest of the dates!
    Greatly Appreciated!

  • March 7, 2019 at 4:39 am

    #2023781

    JW,

    Good to hear you are winning.
    I ended up with no time to take a look last night.

    Rodders...

  • March 7, 2019 at 4:48 am

    #2023782

    UCDA2019 - Thursday, March 7, 2019 4:03 AM

    Having done more work on this the most effective way to update the records is the way in which rodders recommend;

    This updated all formats of dates such as
    12 May 2006
    05/06/2002
    1/24/2011
    06/06/2006  11:39:20 (mm/dd/yyyy)

    The only format that hasn't changed format is the format
    29/01/2001 08:51:56  (dd/mm/yyyy)

    Any ideas from the script what could be changed, altered or added to fix this issue.

    Kind  Reagrds!!

    Last attempt - I give up after this.
    You've identified 06/06/2006  11:39:20 as (mm/dd/yyyy)
    You've identified 29/01/2001 08:51:56 as  (dd/mm/yyyy)
    What format would 05/06/2006  11:39:20 be? mm/dd/yyyy or dd/mm/yyyy?
    How can you tell?

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • March 7, 2019 at 5:12 am

    #2023784

    rodjkidd - Thursday, March 7, 2019 4:39 AM

    JW,

    Good to hear you are winning.
    I ended up with no time to take a look last night.

    Rodders...

    Cheers Rodders,
    your help has been create i have just one more issue to resolve
    with the script you provided me a 'NewDate' column was produced when run how do i transfer this information into the original table as standard procedure doesn't seem to allow for it.

  • March 7, 2019 at 5:16 am

    #2023785

    ChrisM@Work - Thursday, March 7, 2019 4:48 AM

    UCDA2019 - Thursday, March 7, 2019 4:03 AM

    Having done more work on this the most effective way to update the records is the way in which rodders recommend;

    This updated all formats of dates such as
    12 May 2006
    05/06/2002
    1/24/2011
    06/06/2006  11:39:20 (mm/dd/yyyy)

    The only format that hasn't changed format is the format
    29/01/2001 08:51:56  (dd/mm/yyyy)

    Any ideas from the script what could be changed, altered or added to fix this issue.

    Kind  Reagrds!!

    Last attempt - I give up after this.
    You've identified 06/06/2006  11:39:20 as (mm/dd/yyyy)
    You've identified 29/01/2001 08:51:56 as  (dd/mm/yyyy)
    What format would 05/06/2006  11:39:20 be? mm/dd/yyyy or dd/mm/yyyy?
    How can you tell?

    Du to the query run from other dates i can tell whihc format as for example
    29/01/2001 08:51:56 becomes 2001-01-29 08:52:00.000
    09/03/2003 becomes 2003-09-03 00:00:00.000
    so,
     05/06/2006  11:39:20 becomes 2006-05-6 11:39:00.000 yyyy/mm/dd

  • March 7, 2019 at 6:36 am

    #2023795

    Concerning the ambiguous dates, the dd/mm/yyyy format ends to be common in Europe and a few other places. Does your table have some kind of geographical identifier? Or can you relate to tables that do?

    Might give you a fighting chance of disambiguation.

  • March 7, 2019 at 6:55 am

    #2023801

    roger.plowman - Thursday, March 7, 2019 6:36 AM

    Concerning the ambiguous dates, the dd/mm/yyyy format ends to be common in Europe and a few other places. Does your table have some kind of geographical identifier? Or can you relate to tables that do?

    Might give you a fighting chance of disambiguation.

    Yeah the Table contains a 'SalesRegion' Column which uses numbers to identify a particular area
    For example USA is 7
    England is 6

  • March 7, 2019 at 7:02 am

    #2023802

    UCDA2019 - Wednesday, March 6, 2019 5:14 AM

    Researching into the unambiguous dates and asking fellow colleagues are they aware of which dates are which

    It would be better to store the dates/times as the DATETIME datatype and worry about "unambiguous dates" only at display time.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • March 7, 2019 at 7:04 am

    #2023804

    UCDA2019 - Thursday, March 7, 2019 5:12 AM

    rodjkidd - Thursday, March 7, 2019 4:39 AM

    JW,

    Good to hear you are winning.
    I ended up with no time to take a look last night.

    Rodders...

    Cheers Rodders,
    your help has been create i have just one more issue to resolve
    with the script you provided me a 'NewDate' column was produced when run how do i transfer this information into the original table as standard procedure doesn't seem to allow for it.

    You will need to turn the select into an update
    You will need to change the table and column names but something like this will give you a starting point


    UPDATE yourdbname
    SET yourdatecol =
    CASE WHEN ISDATE(originalDate) = 1
      THEN CAST(Createdt AS datetime)
      ELSE
       CASE WHEN SUBSTRING(Createdt, 3, 1) = '/'
       THEN
        CASE WHEN ISDATE(SUBSTRING(Createdt, 4, 2) + '/' + LEFT(Createdt, 2) + '/' + RIGHT (Createdt, 4)) = 1
          THEN CAST(SUBSTRING(Createdt, 4, 2) + '/' + LEFT(Createdt, 2) + '/' + RIGHT (Createdt, 4) AS datetime)
        END
       END
      END

    Are you looking to update the column that is already there, or a new datetime column?
    As we have all already mentioned as these are date / times they really should be in a datetime column. not char.

    Rodders...

  • March 7, 2019 at 7:39 am

    #2023816

    rodjkidd - Thursday, March 7, 2019 7:04 AM

    UCDA2019 - Thursday, March 7, 2019 5:12 AM

    rodjkidd - Thursday, March 7, 2019 4:39 AM

    JW,

    Good to hear you are winning.
    I ended up with no time to take a look last night.

    Rodders...

    Cheers Rodders,
    your help has been create i have just one more issue to resolve
    with the script you provided me a 'NewDate' column was produced when run how do i transfer this information into the original table as standard procedure doesn't seem to allow for it.

    You will need to turn the select into an update
    You will need to change the table and column names but something like this will give you a starting point


    UPDATE yourdbname
    SET yourdatecol =
    CASE WHEN ISDATE(originalDate) = 1
      THEN CAST(Createdt AS datetime)
      ELSE
       CASE WHEN SUBSTRING(Createdt, 3, 1) = '/'
       THEN
        CASE WHEN ISDATE(SUBSTRING(Createdt, 4, 2) + '/' + LEFT(Createdt, 2) + '/' + RIGHT (Createdt, 4)) = 1
          THEN CAST(SUBSTRING(Createdt, 4, 2) + '/' + LEFT(Createdt, 2) + '/' + RIGHT (Createdt, 4) AS datetime)
        END
       END
      END

    Are you looking to update the column that is already there, or a new datetime column?
    As we have all already mentioned as these are date / times they really should be in a datetime column. not char.

    Rodders...

    I was Looking to Update the column that is already there however once i use the code as above the null values that i had previously eg. 29/01/2001 08:51:56 don't update and becomes NULL so i may need to create a new column 

    Once all the data is set in a new column completed i intend to alter the columns data type from nvarchar to datetime.

  • March 7, 2019 at 8:20 am

    #2023839

    UCDA2019 - Thursday, March 7, 2019 7:39 AM

    I was Looking to Update the column that is already there however once i use the code as above the null values that i had previously eg. 29/01/2001 08:51:56 don't update and becomes NULL so i may need to create a new column 

    Once all the data is set in a new column completed i intend to alter the columns data type from nvarchar to datetime.

    I would suggest the following process

    1)  Add a new column of type DATETIME or SMALLDATETIME as needed
    ALTER TABLE dbo.MyTable ADD [NewCreatedt] DATETIME NULL;
    2)  Update the new column with the converted values.
    3)  When you are satisfied with the data, rename the columns
    BEGIN TRANSACTION;

    EXEC sys.sp_rename @objname = N'dbo.MyTable.Createdt'
                     , @newname = N'OldCreatedt'
                     , @objtype = 'COLUMN';

    EXEC sys.sp_rename @objname = N'dbo.MyTable.NewCreatedt'
                     , @newname = N'Createdt'
                     , @objtype = 'COLUMN';

    IF ( @@ERROR = 0 )
        COMMIT TRANSACTION;
    ELSE
        ROLLBACK TRANSACTION;
    GO

    4)  After revalidating the data, you can drop the old column.

    How to post data/code on a forum to get the best help.

  • March 8, 2019 at 4:52 am

    #2023925

    Yeah we seem to have a problem 
    eg.
    11/12/2007
    is appearing
    as 12/11/2007

    any idea in which this could be fixed??

Viewing 15 posts - 16 through 30 (of 37 total)

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