help with 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

SQL Server 2000 Forums

SQL Server Development (2000)

help with date column

Author

Topic

ssunny
Posting Yak Master

133 Posts

Posted - 2009-01-16 : 11:42:06

Hi All,

I have a table with createdate column and datatype is smalldattime.

createdate
--------------
2009-01-08 19:43:00
2009-01-14 11:03:00
2008-12-31 17:09:00

Now for a report I need to display createdate as

12/31/2008
1/8/2009
1/14/2009.

Now if I do select convert (varchar(25),'2009-01-08 19:43:00',110)
it give me 2009-01-08 19:43:00
similarly for select convert (varchar(25),'2009-01-08 19:43:00',101)
I am getting 2009-01-08 19:43:00
but I want 1/8/2009.

Thanks in advance.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-16 : 12:07:25

whats the reporting tool you're using?
if its sql reporting services, you just need to use report language as English (U.S) and then you can right click on cell displaying date and select format tab and chose datatype as date with above format (which is available as one of options)

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2009-01-16 : 12:08:32

select convert (varchar(25),cast('2009-01-08 19:43:00' as datetime),101)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-16 : 12:20:09

quote:
Originally posted by rohitkumar

select convert (varchar(25),cast('2009-01-08 19:43:00' as datetime),101)

its not advisable to change datatype of field to varchar just for formatting purpose. Formatting is a presentation issue which should be handled at you front end as long as you can. Its not purpose of Sql Server to do formatting of output.

ssunny
Posting Yak Master

133 Posts

Posted - 2009-01-16 : 12:26:17

Visakh,
I am not using any tool for reporting. I am just using DTS package and generate report in .xls file.

Rohit,

your query worked for me but I am not getting result in the correct order.

Here's my query:

select count(customerid) as Total, convert (varchar(25),cast(createdate as datetime),101) as date
from dbo.customer (nolock)
where createdate > = '10/15/08' and createdate < = getdate()
group by convert (varchar(25),cast(createdate as datetime),101)
order by convert (varchar(25),cast(createdate as datetime),101) desc

resultI got:

3 01/15/2009
9 11/03/2008
1 11/04/2008
16 11/05/2008
1 11/19/2008
1 11/26/2008
1 12/12/2008

result I want:

3 01/15/2009
1 12/12/2008
1 11/26/2008
1 11/19/2008
16 11/05/2008
1 11/04/2008
9 11/03/2008

Thanks Guys.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-16 : 12:31:34

now i hope you understood problem of changing datatype

. leaving it as datetime would have sorted it correctly. You can very well change format in excel by right clicking on column and selecting red format (there are various format available)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-16 : 12:33:11

[code]
select count(customerid) as Total, dateadd(dd,datediff(dd,0,createdate),0) as [date]
from dbo.customer (nolock)
where createdate > = '10/15/08' and createdate < = getdate()
group by dateadd(dd,datediff(dd,0,createdate),0)
order by [date]
[/code]

ssunny
Posting Yak Master

133 Posts

Posted - 2009-01-16 : 12:45:31

quote:

Originally posted by visakh16


select count(customerid) as Total, dateadd(dd,datediff(dd,0,createdate),0) as [date] 
from dbo.customer (nolock)
where createdate > = '10/15/08' and createdate < = getdate()
group by dateadd(dd,datediff(dd,0,createdate),0)
order by [date]

Hey Visakh,

This query works fine. And as you suggested I changed the format of date column in excel sheet and now report looks perfect.
Today I learned something new and very important thing. Thanks so much.
Thanks to Rohit also.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-16 : 12:55:11

You're welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources