CODEWITHSUNDEEP
sqlsql-serverdatetime
Leith Hughes
Leith Hughes

Reputation: 1

SQL datetime with in a month

I am new to writing sql queries and am having issue with a date time column tblVehicleDepreciationLine.LineDate which is defined in SQL a Smalldatetime All i need is tblVehicleDepreciationLine.LineDate between start of month and end of month.. I read 20 different datetime solutions but can't seem crack what code i require to get my result..

Any help would be much appreciated.

Select
  tblVehicle.Rego,
  tblVehicle.CompliancePlate,
  tblVehicleType.Description,
  tblVehicleEquipmentStatus.Description As Description1,
  tblBranch.Name,
  tblVehicleDepreciationLine.WDV,
  tblVehicle.ID,
  tblVehicleDepreciationLine.LineDate
From
  tblVehicle 
  Inner Join tblVehicleType On tblVehicle.VehicleType_ID = tblVehicleType.ID 
  Inner Join tblVehicleEquipmentStatus On tblVehicle.VehicleEquipmentStatus_ID = tblVehicleEquipmentStatus.ID 
  Inner Join tblBranch On tblVehicle.ControllingBranch_ID = tblBranch.ID 
  Inner Join tblVehicleDepreciationLine On tblVehicleDepreciationLine.Vehicle_ID = tblVehicle.ID
Where
  (tblVehicleDepreciationLine.LineDate >=
       ('(year(getdate()), month(getdate()), 1)') And
         tblVehicle.VehicleEquipmentStatus_ID = 1)
Or
  (tblVehicleDepreciationLine.LineDate <
       ('(year(getdate()), month(getdate())+1, 1)') And
         tblVehicle.VehicleEquipmentStatus_ID = 2)
Order By
  tblVehicle.ControllingBranch_ID

Upvotes: 0

Views: 75

Answers (3)

Koti Panga
Koti Panga

Reputation: 3720

--Below dates will give some idea for you
SELECT
 'Start of Current Month'   = DATEADD(m, DATEDIFF(m, 0, GETDATE()), 0),
 'End of Current Month'     = DATEADD(ms,-10,DATEADD(m, DATEDIFF(m,0,GETDATE())+1,0)),
 'Start of Next Month'      = DATEADD(m, DATEDIFF(m, 0, GETDATE())+1, 0)


--Your WHERE clause would looks like below
WHERE
    (
        tblVehicleDepreciationLine.LineDate >= DATEADD(m, DATEDIFF(m, 0, GETDATE()), 0) 
        AND tblVehicle.VehicleEquipmentStatus_ID = 1
    )
    OR
    (
        tblVehicleDepreciationLine.LineDate < DATEADD(m, DATEDIFF(m, 0, GETDATE())+1, 0)
        AND tblVehicle.VehicleEquipmentStatus_ID = 2
    )

Upvotes: 0

No&#233;mie Lord
No&#233;mie Lord

Reputation: 781

If you want to check if a date is in the same year and month, you can use something like WHERE tblVehicleDepreciationLine.LineDate LIKE ('2012-10%') (you might have to modifiy this to adapt to the way your server converts dates to strings depending on your server's local)

Upvotes: 0

jpw
jpw

Reputation: 44871

Assuming SQL Server. If you want to check if a date if between the first and last in a month, what you're really checking is whether it is in the same year and month: 

(
year(tblVehicleDepreciationLine.LineDate) = year(getdate()) 
and 
month(getdate()) = month(tblVehicleDepreciationLine.LineDate
)

or in SQL Server 2012 if you want to use a between:

(
tblVehicleDepreciationLine.LineDate between 
datefromparts(year(getdate()), month(getdate()), 1) and eomonth(getdate())
)

This might not be the most efficient way to do this, but it should give you an idea.

Upvotes: 2

Related Questions