Problem Maintaining yyyy.mo.dd.hh.mi.ss

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)

Problem Maintaining yyyy.mo.dd.hh.mi.ss

Author

Topic

AlexP
Starting Member

34 Posts

Posted - 2005-07-15 : 14:40:15

I need to get this string in my return set.

time=2003.09.28.02.58.46|

In the DB I am selecting it like this:

SELECT
convert(smalldatetime, O.dtCreatedDate) as time,..blah

How can I append the time= infront of the convert without getting an error?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-07-15 : 14:58:30

you would typically do the formatting in your presentation software/reportwriter and just return the datetime value from the database. But here is one way:


set nocount on
declare @tb table (dtCreatedDate datetime)
insert @tb select getdate()

select 'time=' + replace(replace(replace(convert(varchar, o.dtCreatedDate, 120),'-','.'),':','.'),' ','.')+'|'
from	@tb o

Be One with the Optimizer
TG

AlexP
Starting Member

34 Posts

Posted - 2005-07-15 : 15:01:49

Thank you that works! Appreciate it!

AlexP
Starting Member

34 Posts

Posted - 2005-07-15 : 15:04:00

Just as an aside, what in the heck is the difference from what I tried in the beginning?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-07-15 : 15:08:20

Well just to guess, (since you didn't post the error or the complete code), I would say that you were converting the datetime to a smalldatetime, then trying to concatenate characters to a smallatetime value. sql won't allow that. My code converts your date to varchar before performing the concatenation.

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources