Following query will run respective to today’s date. It will return Last Day of Previous Month, First Day of Current Month, Today, Last Day of Previous Month and First Day of Next Month respective to current month. Let us see how we can do this with the help of Date Function in SQL Server.
DECLARE @mydate DATETIME SELECT @mydate = GETDATE() SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@mydate)),@mydate),101), 'Last Day of Previous Month' UNION SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@mydate)-1),@mydate),101) AS Date_Value, 'First Day of Current Month' AS Date_Type UNION SELECT CONVERT(VARCHAR(25),@mydate,101) AS Date_Value, 'Today' AS Date_Type UNION SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@mydate))), DATEADD(mm,1,@mydate)),101), 'Last Day of Current Month' UNION SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@mydate))-1), DATEADD(mm,1,@mydate)),101), 'First Day of Next Month' GO
Here are few additional blog posts on this subject:
- Adding Datetime and Time Values Using Variables
- Find Current System Date, Time and Time Offset
- Get Time in Hour, Minute Format from a Datetime – Get Date Part Only from Datetime
I have seen scores of expert developers getting perplexed with SQL Server in finding time only from datetime datatype. Let us have a quick glance look at the solution. Let us learn about how to get Time in Hour:Minute Format from a Datetime as well as to get Date Part Only from Datetime.
Reference: Pinal Dave (https://blog.sqlauthority.com)
188 Comments. Leave new
Lazy solution: for fast day of the month
select ‘1/’+
CONVERT(varchar(25),(select MONTH(getdate())))
+’/’+
CONVERT(varchar(25),(select YEAR(getdate()) ))
*First
Thats a VARCHAR and not DATE. Use this approach
select dateadd(day,datediff(day,0,getdate()),0)
Dear Mohamed, this is shah. I provide the solution of getting Days in the month. and u provide something else
BUDDHI Srilanka
Some one need to get previous year Current month start and end date
DECLARE @mydate DATETIME
DECLARE @FdayOfCurrentMonth DATETIME
DECLARE @LdayOfCurrentMonth DATETIME
DECLARE @FdayOfCurrentMonthLY DATETIME
DECLARE @LdayOfCurrentMonthLY DATETIME
BEGIN
SELECT @mydate = GETDATE()
SELECT @FdayOfCurrentMonth = CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@mydate)-1),@mydate),101)
SELECT @LdayOfCurrentMonth = CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@mydate))),DATEADD(mm,1,@mydate)),101)
SELECT @FdayOfCurrentMonthLY = CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@mydate))-1),DATEADD(mm,-12,@mydate)),101)
SELECT @LdayOfCurrentMonthLY = CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@mydate))),DATEADD(mm,-11,@mydate)),101)
Dont convert them to VARCHAR. Use the examples shown here. Go to the bottom of the page and refer the examples
Thanks a lot, helped me quite a bit. :)
Hi I am in the way of automating my SQL query. I have a filed called Transaction_Date in my table.
I my analysis i will pre period analysis, promo period analysis and post period analysis. FOr ex, if the campaign starts on 1-June-2012 and the end date is on 30-Jun-12, then i should take the same number days as promo period for pre and post anlaysis.
I am giving the dates manually in hte SQL code. But i want to automate it by considering the dates automatically.
drop table #response_curve
select Status,
week_no,
[Customers] = count (distinct account_no),
[Transactions] = sum(case when tx_type_code in (’01’,’90’) then 1 when tx_type_code in (’03’,’94’) then -1 else 0 end),
[Spend] = sum(cast(total_txn_value*issue_mult as bigint)),
[Standard_Points_Issued]= sum(cast(std_points_value*issue_mult as bigint)),
[Bonus_Points_Issued] = sum(cast(bonus_points_value*issue_mult as bigint))
into #response_curve
from
(select case when t.transaction_date >= ‘2012-07-17’ and t.transaction_date = ‘2012-07-22’ and t.transaction_date = ‘2012-07-29’ and t.transaction_date = ‘2012-08-05’ and t.transaction_date = ‘2012-08-12’ and t.transaction_date = ‘2012-08-19’ and t.transaction_date = ‘2012-08-26’ then ‘Week7’end as week_no,
t.account_no,tt.tx_type_code ,t.total_txn_value,tt.issue_mult,t.std_points_value,t.bonus_points_value,t.transaction_date,f.Status
from tx_txn t with
inner join #final_campaign_members f on f.account_no = t.account_no
inner join tx_type tt on t.tx_type_code = tt.tx_type_code
where t.terminal_id like ‘RSA%’ and t.transaction_date >= ‘2012-07-17’ and t.transaction_date < '2012-09-01' ) a
group by Status,week_no
Above is the code which i am using. please help me on howto automate the above code by not giving the dates manually
Hi I need a help with the following criteria in SQL query.
I have an end date of say ex. 4/15/2012 and I want to set this date to end of the month ie, 4/30/2012.
Similarly, I have a beginning date as 4/16/2012 and I want to set it first of next month ie 5/1/2012. Can someone help me on this plz.
Hi all,
I need a querry that can give me endate from start date and number of months
Like I have a start date is 10/1/2012 and number of months is 10, how can i get 7/31/2013
Here’s the correct version for the blogger’s queries that takes into account the times 00:00:00 and 23:59:00
SELECT DATEADD(month, DATEDIFF(month, 0, GETDATE())-1, 0), ‘Start Last Month’
SELECT DATEADD(minute, -1, DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)), ‘End Start Last Month’
SELECT DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0), ‘Start This Month’
SELECT DATEADD(minute, -1, DATEADD(month, DATEDIFF(month, 0, GETDATE())+1, 0)), ‘End This Month’
How to get the month having maximum birth day in a particular year in table having field DOB with datetime in sql server?
Saved my time :):)
i need to fetch all the details fom database by filtering name and from(startdate) to (enddate) if a name is kannan i need all his details such as projectname,task details within that filtered date for timesheet eg he done various task and list all the task he made
Do not use string concatenation. Use DATADD/DATEDIFF functions. Run the below query
SELECT
DATEADD(QQ, DATEDIFF(QQ, 0, GETDATE()), 0) ThisQuarterFirstDay,
DATEADD(QQ, 0, GETDATE()) ThisQuarterLastDay,
DATEADD(MM, DATEDIFF(MM, 0, GETDATE()) – 1, 0) LastMonthFirstDay,
DATEADD(MM, DATEDIFF(MM, 0, GETDATE()), -1) LastMonthLastDay,
DATEADD(MM, DATEDIFF(MM, 0, GETDATE()), 0) ThisMonthFirstDay,
DATEADD(MM, DATEDIFF(MM, 0, GETDATE()) + 1, -1) ThisMonthLastDay
Can also do it like this:
Will get the first of the month 2 months ago, and first of the months 4 months in the future
SELECT DATEADD(month, -2, GETDATE() – (DAY(GETDATE()) -1)), DATEADD(month, 4, GETDATE() – (DAY(GETDATE()) -1))
Hi
Am not very will with SQL but I just looking for. script can get first date and last date for a month from
Thank you!!!
thank you pina dave…..
you are inspire to me………..you are question and Answer …thank you
how to get first Monday in next year, by using Ms sql server 2008.Below following code will return the result as a specific(using static value +7) but i want generalized(dynamically change)query
select
(dateadd(day,(365-DATEPART(DY,GETDATE())+7),GETDATE())) as First_Monday_Next_Year
Please help me………..[email removed]
How do I get first day of last month and first day of last month – 1 day.
how to get data for current month and next 3 months
Pinal, everytime I have a SQL question, I google around and your awesome answers keep coming up. Thank you many many times!