|
sql
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
change date from Mon dd yyyy
How do I change a varchar field that is in the format Mon dd yyyy 12:00am to
MM/DD/YYYY? Thanks for any help... Try:
declare @t varchar (20) set @t = 'jun 19 2009 12:00am' select convert (varchar (20), convert (datetime, @t, 100), 101) -- Tom ---------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS SQL Server MVP Toronto, ON Canada https://mvp.support.microsoft.com/profile/Tom.Moreau "DONNA" <DO***@discussions.microsoft.com> wrote in message How do I change a varchar field that is in the format Mon dd yyyy 12:00am tonews:A4BF192A-89B3-4033-8497-2EE95F1D8FD9@microsoft.com... MM/DD/YYYY? Thanks for any help... Works perfectly Tom except I need to field to remain a varchar. Any ideas?
Show quoteHide quote "Tom Moreau" wrote: > Try: > > declare > > @t varchar (20) > > set @t = 'jun 19 2009 12:00am' > > select > > convert (varchar (20), convert (datetime, @t, 100), 101) > > > -- > Tom > > ---------------------------------------------------- > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS > SQL Server MVP > Toronto, ON Canada > https://mvp.support.microsoft.com/profile/Tom.Moreau > > > "DONNA" <DO***@discussions.microsoft.com> wrote in message > news:A4BF192A-89B3-4033-8497-2EE95F1D8FD9@microsoft.com... > How do I change a varchar field that is in the format Mon dd yyyy 12:00am to > MM/DD/YYYY? > > Thanks for any help... > > It does. The "outer" convert changed it back to varchar (20).
-- Tom ---------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS SQL Server MVP Toronto, ON Canada https://mvp.support.microsoft.com/profile/Tom.Moreau "DONNA" <DO***@discussions.microsoft.com> wrote in message Works perfectly Tom except I need to field to remain a varchar. Any ideas?news:7C40F8E4-A7D8-41A0-A666-77F33933F6CB@microsoft.com... Show quoteHide quote "Tom Moreau" wrote: > Try: > > declare > > @t varchar (20) > > set @t = 'jun 19 2009 12:00am' > > select > > convert (varchar (20), convert (datetime, @t, 100), 101) > > > -- > Tom > > ---------------------------------------------------- > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS > SQL Server MVP > Toronto, ON Canada > https://mvp.support.microsoft.com/profile/Tom.Moreau > > > "DONNA" <DO***@discussions.microsoft.com> wrote in message > news:A4BF192A-89B3-4033-8497-2EE95F1D8FD9@microsoft.com... > How do I change a varchar field that is in the format Mon dd yyyy 12:00am > to > MM/DD/YYYY? > > Thanks for any help... > > As Tom responded, it IS still a varchar. But out of curiosity, why on earth
would you want to store dates in a varchar column? What is stopping someone from entering 68/24/5090 as a date? And what will that do to the rest of your application? On 6/19/09 11:53 AM, in article 7C40F8E4-A7D8-41A0-A666-77F33933F***@microsoft.com, "DONNA" <DO***@discussions.microsoft.com> wrote: Show quoteHide quote > Works perfectly Tom except I need to field to remain a varchar. Any ideas? I guess one could argue, even with a datetime type, what is stopping someone
from entering an incorrect date/time? Linchi Show quoteHide quote "Aaron Bertrand [SQL Server MVP]" wrote: > As Tom responded, it IS still a varchar. But out of curiosity, why on earth > would you want to store dates in a varchar column? What is stopping someone > from entering 68/24/5090 as a date? And what will that do to the rest of > your application? > > > > > On 6/19/09 11:53 AM, in article > 7C40F8E4-A7D8-41A0-A666-77F33933F***@microsoft.com, "DONNA" > <DO***@discussions.microsoft.com> wrote: > > > Works perfectly Tom except I need to field to remain a varchar. Any ideas? > > > I guess one could argue, even with a datetime type, what is stopping someone Ok, let's play semantics, Linchi. What I meant was that the user will get> from entering an incorrect date/time? an error message on entry, if you use the correct data type. If you allow bad data in, that's worse, in my opinion. Because code all over the place could fall down in far more spectacular ways if it is written with the assumption that the data in that column is good. A I think what Aaron is getting at is that when you use a datetime datatype,
then only valid dates/times can go in there. As Aaron suggested, a value of '68/24/5090' can be accepted into a varchar column but not into a datetime column. -- Tom ---------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS SQL Server MVP Toronto, ON Canada https://mvp.support.microsoft.com/profile/Tom.Moreau "Linchi Shea" <LinchiS***@discussions.microsoft.com> wrote in message I guess one could argue, even with a datetime type, what is stopping someonenews:0590587B-D325-4840-8729-CB3210B78F60@microsoft.com... from entering an incorrect date/time? Linchi Show quoteHide quote "Aaron Bertrand [SQL Server MVP]" wrote: > As Tom responded, it IS still a varchar. But out of curiosity, why on > earth > would you want to store dates in a varchar column? What is stopping > someone > from entering 68/24/5090 as a date? And what will that do to the rest of > your application? > > > > > On 6/19/09 11:53 AM, in article > 7C40F8E4-A7D8-41A0-A666-77F33933F***@microsoft.com, "DONNA" > <DO***@discussions.microsoft.com> wrote: > > > Works perfectly Tom except I need to field to remain a varchar. Any > > ideas? > > Aaron, Tom;
I'm actually not being facitious here. This is a question everyone has to ask (actually answer). That is, how much check/control you leave to your app and how much you leave to your database? Database purists aside, often a balance must be struck among many factors (performance being one and a key one). In this particular case, sometimes it may make sense to use a string instead of a datetime (before the date type was there anyway). For instance, it may be cleaner and less error prone to partition on a date string than on a datetime. But I submit that in most cases I'd use a datetime type. Linchi Show quoteHide quote "Linchi Shea" wrote: > I guess one could argue, even with a datetime type, what is stopping someone > from entering an incorrect date/time? > > Linchi > > "Aaron Bertrand [SQL Server MVP]" wrote: > > > As Tom responded, it IS still a varchar. But out of curiosity, why on earth > > would you want to store dates in a varchar column? What is stopping someone > > from entering 68/24/5090 as a date? And what will that do to the rest of > > your application? > > > > > > > > > > On 6/19/09 11:53 AM, in article > > 7C40F8E4-A7D8-41A0-A666-77F33933F***@microsoft.com, "DONNA" > > <DO***@discussions.microsoft.com> wrote: > > > > > Works perfectly Tom except I need to field to remain a varchar. Any ideas? > > > > Being an anal-retentive DBA, I tend to put such checks and balances into the
datatypes, nullability and constraints of the database. Apps come and go. Code can have bugs. The constraints don't care about the apps. Just my $0.02. -- Tom ---------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS SQL Server MVP Toronto, ON Canada https://mvp.support.microsoft.com/profile/Tom.Moreau "Linchi Shea" <LinchiS***@discussions.microsoft.com> wrote in message I'm actually not being facitious here. This is a question everyone has tonews:922BA1ED-2D24-4D76-83EE-6049F8877919@microsoft.com... Aaron, Tom; ask (actually answer). That is, how much check/control you leave to your app and how much you leave to your database? Database purists aside, often a balance must be struck among many factors (performance being one and a key one). In this particular case, sometimes it may make sense to use a string instead of a datetime (before the date type was there anyway). For instance, it may be cleaner and less error prone to partition on a date string than on a datetime. But I submit that in most cases I'd use a datetime type. Linchi Show quoteHide quote "Linchi Shea" wrote: > I guess one could argue, even with a datetime type, what is stopping > someone > from entering an incorrect date/time? > > Linchi > > "Aaron Bertrand [SQL Server MVP]" wrote: > > > As Tom responded, it IS still a varchar. But out of curiosity, why on > > earth > > would you want to store dates in a varchar column? What is stopping > > someone > > from entering 68/24/5090 as a date? And what will that do to the rest > > of > > your application? > > > > > > > > > > On 6/19/09 11:53 AM, in article > > 7C40F8E4-A7D8-41A0-A666-77F33933F***@microsoft.com, "DONNA" > > <DO***@discussions.microsoft.com> wrote: > > > > > Works perfectly Tom except I need to field to remain a varchar. Any > > > ideas? > > > > Even with a datetime/smalldatetime you can easily constrain to midnight (or
simply ignore time) if that is the requirement. The date data type makes this easier, but I still submit that I would never use char/varchar to store date/time data in any version of SQL Server, sorry. The benefits of free validation far outweigh the benefits you suggest about tidiness. Maybe you could paint a more convincing use case? On 6/19/09 2:45 PM, in article 922BA1ED-2D24-4D76-83EE-6049F8877***@microsoft.com, "Linchi Shea" <LinchiS***@discussions.microsoft.com> wrote: Show quoteHide quote > Aaron, Tom; > > I'm actually not being facitious here. This is a question everyone has to > ask (actually answer). That is, how much check/control you leave to your app > and how much you leave to your database? Database purists aside, often a > balance must be struck among many factors (performance being one and a key > one). > > In this particular case, sometimes it may make sense to use a string instead > of a datetime (before the date type was there anyway). For instance, it may > be cleaner and less error prone to partition on a date string than on a > datetime. But I submit that in most cases I'd use a datetime type. > > Linchi Well, in some cases we actually have a char(8) column for trade dates right
next to a datetime column. This is basically a calculated column, but it's a real column. And we do date stuffs on this char(8) date column with no worry that it may contain a time fraction or dealing with precision issue around midnight with datetime values,or having to applying datetime functions in a query to get date values. A trade off of a little bit overhead at the insert time for simplified queries. Not a bad trade off. Linchi Show quoteHide quote "Aaron Bertrand [SQL Server MVP]" wrote: > Even with a datetime/smalldatetime you can easily constrain to midnight (or > simply ignore time) if that is the requirement. The date data type makes > this easier, but I still submit that I would never use char/varchar to store > date/time data in any version of SQL Server, sorry. The benefits of free > validation far outweigh the benefits you suggest about tidiness. Maybe you > could paint a more convincing use case? > > > > > > On 6/19/09 2:45 PM, in article > 922BA1ED-2D24-4D76-83EE-6049F8877***@microsoft.com, "Linchi Shea" > <LinchiS***@discussions.microsoft.com> wrote: > > > Aaron, Tom; > > > > I'm actually not being facitious here. This is a question everyone has to > > ask (actually answer). That is, how much check/control you leave to your app > > and how much you leave to your database? Database purists aside, often a > > balance must be struck among many factors (performance being one and a key > > one). > > > > In this particular case, sometimes it may make sense to use a string instead > > of a datetime (before the date type was there anyway). For instance, it may > > be cleaner and less error prone to partition on a date string than on a > > datetime. But I submit that in most cases I'd use a datetime type. > > > > Linchi > > While we were talking about using a CHAR/VARCHAR *or* a DATETIME column (not
both to represent the same fact), you can still get bad crap in there unless you constrain the heck out of it. So why is it a "real" column, when you can derive a CHAR(8) version of the DATETIME column at query time, or in a view, or compute+persist+index it if necessary? I'm still not clear on what you're gaining by maintaining a separate column manually. In my mind that's even worse than just storing the CHAR(8) by itself. On 6/19/09 3:18 PM, in article 830EADB1-583C-42AA-9C09-9D9413DF1***@microsoft.com, "Linchi Shea" <LinchiS***@discussions.microsoft.com> wrote: Show quoteHide quote > Well, in some cases we actually have a char(8) column for trade dates right > next to a datetime column. This is basically a calculated column, but it's a > real column. And we do date stuffs on this char(8) date column with no worry > that it may contain a time fraction or dealing with precision issue around > midnight with datetime values,or having to applying datetime functions in a > query to get date values. A trade off of a little bit overhead at the insert > time for simplified queries. Not a bad trade off.  
Other interesting topics
HELP, RAN OUT OF DISK SPACE
Run 32-bit and 64-bit instances on same machine logshipping on 2000 How to narrow down the failure message Backup and restore sql server 2005 Query is much slower in 2008 than in 2005 c2 files/sysxmitqueue SQL Server 2005 database monitoring SQL 2008 not starting Sql Server Shared Hosting |
|||||||||||||||||||||||
