Convert a date between two dates and times to the first day of a month

user_10362727 0
Viewed 175

I would like to convert a date that falls between two dates and times to the first day of the production month. A production month falls between for example 01/07/2018 07:00:00 and 01/08/2018 07:00:00. The time is important.

So a date falling between these 2 DateTimes (>= 01/07/2018 07:00:00 and < 01/08/2018 07:00:00) should be converted to 01/07/2018. The same needs to happen for all the other months.

A few examples of the desired outcome:

Original_DateTime   | Converted_DateTime
15/07/2018 15:42:00 | 01/07/2018
01/08/2018 05:42:00 | 01/07/2018
01/07/2018 03:30:00 | 01/06/2018
01/07/2018 07:36:00 | 01/07/2018
03/08/2018 05:30:00 | 01/08/2018
date datetime tsql sql-server sql
4 Answers

You can use a query like below

select 
  OriginalDate,
  ConvertedDate= dateadd(hh,7,dateadd(m,datediff(m,0,dateadd(hh,-7,OriginalDate)),0))
from yourTable

See working demo

user_11232260

Calculate the first day of the month, then consider the number of seconds past that point, if it is less than 7 hours, deduct 1 month.

select
     original, ca2.converted
from mytable
cross apply (
    select dateadd(day,-(datepart(day,original)-1) ,cast(original as date))  first_day
    ) ca1
cross apply (
    select case when datediff(second,first_day,original) < (7*60*60) then dateadd(month,-1, first_day) else first_day end converted
    ) ca2
user_20677530

There is also very simple solution:

select format(dateadd(hour,-7,OrginalDateTime),'yyyy-MM-01') from OrginalTable;

Try demo

user_79911230

How does this work for you? You could remove the cast to DATE to include the time:

    CREATE TABLE dates
(
  dt DATETIME
  );

INSERT INTO dates VALUES ('2018-07-01 06:59:59.000'),
('2018-07-01 07:59:59.000'),
('2018-07-30 07:59:59.000'),
('2018-07-02 06:59:59.000');

SELECT 
IIF(
  DATEPART(DAY, dt) = 1 AND 
  CAST(dt AS TIME) < '07:00:00.000', CAST(DATEADD(MONTH, -1, dt) AS DATE),
  DATEADD(DAY, -(DATEPART(DAY, dt)) + 1,CAST(dt AS DATE))) [CalculatedDate]
FROM dates
user_48236040
Related