November 16, 2020 at 10:36 pm
Hi all
Can anyone help me turn a timestamp that looks like this: "1994-10-07 00:00:00.0000000" into "10-07-1994", then into 10-94?
I tried cast(DOB as date) and it came back as 1994-10-07.
Any assistance would be appreciated.
November 16, 2020 at 10:37 pm
My apologies, I meant 10-1994
November 16, 2020 at 10:49 pm
if you are using SQL 2016 or later, it's very easy:
select format(getdate(), 'MM-dd-yyyy'), format(getdate(), 'MM-yy')
;
if not, it's still possible with date related functions.
November 16, 2020 at 11:04 pm
Thanks Sterling. I saw that when I googled it but my problem is I am not sure where that would go and how I would introduce it into the code. Here is my code. The two with "cast" is what I am trying to format MM-DD-YYYY. Then I will finish it off with the MM-YYYY based on the DeathDTS.
SELECT DISTINCT
CASE WHEN Month(HA.DischargeDTS) > 9 THEN YEAR(HA.DischargeDTS) + 1 ELSE YEAR(HA.DischargeDTS) END as FY
,PAT.PatientFirstNM
,PAT.PatientLastNM
,CAST(PAT.BirthDTS AS DATE)
,CAST(PAT.DeathDTS AS DATE)
,PAY.PayorID
,PAY.PayorNM
,FIN.SubscriberID as 'INSUREDSID NUMBER'
November 16, 2020 at 11:07 pm
format is very slow and should be avoided whenever possible.
it does help to read the manual
convert(date, dob, 105) -- 105 format is DD-MM-YYYY
full
select right(convert(char(10), convert(date, dob), 105), 7)
November 16, 2020 at 11:47 pm
Thanks Frederico. The MM-YYY worked and it didnt take very long. Unforunately I needed the mm-dd-yyyy for the first two. But its cool..I wont convert those two.
November 17, 2020 at 12:00 am
true, format() is slower, and I started using it since it was not available until SQL server 2012 and my employer migrated from SQL 2008 to SQL 2016. Just like application programmers move away from native C++ and adopt other managed code(java, C#, etc, performance can be compensated by hardware advancement), readability and programmability are also very important and cannot be compensated by hardware. Entity Framework or other ORM tool generates not very efficient code, but more developers are using it for rapid application development. Application layer has a far more significant impact on the database performance than those functions.
November 17, 2020 at 12:04 am
I did say reading the manual is advisable.
format 110 will give the mm-dd-yyyy you asked for the other columns.
and easier as you don't need the right function
November 17, 2020 at 12:06 am
Thanks..I found that
November 17, 2020 at 4:23 am
true, format() is slower, and I started using it since it was not available until SQL server 2012 and my employer migrated from SQL 2008 to SQL 2016. Just like application programmers move away from native C++ and adopt other managed code(java, C#, etc, performance can be compensated by hardware advancement), readability and programmability are also very important and cannot be compensated by hardware. Entity Framework or other ORM tool generates not very efficient code, but more developers are using it for rapid application development. Application layer has a far more significant impact on the database performance than those functions.
Gosh... I wish people wouldn't take that attitude. Performance always matters and if some one can't read CONVERT code, I don't actually want them in my database anyway. Justifying performance issues is justifying the all to frequently found "Death by a Thousand Cuts" where all these supposedly little (and this one is NOT little) things add up into one big whopping mess for both performance and resource usage.
Stop using FORMAT. It's not worth it. If you want to continue using it, that's your unfortunate choice but now other people know that it will be an unfortunate choice.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 17, 2020 at 6:24 pm
no matter whether you like it or not, 1)application developers write managed code more; 2)application developers implemented the functions, including FORMAT, CONVERT, CAST, LTRIM, RTRIM, TRIM, LEFT, RIGHT; 3)FORMAT is more similar to the .net functions(Format, FormatDate, FormatNumber, etc) ; 4) more code will be generated by tools in the front end.
November 17, 2020 at 7:03 pm
Read the ANSI/ISO standards for this language, you'll see that the only display format permitted for dates is based on ISO 8601 (YYYY-MM-DD). We had to pick one display format to guarantee consistency and this is the one we picked.
There is been a discussion in the standards community to add the MySQL formats. They are YYYY-MM-00 for a for a month within a year and YYYY-00-00 for an entire year. They have the advantage of having the same regular expression as the 8601 standards, so they'll sort correctly. There is no need to invent your own local dialect for displaying months within a year that nobody else uses.
Please post DDL and follow ANSI/ISO standards when asking for help.
November 17, 2020 at 7:48 pm
no matter whether you like it or not, 1)application developers write managed code more; 2)application developers implemented the functions, including FORMAT, CONVERT, CAST, LTRIM, RTRIM, TRIM, LEFT, RIGHT; 3)FORMAT is more similar to the .net functions(Format, FormatDate, FormatNumber, etc) ; 4) more code will be generated by tools in the front end.
its not a matter of liking it or not - if something does not work as expected and performs badly then those that choose to use it are those that do not care about their clients and the impact their choices have on them.
Many developers that do use managed code after awhile tend to look at the performance - and start changing their code precisely to avoid the pitfalls of fast development - which most of the times result in rather poor performance and issues that many times are hard to sort out.
but main point is - if you know something is bad do not suggest it to others - or if you do then at least mention that it is a poor choice and present the better alternative.
November 17, 2020 at 11:38 pm
Why Microsoft released a less performant FORMAT function to make SQL server look bad? I am sure Microsoft released it from requests by SQL server community. Not everyone/every application is so keen/so sensitive to the microsecond/millisecond improvement. It's much better to read/understand than convert with cryptic style(101, 120, etc), it fits application developers better than convert. Some people could discourage using convert due to less readability.
SQL server is rich in features, it's good for one scenario, could be seriously bad for another like temp table/table variable/CTE; index is good for read vs index slows down write. The balance is the key.
Similar to missing index suggestions, it's the DBA's responsibility to create it or not. Either don't create any or create all were likely wrong, but it's still a good feature to have.
If it's so bad, Microsoft will improve on it, just as Microsoft fixed the notorious message 8152, "String or binary data would be truncated" for SQL 2016 SP2(+CU?) or later.
November 17, 2020 at 11:56 pm
Look back at the old Sybase SQL Server days. We had money and small money, as well as the date formatting options of convert (). The real reason it was. There was to keep COBOL programmers happy! In their language. They have an extra clause in their data declarations called a picture clause, which gave all kinds of formatting options for everything. Since when SQL Server was created, COBOL was the dominant business language, Sybase had to follow suit with something. We didn't go all the way, but we did allow the most common complex formatting problems (dates and money) to be solved either by default or by the convert () function. This, of course, totally violates one of the most important principles of any tiered architecture; display formatting should be done in a display tier and not in the database tier.
Please post DDL and follow ANSI/ISO standards when asking for help.
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply