SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 mm/dd/yyyy format
 Forum Locked
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

-Dman100-
Posting Yak Master

210 Posts

Posted - 12/27/2006 :  12:36:02  Show Profile
I have the following proc:

CREATE PROCEDURE spGetAllConferencesInUSAByState
@state CHAR(2)
AS

SELECT

conference_type_id,
conference_id,
name,
CONVERT(VARCHAR(11), start_date, 103) AS start_date,
CONVERT(VARCHAR(11), end_date, 103) AS end_date,
city,
stateabr,
venue,
booth,
conference_website_url,
country_id

FROM conference
WHERE stateabr=@state
AND country_id IN (SELECT country_id FROM country WHERE name='United States')
GO

the date is displayed in the following format: dd/mm/yyyy

How can I get the date in the following format: mm/dd/yyyy

Thanks for any help.

snSQL
Flowing Fount of Yak Knowledge

USA
1837 Posts

Posted - 12/27/2006 :  12:52:52  Show Profile
Look up the CONVERT function in Books Online - you're using date style 103, which is dd/mm/yyyy, you need to use 101, which is mm/dd/yyyy.
Go to Top of Page

PSamsig
Constraint Violating Yak Guru

Denmark
384 Posts

Posted - 12/28/2006 :  01:41:58  Show Profile  Send PSamsig a Yahoo! Message
Formating should be done in the frontend

-- If you give someone a program, you will frustrate them for a day; if you teach them how to program, you will frustrate them for a lifetime.
Go to Top of Page

Hariarul
Posting Yak Master

India
160 Posts

Posted - 12/28/2006 :  01:59:47  Show Profile
Try..

CONVERT(VARCHAR(10), start_date, 101) AS start_date,
CONVERT(VARCHAR(10), end_date, 101) AS end_date,
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22769 Posts

Posted - 12/28/2006 :  02:00:49  Show Profile  Send madhivanan a Yahoo! Message
If you use front end application, you should use Format function there

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

Jeff Moden
Aged Yak Warrior

USA
649 Posts

Posted - 12/29/2006 :  18:33:40  Show Profile
For all those saying to "format in the front end"... WHY?

--Jeff Moden

Edited by - Jeff Moden on 12/29/2006 18:33:53
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 12/30/2006 :  11:23:30  Show Profile  Visit jsmith8858's Homepage
quote:
Originally posted by Jeff Moden

For all those saying to "format in the front end"... WHY?

--Jeff Moden


Because that's where all formatting and presenation of the data returned from SQL should be done. You should just about never do any sort of formatting in T-SQL -- it is simply not designed for that. As soon as you format a date as "mm/dd/yyyy" or money value as "$1,233.20" or a bit as "Active/Inactive" you are suddenly returning nothing but VARCHAR's to your client instead of actual data, and forcing presentation and formatting upon your clients. If I want to format the date differently, or sort the data at the client in a different way, and you are returning a VARCHAR in the stored procedure instead of an actual date, guess what -- I need to immediately convert your strings BACK to a dateTime datatype before I can do anything with them.

If you have a nice datetime value in SQL, why would you convert it to a string, return the string to the client, and then have the client convert the string BACK to a datetime to use it, and then ultimately convert it AGAIN to a string to display it? Why not just return an actual Datetime VALUE and let the client do exactly what it wants with it?

Your job in T-SQL is simple: Return summarized, filtered, raw data to your client ... once you get to that point, sit back, relax, have a beer and enjoy the afternoon, your job at the database layer is done. The client's job is to take that raw data and ultimately present it somewhere.

Stripping off times and things like that are often necessary and can be done in T-SQL, but formatting to make things look a certain way should never be done in T-SQL. You might think you are making life easier for your client application by doing formatting in t-SQL, but as I demonstrated, you are making things more complicated both for yourself and for your client apps.




- Jeff

Edited by - jsmith8858 on 12/30/2006 11:26:54
Go to Top of Page

Jeff Moden
Aged Yak Warrior

USA
649 Posts

Posted - 12/30/2006 :  11:43:04  Show Profile
Thanks, Jeff. Hadn't thought about having to reformat in the app based on a change in requirements.

Based on what you said, about the only time I can see doing any formatting in SQL is when you're exporting to a file that requires a given format and no app is involved.

Appreciate you taking the time!

--Jeff Moden
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22769 Posts

Posted - 12/31/2006 :  22:48:28  Show Profile  Send madhivanan a Yahoo! Message
Well Said Jeff

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22769 Posts

Posted - 12/31/2006 :  23:04:11  Show Profile  Send madhivanan a Yahoo! Message
quote:
Originally posted by Jeff Moden

Thanks, Jeff. Hadn't thought about having to reformat in the app based on a change in requirements.

Based on what you said, about the only time I can see doing any formatting in SQL is when you're exporting to a file that requires a given format and no app is involved.

Appreciate you taking the time!

--Jeff Moden


Jeff Moden, I have some points

1 Why are you always against Do it in the front end application?. Did you think whoever says so dont know how to do it in sql?

2 Most of the time, there is Front end application and users dont know how to format data in front end instead they simply ask here about doing it in sql

3 Now-a-days rarely people want formatted data in Text file where you should do formatting in sql. I have done this previously because I had to export data to Text file where Date should be formated. Other than that I always prefer doing formations at Front end application

4 Whoever ask the following questions, I am sure, they use Front end application
a Generate Serial No
b Format Dates, Prefix $ in money value, concatenate strings and numbers
c Suppress Duplicate values
d Generate Running Total
e Generate comma seperated values based on each group

I know that you prefer formatting anything and everything in sql provided that you dont have enough knowledge in front end applications

I hope you will stop asking this question again and again

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

Jeff Moden
Aged Yak Warrior

USA
649 Posts

Posted - 01/01/2007 :  10:48:18  Show Profile
Madhivanan,

This was the first time that anyone (thank you very much, Jeff Smith) actually gave an answer that actually made sense. To answer your questions...

1. Originally, Yes, I thought that people that said to format in the front end didn't know how to do it in SQL. Now I just think some do and some don't. But, instead of just assuming that an app is available, I will always ask the question instead of summarily dismissing the formatting question with a hearty "Do it in the app" reply. I see you've changed your responses to "IF you have an app, do it there." Good show.

2. Ok, thanks. Didn't know that, either, and, actually, find it a bit hard to believe. For the people I've talked with, usually GUI folks with "some" SQL experience, it's the other way around... they know how to do it in the front end but, for some reason, really want to do it in the back end.

3. In the communications business, fixed width files, tabbed files, and CSV's are very common because of their overwhelming size, especially where usage (such as Call Detail Records) are concerned. Some have tried XML but the file sizes are just too huge to ship. Also, most of the telephone switches still create and expect fixed file formats. Yeah, a bit limited on my part but I live in the communications world so I play the part.

4. This one I don't understand your concern is on... I see no place better to create a serial number, suppress duplicates, and maybe even generate a running total (can easily be done in either) than in the server. I do see your point (thanks to Jeff Smith) about not formatting dates or money values... if there's an app involved, it's best not to do formatting in SQL Server.

So far as asking questions "again and again", allow me to explain... I gave up on writing front ends way back in the days of VB because my idiot boss instructed me to display warnings with Pink lettering on a Fusia background and cautions with White lettering on a Yellow background. And, as I'm sure many have experienced, that wasn't the first time that I was required to do such a ridiculous, hard to read screen design element. My point is, and you are correct, I've been out of the front-end business for a very long time and I no longer assume that I actually know anything about it (although, the basics never really change).

So, my old friend, I wanted to know "why" certain things should be done in the front end (because they certainly could be done in the back end) so that I could explain it to people asking instead of just dismissing the question as so many do. If I'd had gotten the correct answer previously, I wouldn't have asked it again and again. Lot's of folks, including yourself, have simply said that the app is better suited at it which, as you can tell, I whole-heartedly disagreed with. If it was just a matter of formatting, why not do it in SQL? The KEY that everyone left out, until now, is exactly what Jeff Smith explained. I not only understand THAT explanation, but I fully agree with it.

He didn't only say "Because that's where all formatting and presentation of the data returned from SQL should be done" which, by itself, doesn't explain "why", but he went on to imply that despite the best of intentions, it forces a format on the app. While that may have the desired short term effect, if the requirements in the app change, back end formatting may have limited the app in some way because they might need to change the date string back to a date/time to do something with it. Reminded me of how when people make the terrible desision of storing date and time data as VARCHAR in the database.

Like I said, that was the "key" that so many I've asked have simply omitted.

Personally, and if it's purely for formatting purposes (ie. no chance of the result set data being used/manipulated to perform other tasks), I still think it's easier to change a central proc in one place than to recompile and redistribute an app (old VB thinking on redistribution). Even today, where the apps are centralized web apps that need to be changed in only 1 place, I still think it's easier to change a proc than it is to recompile and re-deploy an app. But, then again, I'm not a GUI guy anymore and might simply be underestimating how easy it is to do in the app.

Anyway, thank you Jeff Smith for the answer that I haven't been able to get out of anyone else. And thank you, Madhivanan, for occasionally trying to answer it for the last couple of years. If nothing else, you're a patient man.





--Jeff Moden

Edited by - Jeff Moden on 01/01/2007 11:18:23
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22769 Posts

Posted - 01/01/2007 :  23:48:58  Show Profile  Send madhivanan a Yahoo! Message
Jeff Moden,

I think sometimes ago,I told you that if you format dates it will bcomes VARCHARs and not DATES and I am not sure if you remember it (But I didnt explain in details as Jeff did)

1 Usually my reply is If you use front end application,, do formation there and I dont simply say "do it in app" without knowing where the users want data to be displayed

2 If front end developers with sql knowledge do the formations at sql, then they must have again casted that to date when using that in sort or other display

3 I know that in some cases you need formatting at sql(I also did this previously). But when you say "doing this in database is fine" (in other thread), I assumed that you didnt have enough knowledge in front end applications

4 The points I descibed can be very easily done in applications with the help of simple while or for loops
Otherwise to do in sql, you need to use cursor or aggregated subquery that runs for each row which may take long time to exceute if there are hundreds of thousands of data. Suppressing duplicate values can be easily done in reports either by using formulae or grouping by that column

Other than these, I never disagree with you because your solutions are perfect and you give enough explanation on what your code does

I am happy to know that you are still considering me as your friend

I dont have much knowledge in sql and some of my points may be wrong

Once again I welcome you to this Forum. It is nice to see people like you who have rich enough knowledge in T-sql

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

Jeff Moden
Aged Yak Warrior

USA
649 Posts

Posted - 01/02/2007 :  03:12:40  Show Profile
That's pretty cool, Madhivanan... thank you for your friendship, humility, the nice compliments, and all that you've done to help folks out on the various forums I've seen you on.

Hey... just so you know on point #4... you don't need to use a cursor or an aggregated subquery that runs for each row to remove duplicates so long as you have a unique datetime or an auto-numbering column or something that uniquely indicates the chronology of a row.

Here's some test code you can play with so you can see what I mean... it takes about 45 seconds to build the million row test table...

--DROP TABLE BigTest
GO
--===== Create and populate a million row test table.
     -- Column "SomeDate" has a range of  >=01/01/2000  <01/01/2010
     -- That's ten years worth of dates.
 SELECT TOP 1000000
        RowNum     = IDENTITY(INT,1,1),
        SomeID     = CAST(RAND(CAST(NEWID() AS VARBINARY))*50000+1 AS INT),
        SomeValue  = CHAR(STR(RAND(CAST(NEWID() AS VARBINARY))*25+65))
                   + CHAR(STR(RAND(CAST(NEWID() AS VARBINARY))*25+65)),
        SomeNumber = CAST(RAND(CAST(NEWID() AS VARBINARY))*100 AS MONEY),
        SomeDate   = CAST(RAND(CAST(NEWID() AS VARBINARY))*3653.0+36524.0 AS DATETIME)
   INTO dbo.BigTest
   FROM Master.dbo.SysColumns sc1,
        Master.dbo.SysColumns sc2

--===== A table is not properly formed unless a Primary Key has been assigned
  ALTER TABLE dbo.BigTest
        ADD PRIMARY KEY CLUSTERED (RowNum)

--===== Find all duplicated rows EXCEPT for the row with the latest date.
     -- This can be used as a DELETE (see further below) and only takes
     -- about 7 seconds to find the 15 thousand or so dupes that show up
     -- in the million row table.
 SELECT bt1.*
   FROM dbo.BigTest bt1,
        dbo.BigTest bt2
  WHERE bt1.SomeID = bt2.SomeID
    AND bt1.SomeValue = bt2.SomeValue
    AND bt1.SomeDate < bt2.SomeDate

--===== This is what the delete would look like.  Again, it finds and 
     -- deletes all but the LATEST dupe.  To find and delete all but
     -- the earliest dupe, all you need do is change the "<" to ">".
     -- Again, it finds and deletes more than 15000 dupes in only 
     -- 7 seconds.  
 DELETE bt1
   FROM dbo.BigTest bt1,
        dbo.BigTest bt2
  WHERE bt1.SomeID = bt2.SomeID
    AND bt1.SomeValue = bt2.SomeValue
    AND bt1.SomeDate < bt2.SomeDate


--Jeff Moden
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22769 Posts

Posted - 01/02/2007 :  05:56:59  Show Profile  Send madhivanan a Yahoo! Message
Thanks for that code example Jeff Moden.

But When I said "You need cursor or Aggregated subquery", actually I meant them for Generating Serial No and Running Total and not for deleting duplicates. Please refer point 4 in my first reply

Also by saying suppressing duplicate values, I actually meant this

Samle data

Id       Descrip
10       Test1
10       Test2
10       Test3
11       Test1
11       Test2
12       Test2

and expected data (supress id becauase it is repeating)

Id       Descrip
10       Test1
         Test2
         Test3
11       Test1
         Test2
12       Test2

For these type of questions, I think, the user definetely wants to show them in Reports

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 01/02/2007 :  09:27:24  Show Profile  Visit jsmith8858's Homepage
Jeff -- glad I could help clear things up!

- Jeff
Go to Top of Page

Jeff Moden
Aged Yak Warrior

USA
649 Posts

Posted - 01/02/2007 :  09:28:15  Show Profile
Aye... what kind of serial number did you have in mind?

Also, I haven't found a real performance problem generating the report example you've shown but will agree that it will either take an aggragate to do it (with GROUPing) or a second pass at a temp table... I still wouldn't use a cursor or a While loop on it...

--Jeff Moden
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22769 Posts

Posted - 01/02/2007 :  22:55:37  Show Profile  Send madhivanan a Yahoo! Message

By saying serail no, I meant Serail no for all rows or serial numbers for each group.

I have done all those numbering in Vb6 and Crystal Reports with simple while loop or making use of Reports special formula (RecordNumber, Running Total fature, etc). I also beleive that it will reduce execution time of server when they are done in front end.

So Whenever I see users asking those questions I assume that either they want to show them in Grids (Vb6,ASP.NET,etc) or in Reports.

Thats why before giving sql code to them I usually ask Where do you want to show data?. If they say they dont use any front ends, I give related link on how to to that

We have had very healthy discussions here. Thanks for all your inputs

Thanks Jeff Smith for your clear explanation

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

Jeff Moden
Aged Yak Warrior

USA
649 Posts

Posted - 01/03/2007 :  00:21:35  Show Profile
Ok... thanks for the "serial number" info... that might be faster in the app but I'll have to take a look...

So far as the dupe report goes that you identified in the previous post, I actually thought of a way to do it without having to use an aggregate, temp table, While loop, or cursor... does a million row (a bit of overkill but wanted to show how fast it is) report in about 40 seconds... you can use the same "BigTest" table I created in my last example. Here's the code for the report...

--===== Produce the full million row report with dupe "SomeID" supressed
     -- in about 40 seconds Use the GRID mode if in Query Analyser)...
 SELECT Some_ID = CASE 
                    WHEN bt.RowNum = d.RowNum
                    THEN ''
                    ELSE CAST(bt.SomeID AS VARCHAR(10))
                  END,
        bt.SomeValue,
        bt.SomeNumber,
        bt.SomeDate
   FROM dbo.BigTest bt
   LEFT OUTER JOIN
        (
         SELECT DISTINCT
                bt1.RowNum
           FROM dbo.BigTest bt1,
                dbo.BigTest bt2
          WHERE bt1.SomeID = bt2.SomeID
            AND bt1.SomeValue > bt2.SomeValue
        ) d
     ON bt.RowNum = d.RowNum
  ORDER BY bt.SomeID,Some_ID DESC,bt.SomeValue,SomeDate


--Jeff Moden

Edited by - Jeff Moden on 01/03/2007 00:22:10
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22769 Posts

Posted - 01/03/2007 :  07:28:13  Show Profile  Send madhivanan a Yahoo! Message

Thanks for that code example

My comments on your code are :-

Advantages
1 You didnt use Aggregated subquery(which is, in this case, always suggested) or Cursor
2 You made it clear that the table had primary key with clustered index

Disadvantages
1 If the existing table doesnt have primary or unique key you need to either alter that table or create temp table by copying all data from source table and create primary or unique key

2 Your suppressed column is always converted to VARCHAR(Not a big issue provided it is only for display)

Apart from these, the only suggestion I am able to give is, adding additional where caluse.

Given that Rownum is primary key and it has clustered index, adding WHERE bt.RowNum>0 (assuming RowNum is always greater than 0) will give slightly better performance

 SELECT Some_ID = CASE 
                    WHEN bt.RowNum = d.RowNum
                    THEN ''
                    ELSE CAST(bt.SomeID AS VARCHAR(10))
                  END,
        bt.SomeValue,
        bt.SomeNumber,
        bt.SomeDate
   FROM dbo.BigTest bt
   LEFT OUTER JOIN
        (
         SELECT DISTINCT
                bt1.RowNum
           FROM dbo.BigTest bt1,
                dbo.BigTest bt2
          WHERE bt1.SomeID = bt2.SomeID
            AND bt1.SomeValue > bt2.SomeValue
        ) d
     ON bt.RowNum = d.RowNum
     WHERE bt.RowNum>0
  ORDER BY bt.SomeID,Some_ID DESC,bt.SomeValue,SomeDate

But I am not sure if adding that additional conditional will always give expected result

Again, thanks for all your efforts in giving me an example codes

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 01/03/2007 :  09:06:44  Show Profile  Visit jsmith8858's Homepage
quote:
Originally posted by Jeff Moden

Ok... thanks for the "serial number" info... that might be faster in the app but I'll have to take a look...

So far as the dupe report goes that you identified in the previous post, I actually thought of a way to do it without having to use an aggregate, temp table, While loop, or cursor... does a million row (a bit of overkill but wanted to show how fast it is) report in about 40 seconds... you can use the same "BigTest" table I created in my last example. Here's the code for the report...

--===== Produce the full million row report with dupe "SomeID" supressed
     -- in about 40 seconds Use the GRID mode if in Query Analyser)...
 SELECT Some_ID = CASE 
                    WHEN bt.RowNum = d.RowNum
                    THEN ''
                    ELSE CAST(bt.SomeID AS VARCHAR(10))
                  END,
        bt.SomeValue,
        bt.SomeNumber,
        bt.SomeDate
   FROM dbo.BigTest bt
   LEFT OUTER JOIN
        (
         SELECT DISTINCT
                bt1.RowNum
           FROM dbo.BigTest bt1,
                dbo.BigTest bt2
          WHERE bt1.SomeID = bt2.SomeID
            AND bt1.SomeValue > bt2.SomeValue
        ) d
     ON bt.RowNum = d.RowNum
  ORDER BY bt.SomeID,Some_ID DESC,bt.SomeValue,SomeDate


--Jeff Moden



That's a nice exercise, but surely you realized that this is almost exponentially less efficient than a simple SELECT and doing your formatting at the front end. And the data returned by this SQL statement is completely worthless since not all rows have the necessary data to fully identify them at the client.


- Jeff
Go to Top of Page

Jeff Moden
Aged Yak Warrior

USA
649 Posts

Posted - 01/03/2007 :  09:42:11  Show Profile
Don't know about how fast a front end could do it... I'm not a GUI guy anymore... gave it up a long time ago. But I don't think you're considering things like I didn't have to pass the million rows to the client to build the report. It would be interesting to see what happens if someone actually did try to do the same thing from the front end.

So far as being completely worthless to the front end, you gotta remember, it's a REPORT, as requested! It's supposed to be worthless to the front end for anything except a simple serial display. And, it wouldn't take much more to put a header, subtotals, grand totals, etc on this. Shoot... run this "pig" through sp_MakeWebTask and you don't need the front end to do a bloody thing.

Anyway, you asked the question and I did it with some pretty good speed. Write some front-end code to do the same thing and let's compare performance notes... Not being a smart guy here... I've really been out of the GUI business for a long time and, between the million row data transfer and the required formatting, it just seems like the GUI would be slower. So I'm curious.

--Jeff Moden
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.2 seconds. Powered By: Snitz Forums 2000