• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 289
  • Last Modified:

Date function use to select record not more than date in month

Hi,
what is the date function I can use to select records which no more than date in the month. For example I run a select statement

select * from recordtable where date <30:04:2011 23:59:59:000'

But using a date function I want when I assign the where clause sql will automatically detect end date for each month.
0
motioneye
Asked:
motioneye
4 Solutions
 
Rajkumar GsSoftware EngineerCommented:

select * from recordtable where date < DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0))
0
 
cfEngineersCommented:
select * from recordtable where GetDate() < '30:04:2011 23:59:59:000'
0
 
Rajkumar GsSoftware EngineerCommented:
0
 
Ephraim WangoyaCommented:
use this
declare @LastOfMonth DateTime
set @LastOfMonth = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0))
elect * from recordtable where date <= LastOfMonth

Open in new window

0
 
Alpesh PatelAssistant ConsultantCommented:
select * from recordtable where date < dateadd(day,-1,dateadd(month,1,convert(varchar,YEAR(GETDATE())) + '-' + right('0' + convert (varchar,MONTH(GETDATE())),2) + '-01' ))
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now