May 9, 2012 at 6:35 pm
How to store date in the USA format (05-09-2012 19:28:35) in SQL Server Table? I already have date stored in sql server table in the format (2012-05-09 00:00:00). This table is a back end to the access applications and when users access this table from Microsoft access they want to see the date in the US format. Please Help.
May 9, 2012 at 7:19 pm
Anybody?
May 9, 2012 at 7:28 pm
I guess if you want to run the query on SQL Server and you are content to return a character string to your MS Access application, you can do something like this:
SELECT CONVERT(CHAR(11),GETDATE(), 101)+CONVERT(VARCHAR(8),GETDATE(), 108)
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
May 9, 2012 at 7:39 pm
Thank you for your reply. I can do this in SQL Server but the problem I have is, I just want date to be stored in the USA format permanently in the sql server table.
May 9, 2012 at 7:59 pm
The datetime datatype stores the data differently that the way you think it is being displayed. That's controlled by a SQL Server default setting.
To "store" in US format as you want, you must store as a character string. And that is definitely not recommended. You should be formatting the data, either upon retrieval as I have suggested or (better) in your client application.
Surely the datetime internal data storage format between SQL and Access are compatible. You just need to figure out how to convert it on display in MS Access.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
May 9, 2012 at 9:37 pm
I'll strongly second that. Storing formatted dates and times in SQL might look good from where you're standing right now but you'll pay dearly for it in the future. Do the formatting in whatever output you're trying to build. Don't store formatted dates in a table.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 10, 2012 at 3:34 pm
I agree. Formatting the display on the UI is the best way. It provides more flexibility than if you do the formatting in T-SQL.
Every visual development environment I know of has standard date components that allow you to select the date, either from standard formats, or by using customizable masking.
May 12, 2012 at 11:17 am
Thank you all for your suggestions.
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply