October 29, 2008 at 1:16 am
hi all,
I have problem with split DateTime value in two separate column in Sql.
Example-- Suppose in DateTime field Name dtmDate it hold the value like this. '2008-10-22 11:41:36.920'
I want this value in two column.
i.e. Date = '2008-10-22 ' and Time ='11:41:36'
Thanks in advance.
Praveen
October 29, 2008 at 1:35 am
you will have to use the datepart function and use various parts like day, month,year, hours, mins in a string concatenated with /'s of -'s to make the date and time as per your requirements.
October 29, 2008 at 1:52 am
Getting the date only is fairly easy
select dateadd(dd, datediff(dd,0, getdate()),0)
Easiest way to get the time is probably to convert the date to varchar and use substring to get the time only.
One question. Why do you want the date and time in two separate fields? It makes the date much harder to manipulate, makes comparisons much harder than they should be and generally is a really bad idea.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 29, 2008 at 1:55 am
You better don't.
There is no any reason why store date and time separately could be better than datetime.
If you eventually need date portion of datetime value you may use DateOnly function.
Search this forum for dbo.DateOnly and choose one of the options.
_____________
Code for TallyGenerator
December 17, 2008 at 7:37 am
There are plenty of reasons to split a date and time field using a view or other, but not within the original table/field (my opinion). Here's what I've figured out after needing to split a date and time that should have been stored separately from the start given the logic of the application design and/or planning.
Replace DATETIME with your datetime field.
Let's assume your datetime field has a value of 12/17/2008 1:30:08 AM
CONVERT(VARCHAR, DATETIME, 101)
Returns the current date 12/17/2008
http://www.bennadel.com/blog/122-Getting-Only-the-Date-Part-of-a-Date-Time-Stamp-in-SQL-Server.htm
Let's assume your datetime field has a value of 12/17/2008 1:30:08 AM
CONVERT (VARCHAR, DATETIME, 108)
Returns 13:30:08
http://www.windows-tech.info/15/d8d0520d5991743b.php
LTRIM(REPLACE(REPLACE(SUBSTRING(CONVERT (VARCHAR, CheckInDate), 13, LEN(CheckInDate)), 'P', ':00 P'), 'A', ':00 A'))
Returns 1:30:00 AM (this drops the seconds, which then adds :00 in place of them, but if you don't need the seconds it works)
In my circumstance I'm using CONVERT(VARCHAR, DATETIME, 101) AS MyDateValue in a view to return the date only, or if using 108 instead of 101 - time only.
Personally I want my time to show as 1:30:08 AM rather than military. I'll figure that out next, but here's a start for those needing to split the date and time from one field and put each into their own field.
December 17, 2008 at 8:02 am
Jason White (12/17/2008)
Here's what I've figured out after needing to split a date and time that should have been stored separately from the start.
I see no reasons listed why you'd want those stored separately. Having to split them for display purposes is not a reason for changing the way they are stored in the database. Care to list some of these reasons?
December 17, 2008 at 9:23 am
Before I explain let me say, I know what you mean about no reason for splitting the date and time. But to me that assumes your front-end code, that may require the fields separate, is accessible to tweak. Not to mention assumes your programmer(s) are available to make changes to accommodate database design/schema.
We have a equipment checkout application that's custom. In our circumstance we don't want to crack open and tweak this application because the code is tricky and works as is. The application already uses date and times separately to perform various functions that are used to perform reservations down to every quarter of an hour per day. It also has the ability to perform reoccurring reservations all at once. These functions also look at all existing reservations and checkouts to make sure a new reservation (and reoccurring reservation requests) aren't conflicting with an existing reservation and/or isn't being reserved for a piece of equipment that's already checked out. Having the dates and times separate is how the application and fields were initially created. There is some degree of logic as to how it makes checking conflicts easier, but I don't know how necessary it is to have them separate. Nevertheless, it is what it is.
The problem we're having is much the same where a person makes a hotel reservation, arrives to the hotel, and there's no room available (Oops...a conflict). I have to include the data in another table, which is missing from the reservation system and needs to be included to ensure no conflicts appear when reservations are made. The field names are different, but the data is what's needed. This new table has the date and time in one field, but the existing front-end code requires them separate. For me it's either tweak the application or tweak the views currently used. I simply choose to tweak the views. Thus I need to split the date and time and make them separate fields.
I didn't write the application or create the tables, but I have to correct the problem from poor planning at the beginning. Fun, Fun!!!
December 17, 2008 at 10:39 am
Jason White (12/17/2008)
For me it's either tweak the application or tweak the views currently used. I simply choose to tweak the views. Thus I need to split the date and time and make them separate fields.I didn't write the application or create the tables, but I have to correct the problem from poor planning at the beginning. Fun, Fun!!!
Again, you're not changing the way it is stored in the database, you're making a view to support application logic. A view satisfies your application requirements, while allowing you to maintain one column in the underlying table. I think it was a misunderstanding of what you were saying vs. what they were cautioning against here.
In your situation, due to design choices made by your application designer, you are "forced" into a design decision or a partial re-write. Just because you have to do it for that application, doesn't mean it's not something that should ordinarily be avoided. I've got a couple hundred cursor driven triggers in my DB from our Front end ERP system that I can't re-write, but I'd still caution someone else against using them :).
Given the somewhat simple nature of the question, they're covering the possibility that the OP 'may not know better'.
December 17, 2008 at 2:58 pm
Jason White (12/17/2008)
I simply choose to tweak the views. Thus I need to split the date and time and make them separate fields.
So, you change the views.
Absolutely fine.
But where is the reason to change the way datetimes are stored in database?
_____________
Code for TallyGenerator
December 17, 2008 at 3:35 pm
I've gotta agree with the others... if you want to split out dates and times, particularly formatted dates and times, AND you think you shouldn't follow best practices by doing it in the GUI or you need it to output to a file, then either do them as calculated columns or in a view... don't store the data as separated dates and times.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 18, 2008 at 9:12 am
It's all good folks. The original post stated that someone had the date and time in one field and needed to split them into two fields, which is exactly something I've had to do. I agree I wouldn't split the fields within the table, but by creating a view (sp or other) and separating the date and time I'd say that seems feasible and reasonable to me if one needs to do so for whatever reason. So that's the solution I was offering, even though my response is so much later than the original post that it may not have been worth it for the Author. Hopefully it will be worth something to someone else coming across the forum.
Perhaps the ongoing discussion were minor misunderstandings (self-included) with regard to should have, would have, could have. Either way I'd reiterate that if a field has the date and time in it as one value "don't try to undo this". However, if one needs to just see the time or just the date in separate fields then it can be done via views, sp's, UDF's or other. Agreed?
Thanks for everyone's input, and for having a place to post a solution that will hopefully be useful for someone someday. Note: the solution is actually easier in SQL 2K+ than with SQL 7 or 2000.
December 18, 2008 at 2:09 pm
Jason, what you describe in your lost post is presenting datetime values in different string formats.
It has nothing to do with splitting date and time parts.
And solution in SQL2k is easy as:
CONVERT(varchar(8), DateTimeValue, 112) - for date,
CONVERT(varchar(12), DateTimeValue, 114) - for time
You may choose another format for date depending on the settings and compatibility of your application.
Anyway, it's just presentation format, nothing else.
_____________
Code for TallyGenerator
December 19, 2008 at 4:12 pm
Jason White (12/18/2008)
Note: the solution is actually easier in SQL 2K+ than with SQL 7 or 2000.
How do you figure that?
--Jeff Moden
Change is inevitable... Change for the better is not.
December 22, 2008 at 1:54 am
Better DateOnly and TimeOnly algorithms.
DECLARE@Sample DATETIME
SET@Sample = '2008-10-22 11:41:36.920'
SELECTDATEADD(DAY, DATEDIFF(DAY, 0, @Sample), 0) AS DateOnly,
DATEADD(DAY, DATEDIFF(DAY, @Sample, 0), @Sample) AS TimeOnly
N 56°04'39.16"
E 12°55'05.25"
July 30, 2009 at 3:52 am
Agreed that this should be done in a view or calculated column.
One reason why one would want to split out date and time is to allow the use of separate date and time dimensions in Analysis Services to give intra day analysis, eg what time of day do we receive the most calls ?
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply