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
hi,good day…
thanks for ure codings above….but iam having problem to select the month and display the details…eg…..i have a table with the field which would have the date,month and year (fldMonth), if let say i select the year 2007 in another drop down list, i want the table to display the details starting from june 2007 till july 2008…..
i really have no idea…plz help me out…urgent…!!!currently my coding just display the details for that particular year onli from january to december…..
thanks lots
Get idea from this and apply it
declare @year int
set @year=2007
select dateadd(month,6-1,dateadd(year,@year-1900,0)),dateadd(month,6-1+13,dateadd(year,@year-1900,0))
Worked like a charm and saved a few brain ticks from figuring it out myself.
I used it for a financial report so I went a step further and did this for previous year dates. I also need to find previous year WTD so I used this:
DATEADD(wk, DATEDIFF(wk, 6, dateadd(yy,-1,@dToday)), 6)
How to get first and last working day of month? I mean exact DAY of week when we start working by excluding SATURDAY and SUNDAY if it comes on 1st or 2nd. So Query should return 3rd.
/*To find first day of previous/next month*/
declare @testdate datetime
select @testdate=getdate()
–Find first day of pevious month
SELECT DATEADD(dd,1-day((DATEADD(mm,-1,@testdate))),DATEADD(mm,-1,@testdate))
–Find first day of next month
SELECT DATEADD(dd,1-day((DATEADD(mm,1,@testdate))),DATEADD(mm,1,@testdate))
i have to store in the sql server database like 2008AUG how to do that
Why do you want to store formatted dates?
@nir
use master
create database “2008AUG”
— give your options
use double quotation around the name of the database and you can create a database name starting with a letter.
Thanks,
Imran.
hi I want to get the last day of the month between the two given dates. also I want to get the last day of the week between the two dates.
Hi I’m trying to retrieve data from a database which uses SQL, how do I specify which dates I require values for in the DD/MM format
Thanks for the article, gave me clue for my requirements, I have jotted down calculating various days like this day last year, first day of prior years and so on, hope this would be useful.
–This week start date Monday —
select DATEADD(wk, DATEDIFF(wk,0,getdate()),0)
–This week enddate–
–select dateadd(d,-1,getdate())
select cast(floor(cast(dateadd(d,-1,getdate()) as float)) as datetime)
–Last week start date–
select DATEADD(wk, DATEDIFF(wk,0,getdate()),-7)
–Last week end date–
select DATEADD(wk, DATEDIFF(wk,0,getdate()),-1)
–First day of this year–
select DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)
–Last day of the prior year–
select cast(floor(cast(dateadd(mi,-1,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)) as float)) as datetime)
–First day of next year–
select DATEADD(yy, DATEDIFF(yy,-1,getdate()), 0)
–First day of prior year–
SELECT DATEADD(yy, DATEDIFF(yy,0,dateadd(mi,-1,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0))), 0)
–This week last Year start date Monday —
select cast(floor(cast(dateadd(wk,datediff(wk,0,dateadd(yy,-1,getdate()) ),0)as float)) as datetime)
–This day last year
select dateadd(yy,-1,DATEADD(wk, DATEDIFF(wk,0,getdate()),0))
–First day of the current month–
select DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)
–Last day of the current month–
select cast(floor(cast(dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate() )+1, 0))as float)) as datetime)
–First day of the current month Last year–
select dateadd(yy,-1,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0))
–Last day of the current month Last Year–
select cast(floor(cast(dateadd(yy,-1,dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate() )+1, 0)))as float)) as datetime)
Hi,
Thnaks for your query, Is it possible that instead of sending date can I send only month. Could you please send me that query if possible.
Thanks in advance.
Regards
Suman
What about year part?
Do you want to send it too?
This article helped me in one query.But i’m trying another query,can you help me in ths?
I have a Sales table data.I have user submitting every month sales data.My company has financial year from OCt 1 st to Sept 30 the next year.Whenever, i run the query, i have to get totalSalesAmount,for Salesdate which ranges between Oct 1st to till date.If i run that query now i have to get sales amount(Oct 2008+Nov 2008+Dec 2008) but when i run it in March 2009, it should should fetch (Oct 2008+Noc 2008+Dec2008+Jan 2009+Feb2009),
How do i do that?
Help appreciated
Cool stuff.. great job keep rocking…
can anybody help me to find out second,third,fourth,fifth tuesday,wed,thu,fri,sat,sun of a month?
thanx
Hi,
For each month we have many weeks, some month will have 4 weeks but some will have 6 weeks ( for example for jan 09 , 01 -03 jan will be considered as Week1). How to find out the week number as per the above logic.
If i give a date the query should return it belongs wich week of the month.
i tried with datepart and week , but it returns week number for the year not for the month
Thanks
Hi i want the query to find the maximum date in a particular table.
Thanks,
Karthi..
select max(date_col) as max_date from your_table
Hi,
Can you help me to get a query in SQL server that gives me the date of last saturday of every month
Thanks,
Sanil
Hi,
I want to claculate age say for eg.
Tab1
——-
contribution_date Dob AgeAtContribution
15-12-2005 01-07-1977 ?
Want to calculate age at the time of contribution_date with the help of dob column above.
Thanks,
aak
Hi,
Here you can use computed column to get Age At contribution Date.
Example:
CREATE TABLE t1(
contribution_date DATETIME,
Dob DATETIME
)
INSERT INTO t1 (contribution_date, DOB) VALUES(NULL, ‘1977-07-01’
)
ALTER TABLE t1
ADD AgeAtContribution AS DATEDIFF(yy, DOB, contribution_date)
SELECT * from t1
You can also find my blog to add computed column at:
Thanks,
Tejas Shah
How do you get last day of month prior year for February where prior year was leap year?
Using the example above:
declare @period smalldatetime
set @period = ‘2/28/2009’
select cast(floor(cast(dateadd(yy,-1,dateadd(ms,-3,dateadd(mm,datediff(m,0,@period)+1,0)))as float)) as datetime)
Result: 2/28/2008
Need result to be 2/29/2008
This is what you need
select dateadd(month,2,dateadd(year,year(getdate())-1900-1,0))-1
@Verdel
Make the date March 1st, then subtract a day: select dateadd(yyyy, -1, dateadd(m, +1, dateadd(dd, – (datepart(dd, @period) – 1), @period)))
Suppose Getdate() output is 01/11/2009.
How to get previous month last day.
Out put should be 12/31/2008.
Let me know.
Thanks.
@nirav
Here you get last date of previous month
SELECT DateAdd(day, -1, DateAdd(month, DateDiff(month, 0, GETDATE()), 0))