Coming from Oracle - How Do I Do This Date Conversion in SQLServer?

Question

Coming from Oracle - How Do I Do This Date Conversion in SQLServer?

theedwardb

Grasshopper

Points: 24
More actions
October 5, 2018 at 6:23 am

#363761

Hi everyone,
My workplace is moving to SQL Server after several years on Oracle.
I had one query that converted and Oracle date to display just the hour and AM or PM - so the result would be something like "07 AM" or "07 PM".
I was using this:
(to_char(PROD.TableName.DATETIME_STAMP,'HH AM'))
How do I accomplish the same thing in SQL Server?
I'm close. So far I have
(CONVERT(varchar(15),CAST(PROD.BEDS_BEDBOARD.DATETIME_STAMP AS TIME),100))
...but it's returning "7:49PM" and because SQL Server uses numbers (eg 100) I don't know how to change the format to what I'm looking for.
Thanks!

Viewing 12 posts - 1 through 11 (of 11 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

theedwardb - Friday, October 5, 2018 6:23 AM
Hi everyone,
My workplace is moving to SQL Server after several years on Oracle.
I had one query that converted and Oracle date to display just the hour and AM or PM - so the result would be something like "07 AM" or "07 PM".
I was using this:
(to_char(PROD.TableName.DATETIME_STAMP,'HH AM'))
How do I accomplish the same thing in SQL Server?
I'm close. So far I have
(CONVERT(varchar(15),CAST(PROD.BEDS_BEDBOARD.DATETIME_STAMP AS TIME),100))
...but it's returning "7:49PM" and because SQL Server uses numbers (eg 100) I don't know how to change the format to what I'm looking for.
Thanks!

Perhaps this:
declare @TestTime datetime = '2018-10-05 07:05:28.000';

select stuff(convert(varchar(15),cast(@TestTime as time),100),patindex('%:[0-5][0-9]%', convert(varchar(15),cast(@TestTime as time),100)),3,' ');

Thom A SSC Guru Points: 99126 More actions · Answer 2

SQL Server has a FORMAT function, so if you were doing this with a scalar value you could use:
SELECT FORMAT(GETDATE(),'hh tt');
I don't, however, recommend applying that function to a dataset, the performance is awful. Personally, my method might be something like:
RIGHT('0' + DATENAME(HOUR,YourColumn),2) + ' ' + RIGHT(CONVERT(varchar(20),YourColumn,100),2)
DATENAME might seem odd, but DATENAME (as opposed to DATEPART) returns a varchar, which just avoids the additional convert.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Thom A - Friday, October 5, 2018 7:51 AM
SQL Server has a FORMAT function, so if you were doing this with a scalar value you could use:
SELECT FORMAT(GETDATE(),'hh tt');
I don't, however, recommend applying that function to a dataset, the performance is awful. Personally, my method might be something like:
RIGHT('0' + DATENAME(HOUR,YourColumn),2) + ' ' + RIGHT(CONVERT(varchar(20),YourColumn,100),2)
DATENAME might seem odd, but DATENAME (as opposed to DATEPART) returns a varchar, which just avoids the additional convert.

Just remember that the format function is very slow.

Thom A SSC Guru Points: 99126 More actions · Answer 4

Lynn Pettis - Friday, October 5, 2018 7:55 AM
Thom A - Friday, October 5, 2018 7:51 AM
SQL Server has a FORMAT function, so if you were doing this with a scalar value you could use:
SELECT FORMAT(GETDATE(),'hh tt');
I don't, however, recommend applying that function to a dataset, the performance is awful. Personally, my method might be something like:
RIGHT('0' + DATENAME(HOUR,YourColumn),2) + ' ' + RIGHT(CONVERT(varchar(20),YourColumn,100),2)
DATENAME might seem odd, but DATENAME (as opposed to DATEPART) returns a varchar, which just avoids the additional convert.
Just remember that the format function is very slow.

Gave that caveat in my post Lynn (and hence the alternative solution). 🙂

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

Thom A - Friday, October 5, 2018 7:56 AM
Lynn Pettis - Friday, October 5, 2018 7:55 AM
Thom A - Friday, October 5, 2018 7:51 AM
SQL Server has a FORMAT function, so if you were doing this with a scalar value you could use:
SELECT FORMAT(GETDATE(),'hh tt');
I don't, however, recommend applying that function to a dataset, the performance is awful. Personally, my method might be something like:
RIGHT('0' + DATENAME(HOUR,YourColumn),2) + ' ' + RIGHT(CONVERT(varchar(20),YourColumn,100),2)
DATENAME might seem odd, but DATENAME (as opposed to DATEPART) returns a varchar, which just avoids the additional convert.
Just remember that the format function is very slow.
Gave that caveat in my post Lynn (and hence the alternative solution). 🙂

Sorry. Missed it.

Thom A SSC Guru Points: 99126 More actions · Answer 6

Lynn Pettis - Friday, October 5, 2018 8:03 AM
Sorry. Missed it.

No worries. To be honest, it's something that probably needs to be reiterated. The function itself is a really good idea, however, the performance it has just makes it completely unusable 99.999% of the time. Maybe they'll have fixed it in SQL Server 2019 and no one has noticed yet.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

drew.allen SSC Guru Points: 76944 More actions · Answer 7

Thom A - Friday, October 5, 2018 7:51 AM
SQL Server has a FORMAT function, so if you were doing this with a scalar value you could use:
SELECT FORMAT(GETDATE(),'hh tt');
I don't, however, recommend applying that function to a dataset, the performance is awful. Personally, my method might be something like:
RIGHT('0' + DATENAME(HOUR,YourColumn),2) + ' ' + RIGHT(CONVERT(varchar(20),YourColumn,100),2)
DATENAME might seem odd, but DATENAME (as opposed to DATEPART) returns a varchar, which just avoids the additional convert.

I would just skip to the alternate solution, then as an aside mention that there is a FORMAT function, but that they shouldn't use it, because it performs horribly. People tend to focus on the first thing they see, so you don't want to present the awful solution first.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Thom A SSC Guru Points: 99126 More actions · Answer 8

drew.allen - Tuesday, October 23, 2018 9:51 AM
Thom A - Friday, October 5, 2018 7:51 AM
SQL Server has a FORMAT function, so if you were doing this with a scalar value you could use:
SELECT FORMAT(GETDATE(),'hh tt');
I don't, however, recommend applying that function to a dataset, the performance is awful. Personally, my method might be something like:
RIGHT('0' + DATENAME(HOUR,YourColumn),2) + ' ' + RIGHT(CONVERT(varchar(20),YourColumn,100),2)
DATENAME might seem odd, but DATENAME (as opposed to DATEPART) returns a varchar, which just avoids the additional convert.
I would just skip to the alternate solution, then as an aside mention that there is a FORMAT function, but that they shouldn't use it, because it performs horribly. People tend to focus on the first thing they see, so you don't want to present the awful solution first.
Drew

It was more to demonstrate that there is an similar function to TO_CHAR in T-SQL, while letting the OP know it's awful. If people don't know it's awful, then they're more likely to use it; learned that one the hard way... Yes the OP may not be have been aware of FORMAT before my post, but I'm sure they would have found out about it another time, and possibly without the caveat.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Jeff Moden SSC Guru Points: 1003861 More actions · Answer 9

I have to agree with Drew. I won't even post the FORMAT function as a possible solution until they fix the performance issues. I may, however, start off a posted solution with "Whatever you do, don't use the FORMAT function because it's terribly slow".

--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)

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

Thom A - Friday, October 5, 2018 7:51 AM
SQL Server has a FORMAT function, so if you were doing this with a scalar value you could use:
SELECT FORMAT(GETDATE(),'hh tt');
I don't, however, recommend applying that function to a dataset, the performance is awful. Personally, my method might be something like:
RIGHT('0' + DATENAME(HOUR,YourColumn),2) + ' ' + RIGHT(CONVERT(varchar(20),YourColumn,100),2)
DATENAME might seem odd, but DATENAME (as opposed to DATEPART) returns a varchar, which just avoids the additional convert.

I believe you'll find that will give you a surprise when the hour is less than 1 (00 AM) or greater than 12 (19 PM, for example). 😉

--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)

Jeff Moden SSC Guru Points: 1003861 More actions · Answer 11

Lynn's method will not provide leading zeros for times < 10, which you seem to indicate is required in your original post. The following formula will.

STUFF(REPLACE(RIGHT(CONVERT(CHAR(19),SomeDateTimeColumn,100),7),' ',0),3,3,' ')

It's also slightly faster but it will take a million rows to see differences.

There are brute force methods that are twice as fast but 350ms vs 700ms for a million rows may not make it worth it.

And, to reiterate the other discussion on this thread, don't use FORMAT. It's about 43 times slower in measured tests.

And, finally and to reiterate what Drew frequently states, this type of formatting should be done in the presentation layer, even if it's Oracle. That's if you have a presentation layer.

--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)