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
Hello,
I tried to create the SQL statement with the condition:
“Today’s date > Month 36 after first VISIT_date” ?
I would think about 36 months * 30 days as:
(SysDate – VISIT_date) > (36*30)
But not every month has 30 days. Can any one help pls?
Thanks,
-Vanessa
@Vanessa
How about: SysDate >= DATEADD(m, 36, VISIT_date)
Though, 36 months is 3 years, so: SysDate >= DATEADD(yy, 3, VISIT_date)
Found your queries helpful
hi,
am sreekanth, i have a table with some columns and date of update.i am writing function which takes month and year as parameters.i want max date of update from table where date of update <=(month and year) how to write a query.plz reply me to the mail if u know the solution
hey
i want to get all the month names using SQL Query plz help me out
thanks
tania
@Tania.
If you have SQL Server 2005 or higher , you can try this,
create table #temp(id int identity, ename varchar(10))
go
insert into #temp values (‘Testing’)
go 12 — This should not be replaced.
go
select datename (mm , dateadd (mm, id – (datepart(mm,getdate())), getdate())) Month_Names
from #temp
go
drop table #temp
go
I am sure, there is a better way of doing this, at-least this should give you a start.
~ IM.
I was going through this forum hoping to get the last day of year of a given date but can’t find how to get. Anyone can help me how to get it. I need this badly.
Thanks.
select dateadd(year,datediff(year,0,@date)+1,-1)
Haamidou,
Try this,
Select Datename ( dw, Dateadd ( dd , -1 , datename (yy, dateadd (year, +1, getdate()))))
Output : Thursday
Replace Getdate() with your date column name.
Please test this before using it in your code.
~ IM.
Thanks a lot Imran. I also got how to get the end date as well i.e. December 31st of any given date.
Hellooo…
M the student of MCA n i hv such a query….
der detail is as follow….
-> PRINT THE INFORMATION OF CUSTOMERS WHO HAVE BEEN BORN IN THE MONTH OF FEBRUARY.
@Devika
Take a look at the DATEPART FUNCTION:
WHERE DATEPART(m, date-col) = 2
Shortly
WHERE month(date_col) = 2
I need to know the concept of Parameterized views?? Is it realted to functions returning table are parameterized views??
How these are different then Stored procedures.??
Please tell me!!
Hi,
I want query that will display the all the sundays with date that are coming in the whole year, or the year that i will pass as a parameter.
Thanks in advance,
regards,
yogesh
Hi,
Yogesh you can find out all sundays for given year by this query.
What I did is:
First I get First Date of given year and Last date of given year. Then I used CTE (common Table expression), to get all dates.
DECLARE @Year AS INT, @FirstDateOfYear DATETIME, @LastDateOfYear DATETIME
SELECT @year = 2009
SELECT @FirstDateOfYear = DATEADD(yyyy, @Year – 1900, 0)
SELECT @LastDateOfYear = DATEADD(yyyy, @Year – 1900 + 1, 0)
;with cte as(
SELECT 1 AS DayID,
@FirstDateOfYear AS FromDate,
DATENAME(dw, @FirstDateOfYear) AS Dayname
UNION ALL
SELECT cte.DayID + 1 AS DayID,
DATEADD(d, 1 ,cte.FromDate),
DATENAME(dw, DATEADD(d, 1 ,cte.FromDate)) AS Dayname
FROM cte
WHERE DATEADD(d,1,cte.FromDate) < @LastDateOfYear
)
SELECT FromDate AS Date FROM CTE
WHERE DayName LIKE 'SunDay'
option (MaxRecursion 370)
Let me know if you have any doubts.
Thanks,
Tejas
SQLYoga.com
Or you can use the below query :
create table #temp (i int identity(1,1),c1 char(1))
insert into #temp (c1) values(‘a’)
go 1000
select dateadd(“dd”,i,GETDATE()) dtDate from #temp where datename(dw,dateadd(“dd”,i,GETDATE())) = ‘Sunday’
Regards,
Pinal Dave
hi,
i want to know
how to write a query that gets no of days in month and year.
to get days in a month(yearwise)
Dear Pinal Dave,
i am new in database site but i got good knowledge from you blog nice efforts helping db developer by ur skill well dear i want help concerning date issue, will you solve “how to get all dates of current month.”
thanks
;WITH CTE AS
(
SELECT DATEADD(dd,-(DAY(GETDATE())-1),GETDATE()) AS FIRSTDATE
UNION ALL
SELECT DATEADD(dd,1,firstdate) from CTE where FIRSTDATE<=CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,GETDATE()))),DATEADD(mm,1,getdate())),101)
)
select CONVERT(varchar(25),FIRSTDATE,101) from CTE
Thanks for the query.
hey..
I’m looking to get the day from a date.
How do i get it?
suppose I give some date format in the sql query, lets say for example “10-Apr-98”, I would like to get the result as which day this date has occurred? Like.. saturday or sunday .. etc??
Can someone help me how to do it?
Thx !!
select datename(weekday,’10-Apr-98′)
hello,
I’m looking to get the for every day of the month only a special time..
for example 01.04.2010-30.04.2010; every day 8-12 clock
Can someone help me how to do it?
thanks
where datepart(hour,date_col)>=8 and datepart(hour,date_col)<13