Updating date fields

Topic

New Reply

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!

Reply | Quote

Replies

Date2 = DateSerial(Year(Date1), Month(Date1),1)

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote