Can I do this an easier way? - 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

Can I do this an easier way?

Author

Topic

btc
Starting Member

2 Posts

Posted - 2007-04-20 : 07:23:23

This statement returns date formatted 'yyyymmdhhnn'. But there has to be an easier way. Can someone help?

DECLARE @dt datetime;
SELECT @dt = GETDATE();
SELECT CONVERT(varchar(40),@dt,112) +
RIGHT('0' + CAST(DATEPART("hh", @dt) AS varchar(2)), 2) +
RIGHT('0' + CAST(DATEPART("mi", @dt) AS varchar(2)), 2) AS isodt;

Thank you.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-20 : 07:57:27

[code]select left(convert(varchar(8), getdate(), 112) + replace(convert(varchar(10), getdate(), 108), ':', ''), 12) as isodt[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-04-20 : 08:41:00

Please read this: http://weblogs.sqlteam.com/jeffs/archive/2007/04/13/60175.aspx

Do not try to "format" data in SQL Server -- let your front end client or presentation layer do all formatting. Just return the raw datetime using SQL. SQL Server is not a presentation tool, its job is just to return data which is ultimately formatted and presented by whatever application is consuming that data.

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

btc
Starting Member

2 Posts

Posted - 2007-04-20 : 11:02:47

For several clients I need the server date time. Because I have no idea what their date time formatting is, I wanted an ISO style date so I know for sure that I have a yyymmddhhnn format in stead of an unknow y-m-d or m-d-y etc. MySQL does the trick like this:

SELECT DATE_FORMAT(CURRENT_TIMESTAMP, "%Y%m%d%h%i") AS DT

For SQL Server I was unable to find anything other than above, and at this time a shorted variant given bij Harsh Athalye.

Michiel

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-04-20 : 12:17:21

You don't need to format the date or time in T-SQL! that's my point -- you simply return the raw *value* from SQL Server and then formatting is not a concern! Then any client application (report, web page, windows app, etc) can format that dateTime any way it wants depending on any settings it needs -- and these client apps are DESIGNED to do this, not SQL Server which is a database. If you try to format a dateTime in T-SQL, you are converting it to a STRING!

Do this make sense ????

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources