how to write query? - 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

how to write query?

Author

Topic

samsun125
Yak Posting Veteran

63 Posts

Posted - 2009-06-11 : 10:59:07

hi all,

i HAVE ONE QUERY ,

SELECT Left(convert(varchar(11),COLUMNDate),7) 'PublishedTime' FROM TBL WHERE datediff(day, COLUMNDate,getdate()) < 08
group by
convert(varchar(11),COLUMNDate)
order by
convert(varchar(11),COLUMNDate)
This above query is giving last 7 days data like(june11,june10,june9,
june8,june7,june6,june5)

i want to write query from june5 to last 7 days
like (june5,june4,june3,june2,june1,may31,may30)

can u please tell me how to write query

Thanks & Regards
Rama

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-11 : 11:05:35

[code]SELECT dateadd(dd,datediff(dd,0, COLUMNDate),0) 'PublishedTime' FROM TBL
WHERE COLUMNDate >= DATEADD(wk,DATEDIFF(wk,0,GETDATE())-1,0)
AND COLUMNDate < DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0)-1
group by
dateadd(dd,datediff(dd,0, COLUMNDate),0)
order by
dateadd(dd,datediff(dd,0, COLUMNDate),0)
[/code]

samsun125
Yak Posting Veteran

63 Posts

Posted - 2009-06-12 : 02:03:36

Hi visakh16,

Thank you very much,

i am getting data like this

2009-06-01 00:00:00.000
2009-06-02 00:00:00.000
2009-06-03 00:00:00.000
2009-06-04 00:00:00.000
2009-06-05 00:00:00.000
2009-06-06 00:00:00.000

but i need like this
june1
june2
june3
june4
june5
june6

can u please tell me how to do

Thanks & Regards
Rama

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-12 : 02:05:15

Format the date in your front end application.

KH
[spoiler]Time is always against us[/spoiler]

samsun125
Yak Posting Veteran

63 Posts

Posted - 2009-06-12 : 02:30:13

Hi khtan,

is this possible i want to change date format to varchar in query itself.

if anybody knows means please let me know.

Thanks & Regards
Rama

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-12 : 02:34:16

of-course it is possible. It is just that it is much better to do it in your front end. Formatting it in SQL means you will not be able to sort the date in your front end application if you need to.

for example , august date will come before june.

If you really really must do it in the query, checkout CONVERT() in BOL

KH
[spoiler]Time is always against us[/spoiler]

samsun125
Yak Posting Veteran

63 Posts

Posted - 2009-06-12 : 05:34:06

hi all,

using the below query i will get june1,june2.....june7

what i want to do is i want just count means june1 to june7 means result is 7.
how to change the below query to get count.
query is:

SELECT dateadd(dd,datediff(dd,0, COLUMNDate),0) 'PublishedTime' FROM TBL
WHERE COLUMNDate >= DATEADD(wk,DATEDIFF(wk,0,GETDATE())-1,0)
AND COLUMNDate < DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0)-1
group by
dateadd(dd,datediff(dd,0, COLUMNDate),0)
order by
dateadd(dd,datediff(dd,0, COLUMNDate),0)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-14 : 01:50:51

do you mean this?


SELECT dateadd(mm,datediff(mm,0, COLUMNDate),0),COUNT(*) FROM TBL
WHERE COLUMNDate >= DATEADD(wk,DATEDIFF(wk,0,GETDATE())-1,0)
AND COLUMNDate < DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0)-1
group by 
dateadd(mm,datediff(mm,0, COLUMNDate),0)
order by 
dateadd(mm,datediff(mm,0, COLUMNDate),0)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources