datetime convert 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

SQL Server 2005 Forums

Transact-SQL (2005)

datetime convert query

Author

Topic

ayu
Starting Member

43 Posts

Posted - 2008-05-01 : 09:28:07

declare @dt varchar(20)
select count(s.sopnumbe) as orders from sop30300 s
left outer join sop30200 ss
on s.sopnumbe = ss.sopnumbe
where s.itemnmbr=ss.sku and s.soptype = 2 and ss.docdate=@dt

well docdate is datetime and dt is varchar
how can i compare - both?

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-05-01 : 09:34:08

Why not declare @dt as datetime? What does data for @dt look like?

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

ayu
Starting Member

43 Posts

Posted - 2008-05-01 : 09:37:37

well, @dt variable is from function i have to compare docdate-datetime and @dt= varchar(20) variable in where condition.

if its same then only i will get count(*) results..

this statement is working in function.

thanks.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-05-01 : 09:45:38

That doesn't answer either question, I don't think. Why does the function return a date as a varchar? What does the varchar look like?

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-05-01 : 11:16:05

SQL Server can compare a datetime datatype to varchar directly, provided that the varchar is in a format that SQL Server can cast to datetime with no problem.

If you must do this, then it is best to put the varchar date in the "universal" datetime format, YYYYMMDD or YYYYMMDD hh:mm:ss.mmm

Examples:


-- Date only
set @dt = '20080530'

-- With time
set @dt = '20080530 23:59:59.993'

CODO ERGO SUM

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-05-01 : 13:14:57

maybe like this:

declare @dt varchar(20)

select 
	count(s.sopnumbe) as orders 
from 
	sop30300 s 
left outer join 
	sop30200 ss
	on s.sopnumbe = ss.sopnumbe 
where 
	s.itemnmbr=ss.sku 
	and s.soptype = 2 
	and ss.docdate= CAST(@dt AS DATETIME)

-- OR

declare @dt DATETIME

select 
	count(s.sopnumbe) as orders 
from 
	sop30300 s 
left outer join 
	sop30200 ss
	on s.sopnumbe = ss.sopnumbe 
where 
	s.itemnmbr=ss.sku 
	and s.soptype = 2 
	and ss.docdate= @dt

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-01 : 13:28:31

I dont think ss.docdate=@dt will work correctly when you have a time part too in your datetime field value and you are trying to retrieve detail by passing date alone. in that case you need to strip off time part from your field and then perform comparison.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources