Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Jan 2008
    Location
    Naperville, Illinois, United States
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Updating date fields

    I need some help with the logic on how to update a date field based on information in another date field in MS SQL 2008.

    DATE1: YYYY-MM-DD-hh:mm - This field is populated with a data entered by the client with all elements entered
    DATE2: Same format. However, I want to create this datestamp using the YYYY and MM from DATE1 and setting DD to 01, hh:mm to 00:00.

    I need to do this for the whole table, with each record having a unique ID with a (hopefully) simple SQL script.

    Both fields currently exist in the database - I just need to update the DATE2 field based on pieces of the DATE1 field.

    Thanks for any and all help that can be provided!
    Last edited by dascooper; 2015-01-16 at 11:55.

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    Date2 = DateSerial(Year(Date1), Month(Date1),1)
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    New Lounger
    Join Date
    Jan 2008
    Location
    Naperville, Illinois, United States
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Close

    I don't think dateserial is a MS SQL 2008 function. If so, this would have been a great solution. It looks like there have been some new functions added in SQL 2012, but we're not there yet.

  4. #4
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,168
    Thanks
    47
    Thanked 978 Times in 908 Posts
    Personally I'd generate the date when looking at the record, but I suspect you need to use the date as an index / lookup?

    cheers, Paul

  5. #5
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    Quote Originally Posted by dascooper View Post
    I don't think dateserial is a MS SQL 2008 function. If so, this would have been a great solution. It looks like there have been some new functions added in SQL 2012, but we're not there yet.
    Oop! DateSerial is an Access function. There is now a comparable function in SQL 2012 named "DateFromParts" (actually, there are several variations).

    But for SQL 2008 you might try this (it is a little convoluted, but it works):

    CAST(CAST(YEAR(YourDate) AS VARCHAR) + '-' + CAST(MONTH(YourDate) AS VARCHAR) + '-1' AS DateTime) AS FirstOfMonthDate
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •