Date Format (mm/dd/yyyy hh:mm), no seconds or milliseconds

Question

Post reply

Date Format (mm/dd/yyyy hh:mm), no seconds or milliseconds

jpalmer-755805

SSC Eights!

Points: 959
More actions
February 9, 2009 at 1:32 pm

#130795

Does anyone know syntax to use to format a date field with as follows (02/09/2009 14:34)?

Any help would be appreciated.

Thanks,

Jeff

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply

JestersGrind SSCertifiable Points: 5439 More actions · Answer 1

JestersGrind

SSCertifiable

Points: 5439

February 9, 2009 at 2:03 pm

#940810

Try this:

SELECT CONVERT(VARCHAR, GetDate(), 101) + ' ' +



		  CONVERT(VARCHAR, DATEPART(hh, GetDate())) + ':' +

		  RIGHT('0' + CONVERT(VARCHAR, DATEPART(mi, GetDate())), 2) AS Date

Greg

jpalmer-755805 SSC Eights! Points: 959 More actions · Answer 2

jpalmer-755805

SSC Eights!

Points: 959

February 9, 2009 at 2:06 pm

#940812

That worked.

Thank you very much.

Johan Bijnens SSC Guru Points: 134873 More actions · Answer 3

Although Greg's solution works, you should still pose the question if you need to do this in a query!

Presentation should be handled at the front end application !

(one of the advantages is that you'll be able to use the client settings to format the data like the consumer wants it to be)

That goes for datetime, numeric data ( decimal point as comma or not),..

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

jpalmer-755805 SSC Eights! Points: 959 More actions · Answer 4

jpalmer-755805

SSC Eights!

Points: 959

February 9, 2009 at 2:17 pm

#940820

This is for a data extract to a 3rd party software. So Greg's solution fits our need perfectly.

Thanks,

Jeffrey Williams SSC Guru Points: 89773 More actions · Answer 5

Jeffrey Williams

SSC Guru

Points: 89773

February 9, 2009 at 9:58 pm

#940954

Here is another method that is a bit simpler:

select convert(char(10), getdate(), 101)

+ ' ' + convert(char(5), getdate(), 108);

Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 6

In that case, this might be just a little faster because it doesn't have as many CONVERT's...



SELECT CONVERT(CHAR(11),GETDATE(),101)

+ CONVERT(CHAR( 5),GETDATE(),114)

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

jpalmer-755805 SSC Eights! Points: 959 More actions · Answer 7

jpalmer-755805

SSC Eights!

Points: 959

February 10, 2009 at 7:36 am

#941203

Thanks for everyone's reply's.

Have a great day..

Phani-345480 SSC Veteran Points: 228 More actions · Answer 8

Phani-345480

SSC Veteran

Points: 228

December 16, 2009 at 3:01 am

#1092883

For the format : -- MM/DD/YYYY HH:MMAM/PM

CONVERT(VARCHAR(10), GETDATE(), 101) + ' ' + RIGHT(CONVERT(VARCHAR, GETDATE(), 100), 7)

imhemal29 SSC Enthusiast Points: 144 More actions · Answer 9

imhemal29

SSC Enthusiast

Points: 144

January 12, 2010 at 3:51 am

#1102278

Select Cast(convert(varchar(16),Getdate(),120) as DateTime) as [Date]

And

Select convert(varchar(16),Getdate(),120) as [Date]

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 10

imhemal29 (1/12/2010)

Select Cast(convert(varchar(16),Getdate(),120) as DateTime) as [Date]

And

Select convert(varchar(16),Getdate(),120) as [Date]

Check again... op asked for mm/dd/yyyy hh:mm. Above code returns yyyy-mm-dd hh:mi 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Paul White SSC Guru Points: 150457 More actions · Answer 11

Jeff Moden (1/12/2010)

Check again... op asked for mm/dd/yyyy hh:mm. Above code returns yyyy-mm-dd hh:mi 😉

Assuming you don't mind using the Microsoft version of the Kuwaiti algorithm of the Islamic (Hijri) calendar *and* you're all sensible and use dd/mm/yyyy instead of the bizarre mm/dd/yyyy format...:-D...this works:

SELECT CONVERT(CHAR(16), GETDATE(), 131)

:laugh:

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 12

Paul White (1/12/2010)

Jeff Moden (1/12/2010)

Check again... op asked for mm/dd/yyyy hh:mm. Above code returns yyyy-mm-dd hh:mi 😉

Assuming you don't mind using the Microsoft version of the Kuwaiti algorithm of the Islamic (Hijri) calendar *and* you're all sensible and use dd/mm/yyyy instead of the bizarre mm/dd/yyyy format...:-D...this works:

SELECT CONVERT(CHAR(16), GETDATE(), 131)

:laugh:

Now THAT's funny! 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Mohammad Mazharuddin Ehsan SSCarpal Tunnel Points: 4553 More actions · Answer 13

Jeff Moden (1/12/2010)

imhemal29 (1/12/2010)

Select Cast(convert(varchar(16),Getdate(),120) as DateTime) as [Date]

And

Select convert(varchar(16),Getdate(),120) as [Date]

Check again... op asked for mm/dd/yyyy hh:mm. Above code returns yyyy-mm-dd hh:mi 😉

Could not resist to post that imhemal29 solution might not be satisfying the OP query but it luckily provided me the answer I was searching for in solving this chalenge

TSQL Challenge 37 - Calculate the downtime and duration of servers based on the monitoring log

Thanks imhemal29

-----------------------------------------------------------[font=Arial Black]Time Is Money[/font][font=Arial Narrow]Calculating the Number of Business Hours Passed since a Point of Time[/url][/font][font=Arial Narrow]Calculating the Number of Business Hours Passed Between Two Points of Time[/font]

lee 90891 Newbie Points: 9 More actions · Answer 14

lee 90891

Newbie

Points: 9

December 19, 2018 at 2:05 am

#2016797

For the layout...
2018-02-14 18:28
use...
LEFT(CONVERT(VARCHAR, SalesOrders.CreatedOn, 121),16)