query on a date column - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

query on a date column

Author

Topic

venkath
Posting Yak Master

202 Posts

Posted - 2008-11-26 : 06:14:16

Hi all

I have a table with loggedtime column. I would like to get all the records for a month. say for the month August,2008

the column has data as 2008-08-01 13:39:51.000

How can I get it using select query?

Thanks.

raky
Aged Yak Warrior

767 Posts

Posted - 2008-11-26 : 06:32:57

Declare @i int,
select @i = 5

select *
FROM tbl
where month(loggedtime) = @i and year(loggedtime) = year(getdate())

This will return the records with Passed Month value of Current Year.

venkath
Posting Yak Master

202 Posts

Posted - 2008-11-26 : 06:42:26

can't we write with out using any veriables and functions?

Thanks

raky
Aged Yak Warrior

767 Posts

Posted - 2008-11-26 : 07:05:06

replace @i with Month value and no need to declare the @i variable

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-26 : 07:12:59

quote:
Originally posted by venkath

Hi all

I have a table with loggedtime column. I would like to get all the records for a month. say for the month August,2008

the column has data as 2008-08-01 13:39:51.000

How can I get it using select query?

Thanks.

select * from table where loggedtime >='20080801' and loggedtime<'20080901'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-26 : 07:13:48

quote:
Originally posted by raky

Declare @i int,
select @i = 5

select *
FROM tbl
where month(loggedtime) = @i and year(loggedtime) = year(getdate())

This will return the records with Passed Month value of Current Year.

this will cause index on loggedtime to be ignored by query engine, if any present

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-26 : 07:28:06

[code]-- Mimic user supplied parameter
DECLARE @anyDate DATETIME

SET @anyDate = GETDATE()

-- Initialize query
DECLARE @fromDate DATETIME,
@toDate DATETIME

SELECT @fromDate = DATEADD(MONTH, DATEDIFF(MONTH, '19000101', @anyDate), '19000101'),
@toDate = DATEADD(MONTH, DATEDIFF(MONTH, '18991231', @anyDate), '19000101')

SELECT *
FROM Table1
WHERE loggedTime >= @fromDate
AND loggedTime < @toDate[/code]

E 12°55'05.63"
N 56°04'39.26"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-11-26 : 07:29:04

quote:
Originally posted by raky

Declare @i int,
select @i = 5

select *
FROM tbl
where month(loggedtime) = @i and year(loggedtime) = year(getdate())

This will return the records with Passed Month value of Current Year.

Declare @i int
select @i = 8

select *
FROM tbl
where loggedtime>=dateadd(month,@i-1,dateadd(year,year(getdate())-1900,0))
and loggedtime>=dateadd(month,@i,dateadd(year,year(getdate())-1900,0))

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-11-26 : 07:30:47

Madhivanan

Failing to plan is Planning to fail

raky
Aged Yak Warrior

767 Posts

Posted - 2008-11-26 : 09:01:30

quote:
Originally posted by madhivanan

quote:
Originally posted by raky

Declare @i int,
select @i = 5

select *
FROM tbl
where month(loggedtime) = @i and year(loggedtime) = year(getdate())

This will return the records with Passed Month value of Current Year.

Declare @i int
select @i = 8

select *
FROM tbl
where loggedtime>=dateadd(month,@i-1,dateadd(year,year(getdate())-1900,0))
and loggedtime>=dateadd(month,@i,dateadd(year,year(getdate())-1900,0))

Madhivanan

Failing to plan is Planning to fail

small change

Declare @i int
select @i = 8

select *
FROM tbl
where loggedtime>=dateadd(month,@i-1,dateadd(year,year(getdate())-1900,0))
and loggedtime<dateadd(month,@i,dateadd(year,year(getdate())-1900,0))

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-26 : 09:04:52

Why would you have records for next month? Now you have included all records dated on the 1st of following month dated 12:00 AM.
I think Madhi wanted "less than" ( < ) only. Maybe a copy and paste error?

Declare @i int
select @i = 8

select *
FROM tbl
where loggedtime>=dateadd(month,@i-1,dateadd(year,year(getdate())-1900,0))
and loggedtime < dateadd(month,@i,dateadd(year,year(getdate())-1900,0))

E 12°55'05.63"
N 56°04'39.26"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-11-27 : 02:20:53

Yes it is copy and paste error. It should be <

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources