Display date&time incorrect - 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)

Display date&time incorrect

Author

Topic

lovehui
Yak Posting Veteran

60 Posts

Posted - 2009-04-01 : 12:09:55

Hello,
I have a stored procedure which likes this.

create table #Tmp(
		EMPLOYEE				varchar(50), 
        	TRAN_DATE				datetime,
		TRAN_TYPE				varchar(50),
     ......
)

I want to display TRAN_DATE as 'mm/dd/yyyy', so

exec('update #Tmp set TRAN_DATE =
(select convert(varchar(10), TRAN_END_DTTM, 101)
FROM TRANSTABLE A WHERE #Tmp.TRANS_NUMBER = A.TRAN_ID and #Tmp.TRANS_NUMBER = A.TRAN_ID
)')

And other sql scipts
exec('select EMPLOYEE,...from Tmp where TRAN_DATE between
'03/29/2009 12:00am' and '04/01/2009 12:00pm')
However in the rdlc report, it always is shown as
'mm/dd/yyyy 12:00 AM'

Can you please tell me why?

Many thanks

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-04-01 : 12:24:54

Use the convert wheh you are doing the select

exec('select EMPLOYEE,convert(varchar,TRAN_DATE,101)...from Tmp where TRAN_DATE between
'03/29/2009 12:00am' and '04/01/2009 12:00pm')

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2009-04-01 : 12:35:17

Formating should be done on the report itself, not in the query.

Format("d",mydatecol)

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-01 : 13:25:14

if its sql report you've standard date formats available in cell properties. so no need to use format() function if what you want is standard date format

lovehui
Yak Posting Veteran

60 Posts

Posted - 2009-04-02 : 16:28:11

I set format code in the cell property but why it fails?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources