Preventing SQL Server from converting date

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

Transact-SQL (2000)

Preventing SQL Server from converting date

Author

Topic

drewsalem
Constraint Violating Yak Guru

304 Posts

Posted - 2007-07-25 : 10:29:05

Hi,

How do I prevent sql server from changing the format of a selected date when used with a string function i.e.

If the column dateColumn contains 2007-07-01 10:00:00.000 then

Select replace (datecolumn, '-','')
from table

returns:

Jul 1 2007 10:00AM

I want it to return 20070701 10:00:00.000 as it is in the column.

Thanks

Drew

Drew
---------------------
"It's Saturday night; I've got no date, a two litre bottle of Shasta, and my all-Rush mix tape... LET'S ROCK."

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-07-25 : 10:49:35

How about this

DECLARE @date smalldatetime
set @date = '2007-07-01 10:00:00.000'
SELECT CONVERT(varchar(10),@Date,112)+' ' +CONVERT(varchar(20),@DATE,114)

But this is varchar, not smalldatetime

Jim

drewsalem
Constraint Violating Yak Guru

304 Posts

Posted - 2007-07-25 : 11:01:29

That's great. Thanks.

Drew
---------------------
"It's Saturday night; I've got no date, a two litre bottle of Shasta, and my all-Rush mix tape... LET'S ROCK."

nr
SQLTeam MVY

12543 Posts

Posted - 2007-07-25 : 12:24:58

select convert(varchar(8),getdate(),112) + ' ' + convert(varchar(12),getdate(),114)

oops - guess I had that waiting for a response and went away.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources