CODEWITHSUNDEEP
sql-server-2008t-sqldatetime-format
marky
marky

Reputation: 5068

T-SQL Updated date field not the same as date format in select statement

In a SQL Server 2008 database I have a Fiscal Year table where the end_date for each fiscal period is set up wrong. The period start_date is the first day of a calendar month at midnight in the format smalldatetime (2015-01-01 00:00:00). The period end_date is supposed to be the last second of the last day of the start_date's month (2015-01-31 23:59:59). The data type for both fields is smalldatetime.

The following gives me the desired date and time that I would like to put in the end_date field:

SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,start_date)+1,0))

But it adds on milliseconds to the end of the time: 2015-01-31 23:59:59.000

When I try converting or casting that as smalldatetime to omit the milliseconds it sets the time to midnight of the last day of the start_date's month: 2015-01-31 00:00:00. This also happens if I just update the end_date field with the code in the select statement above.

How can I update the end_date with the correct format and value (2015-01-31 23:59:59)?

Upvotes: 0

Views: 193

Answers (2)

marky
marky

Reputation: 5068

I didn't realize that smalldatetime doesn't store seconds. KekuSemau's answer wasn't quite the right answer, but it did make me realize I wasn't trying to do the right thing, given the datatype of the column I had to update (No, I couldn't change the datatype of the column).

All I had to do was adjust the query to subtract a minute versus a second, which the smalldatetime field will work with:

cast(DATEADD(MINUTE,-1,DATEADD(mm, DATEDIFF(m,0,start_date)+1,0)) as smalldatetime)

So I ended up updating the end_date column with 2015-01-31 23:50:00, as required.

Upvotes: -1

KekuSemau
KekuSemau

Reputation: 6853

smalldatetime does not store seconds, it's always 00. That's why there's an automatic conversion when you subtract one second.
If you want to store seconds, you have to convert the columns to a different data type.

Upvotes: 4

Related Questions