date time formatt - 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

General SQL Server Forums

New to SQL Server Programming

date time formatt

Author

Topic

stumbling
Posting Yak Master

104 Posts

Posted - 2007-04-02 : 18:29:57

Hi All
I have a column in a table that is of date time format
2006-09-05 14:33:32.077
2006-02-24 16:14:09.853
2006-02-24 16:14:09.900
2006-02-24 16:14:09.913
2005-10-12 10:07:50.513
need to break it down to 2 parts for a sql querie the two parts are
yyyymmdd and HHMISS

I need to tweak the following but can not think of something at present brain drain or brain in drain not sure but any help would be appreciated.

select convert(char (10),dateprocessed,112),convert(char (10),dateprocessed,108)
from tablename

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-04-02 : 18:46:55

Is this what you are looking for?

select convert(char (10),dateprocessed,112), REPLACE(convert(char (10),dateprocessed,108), ':', '')
from tablename

stumbling
Posting Yak Master

104 Posts

Posted - 2007-04-02 : 20:46:38

Thank you very very much replace lol now i think of it i used this command last year..

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-04-02 : 21:16:44

There is a DateOnly() and TimeOnly() function here that you can use:

http://weblogs.sqlteam.com/jeffs/archive/2007/01/02/56079.aspx

Using convert() returns VARCHAR data, not datetime data, which will not sort or compare as you might expect, and you cannot use Date functions or client formatting on it. It is best to keep your data in the proper datatypes and do the formatting at your front-end.

- Jeff
http://weblogs.sqlteam.com/JeffS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources