Get the first day of the month from a datetime column

Question

I am trying to convert this Oracle statement to SQL Server:

trunc(e.evt_created) - (to_number(to_char(e.evt_created, 'DD')) - 1)As "Month Raised"

This gets the first day of the month, so if e.evt_created holds 2009-05-11 10:19:27.0 then I need 2009-05-01.

What is the SQL Server equivalent to get that result?

Can you give an example of input and output? That seems to be a long-winded way of doing it in Oracle - seems to emulate `trunc(e.evt_created, 'MM')`, but maybe missing something? By "to SQL" presumably you mean from Oracle's flavour of SQL to SQL Server's flavour of SQL... — Alex Poole, Mar 01 '16 at 11:45
Can you explain what are you trying to achieve here? So we can suggest a solution. — Jorge Campos, Mar 01 '16 at 11:55
Hi Alex and Jorge, Thanks for your response. e.evt_created (2009-05-11 10:19:27.0) is date time in database and all I need is beginning of the month of example date dd/mm/YYYY ie 1/05/2009. Yes I need this to be converted from Oracle Flavour to TSQL flavor — chazpe, Mar 01 '16 at 11:57
Possible duplicate of [How to get first and last day of previous month (with timestamp) in SQL Server](http://stackoverflow.com/questions/11743810/how-to-get-first-and-last-day-of-previous-month-with-timestamp-in-sql-server) — MT0, Mar 01 '16 at 12:05

score 1 · Answer 1 · answered Mar 01 '16 at 11:56

1

You seem to want the first day of the month without a time component. To do this in SQL Server:

select dateadd(day,
               1 - day(e.evt_create),
               cast(e.evt_created as date)
              ) as FirstDayOfMonth

answered Mar 01 '16 at 11:56

Gordon Linoff

score 0 · Answer 2 · answered Mar 01 '16 at 13:50

0

If you use SQL Server 2012 you could do this:

declare @d datetime = getdate();
select datefromparts(year(@d),month(@d),1);

answered Mar 01 '16 at 13:50

Slava N.

2 Answers2