Date issues in sql - 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

SQL Server 2005 Forums

Analysis Server and Reporting Services (2005)

Date issues in sql

Author

Topic

cipriani1984
Constraint Violating Yak Guru

304 Posts

Posted - 2009-01-23 : 05:52:53

Hi,

Ive got bunch of records with different dates; 01/01/2009, 04/03/2009 and so one basically few records per month.

I want to create a parameter so user can select by month. How could I go about converting or writing statement so Jan is 01/2009, Feb is 02/2009. I tried a case if statement but sql doesnt convert date time with like.

Any ideas?

Thanks

revelator
Starting Member

32 Posts

Posted - 2009-01-23 : 06:02:15

Do you want this just for the parameter display? Something like this would do that...

select distinct cast(datepart(m,DateStamp) as varchar) + '/' + cast(datepart(yyyy,DateStamp) as varchar)
from YourTable

It's possibly not the solution you are after though? Do you want the SQL that the parameter will be passed to?

-----------------------------------
Waiting for the Great Leap Forwards

cipriani1984
Constraint Violating Yak Guru

304 Posts

Posted - 2009-01-23 : 06:05:26

Ill try what you suggested in a min, basically I want it to select al thats in Jan, so when selecting Jan it will show all those (e.g. 01/01/2009, 15/01/2009, 04/01/2009 etc).

Obv I will need to convert, or add a another field to each record so the parameter has something to link to on the original dataset.

quote:
Originally posted by revelator

Do you want this just for the parameter display? Something like this would do that...

select distinct cast(datepart(m,DateStamp) as varchar) + '/' + cast(datepart(yyyy,DateStamp) as varchar)
from YourTable

It's possibly not the solution you are after though? Do you want the SQL that the parameter will be passed to?

-----------------------------------
Waiting for the Great Leap Forwards

revelator
Starting Member

32 Posts

Posted - 2009-01-23 : 06:09:22

Something along these lines then?

select *
from YourTable
where datepart(m,DateStamp) = '01' and datepart(yyyy,DateStamp) = '2009'

-----------------------------------
Waiting for the Great Leap Forwards

cipriani1984
Constraint Violating Yak Guru

304 Posts

Posted - 2009-01-23 : 06:56:30

Amazing! Thanks for the prompt replies

quote:
Originally posted by revelator

Something along these lines then?

select *
from YourTable
where datepart(m,DateStamp) = '01' and datepart(yyyy,DateStamp) = '2009'

-----------------------------------
Waiting for the Great Leap Forwards

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-23 : 09:56:10

if you want to use index on date column, try this


select *
from YourTable
where DateStamp>=dateadd(mm,@month-1,dateadd(yy,@year-1900,0))
and DateStamp<dateadd(mm,@month,dateadd(yy,@year-1900,0))

@month and @year are values passed

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources