Smalldatetime conversion - 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

Transact-SQL (2005)

Smalldatetime conversion

Author

Topic

Nesu
Starting Member

15 Posts

Posted - 2007-05-25 : 07:41:41

I'm selecting a number of attributes in particular some dates in a row of which I have to find the largest of the three dates. I have done this using a sub query to select the date I want, however the date comes in a smalldatetime format, eg 2006-01-01 00:00:00.000 I wish this date to returned in the format 01/01/2006 How do I go about this?

Thanks a lot.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-25 : 07:45:20

Option :
1. do the formatting in your front end
2. use convert(varchar(10), datecol, 103)

Nesu
Starting Member

15 Posts

Posted - 2007-05-25 : 07:51:26

Obviously I didn't included the code. Sorry!

select p.empb, p.empnum, p.leftdate, p.paydate, (select max(datevalue) from (select leftdate as datevalue from emp.dbo.emp314 b
where p.empb = b.empnum
and p.empb = b.empnum
union all
select paydate from emp.dbo.emp314 c
where p.empb = c.empb
and p.empnum = c.empnum) as sub1) as requireddate,
from emp.dbo.emp314 as p
where (p.leftdate between '01/01/1897' and '01/01/2007')
and (p.paydate between '01/01/1897' and '01/01/2007')

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-25 : 08:34:14

Sorry, don't quite understand what you want here. Care to explain ?

Nesu
Starting Member

15 Posts

Posted - 2007-05-25 : 08:45:39

Sorry. I'll try my best.
What the query does is to select the greatest date of leftdate and paydate and returns that as required date.
When i run the query, the field requireddate is returned as a samlldatetime field eg 2006-01-01 00:00:00.000
I want this field to be returned in the format 01/01/2006 and that it will keep this format if exported using the report function to a program such as SAS.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-25 : 08:53:16

you can use convert to do that


convert(varchar(10), ( <the big sub-query here> ), 103) as requireddate

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-25 : 09:00:17

also try this. Re-write using INNER JOIN.


select	p.empb, p.empnum, p.leftdate, p.paydate, 
        convert(varchar(10), datevalue, 103) as requireddate,
from 	emp.dbo.emp314 as p
	inner join
	(
		select	empb, empnum, max(datevalue) as datevalue
		from
		(
			select	empb, empnum, max(leftdate) as datevalue
			from	emp.dbo.emp314
			group by empb, empnum
			
			union all
	
			select	empb, empnum, max(paydate) as datevalue
			from	emp.dbo.emp314
			group by empb, empnum
		) n
                group by empb, empnum
	) m
	on	p.empb		= m.empb
	and	p.empnum	= m.empnum
where 	p.leftdate between '01/01/1897' and '01/01/2007'
and 	p.paydate  between '01/01/1897' and '01/01/2007'

Nesu
Starting Member

15 Posts

Posted - 2007-05-25 : 11:16:14

Thanks for your help.

I used your first solution:

quote:
Originally posted by khtan

you can use convert to do that

convert(varchar(10), ( <the big sub-query here> ), 103) as requireddate

KH

and it worked great.

Thanks again.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-05-26 : 01:38:09

Also, if you use front end application, use format function there

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources