locked
Need help with manipulating datetime data types RRS feed

  • Question

  • I have an existing process (that I cannot change) that purges old data from tables that contain a varchar column with a yyyymmdd date. For convienance sake, let's say that table1 now has a datetime date that we want to use to archive old records. I need some help comparing and manipulating datetime data to strings. The bullet points of the current process where I need help are below (no, I cannot rewrite the process):

    1) Current statement for varchar yyyymmdd field is below. #temp1 (datefield) is a varchar field that needs to store the dates in yyyymmdd format. I need to change the statement to select distinct dates (not include time) from a datetime field and store them into #temp1.datefield in yyyymmdd format.

    Insert into #temp1 (datefield) select distinct datefield from table1


    2) Current statement for varchar yyyymmdd field is below. I need to change it to compare a datetime table1.datefield to a varchar yyyymm value (no time and no day).
     
    select * into archive_table1 from table1 where datefield like 'yyyymm%' -- yyyymm has a value


    3) Current statement for varchar yyyymmdd field is below. I need to change it to delete records based on a datetime field compared to a varchar yyyymm value.
     
    delete table1 where datefield like 'yyyymm%'


    Thanks.

    Tuesday, July 8, 2014 3:47 PM

Answers

  • 1) insert into #temp1 (dateField) select distinct convert(varchar(8), DateField, 112) from Table1

    2) select * into archive_table1 from table1 where datefield >= convert(datetime, 'yyyymm01', 112) and datefield < dateadd(month, 1,convert(datetime, 'yyyymm01', 112))

    --- get all dates for the month

    3) delete table1 where datefield >== convert(datetime, 'yyyymm01', 112) and datefield < dateadd(month, 1,convert(datetime, 'yyyymm01', 112))


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Tuesday, July 8, 2014 3:59 PM

All replies

  • 1) insert into #temp1 (dateField) select distinct convert(varchar(8), DateField, 112) from Table1

    2) select * into archive_table1 from table1 where datefield >= convert(datetime, 'yyyymm01', 112) and datefield < dateadd(month, 1,convert(datetime, 'yyyymm01', 112))

    --- get all dates for the month

    3) delete table1 where datefield >== convert(datetime, 'yyyymm01', 112) and datefield < dateadd(month, 1,convert(datetime, 'yyyymm01', 112))


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Tuesday, July 8, 2014 3:59 PM
  • Tuesday, July 8, 2014 4:18 PM
  • Sounds like a good idea.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Tuesday, July 8, 2014 4:21 PM
  • I would love to open a group but, first of all Ed have to agree, since only he can make it an official group for the WIKI (and add the link in the WIKI), and second You as a manager in the WIKI (as well any other manager) and of course Ed have to be part of the managers in that group.
    * I actually never opened a group at Facebook yet, and it might be time that I will test this option :-)
    * Maybe it is better that one of the WIKI's managers will open the group?!?
    * In any case I think it need to be done

    I will write it on the WIKI forum, and i will wait for Ed's response.


    [Personal Site] [Blog] [Facebook]signature

    Tuesday, July 8, 2014 4:53 PM
  • Worked like a charm. Thanks.

    Wednesday, July 9, 2014 4:00 PM