March 9, 2010 at 2:00 pm
Hi,
I have database table and we have StartDate and StopDate field but its not in
typical DATE datatype but unfortunately its in VARCHAR data type.
We have date field(both) in following format.
2009-11-13T15:01:00.2504758-05:00 (most of the dates are in this kind of format)
9/24/2009 3:58:10 PM (few records are like this format)
Now, when i am running my extract query to restrict monthly data but its also pulling
other month data too and when i troubleshoot my data, i found above two kind of records.
Please see my query:
select A.TrackID, A.Version, A.OS, A.PCID,
B.LTrackID, B.LType,B.PLCnt, B.PJobCnt,
C.PTrackID, C.Mode,
D.STrackID, D.Start, D.Stop, D.Source
from
Track A, LTrack B,
PTrack C, STrack D
where A.TrackID = D.TrackID
and D.STrackID = C.STrackID
and C.PTrackID = B.PTrackID
and D.Start >= '2009-11-01T14:51:33.625-05:00'
and D.Stop <= '2009-11-30T16:57:37.890625-05:00'
To avoid both the date format problem as we have into Database, how can i use to get the
Monthly data so i can't get those data as its from old format (9/24/2009 3:58:10 PM).
Thanks for your help!
poratips
March 16, 2010 at 8:52 am
Any help will be appreciated.
March 16, 2010 at 12:57 pm
The easiest way would be to change your columns from varchar into datetime format. You could add another column each with datetime format and copy the varchar values into the new column. Therewith you'd ensure that you won't face any loss of data or "misinterpretation" (e.g. 9/11/2009 could eithe be September 11th or November 9th).
Once the data are properly converted, delete the old columns and rename the new ones.
This would of course require to guarantee that all code that queries those columns will be able to properly work with the new format.
Another option would be to convert Start and Stop to datetime within your query. But this may cause a serious drop of performance because the query wouldn't be able to use any index.
Last but not least you could update your column and set the values that have a "bad format" to a value in the format of the remaining rows. But this would require to know the time zone that has been used...
I'd recommend option 1.
March 17, 2010 at 9:47 pm
Thanks Lutz.
The reason we created Varchar column instead of DateTime because we will be getting data in a different format and that contains date records like - 2009-11-13T15:01:00.2504758-05:00 and 9/24/2009 3:58:10 PM, now see that T in 2009-11-13T15:01:00.2504758-05:00 so how we can convert or store into DateTime field?
If i update the column each time either using Trigger or just update statement every time so we need to keep both the column into database type as in our regualr column we are inserting data through Web service.
Appreciate your response!
March 18, 2010 at 1:10 pm
The following code would allow you to convert ISO8601 with time zone (aka as style 127) into a valid datetime format.
SELECT CAST('2008-09-18T15:52:00.000-04:00' AS XML).value('xs:dateTime(.[1])', 'datetime')
I strongly recommend to enforce using the correct column data type.
I see (at least) three possible solutions:
1) change the web service to provide a consistent SQL Server compliant date format (not style 127)
2) change the web service to provide a consistent style 127 fomat
3) change the Web service to call a stored procedure or a user defined function that will take care of the date conversion if there a numerous formats provided
I'd use option 1 together with option 3 (stored procedure)
March 18, 2010 at 2:49 pm
Thank you very much Lutz.
I tried to update my date filed with using your query but i have question that instead of in a yyyyMMDD format, how can i change it to:
DateColumn(Convert column)
2009-01-27T14:32:37.7389657-08:002009-01-27 22:32:37.740
2009-01-27T14:32:37.7389657-08:002009-01-27 22:32:37.740
I have new data coming into DB table in this format:
9/22/2009 7:58:29 AM
If i can match in this format then update my column so it will be consistent.
Thanks for your help!
March 18, 2010 at 3:01 pm
Sorry that i forgot to mention exact format:
I need in this format
DateColumn (Convert column)
2009-01-27T14:32:37.7389657-08:00 01/27/2009 22:32:37 PM
2009-01-27T14:32:37.7389657-08:00 01/27/2009 22:32:37 PM
Thanks!
March 18, 2010 at 3:26 pm
I was talking about a datetime column. The data would be stored in the database standard edit: internal format and, if formatting of result sets is required, this would either be done by the app or within queries or views using CONVERT(). The format you posted earlier (2009-01-27 22:32:37.740) is the standard format used to display datetime values. Internally the value is stored as two integers.
Straight from BOL (the SQL Server help system usually installed together with SQL Server):
Values with the datetime data type are stored internally by the Microsoft SQL Server 2005 Database Engine as two 4-byte integers. The first 4 bytes store the number of days before or after the base date: January 1, 1900. The base date is the system reference date. The other 4 bytes store the time of day represented as the number of milliseconds after midnight.
Therefor, if you'd like to see the two integers in a different format, you need to use CONVERT().
March 18, 2010 at 4:26 pm
Thanks once again.
I totally agree with you.
Is it any way Ican convert this into following format?
01/27/2009 22:32:37.740 or 2009/01/27 22:32:37.740
Thanks.
March 25, 2010 at 2:13 pm
Hi,
If someone can help me out but my simple requirement is:
I have currently Date Data stored in two different format:
2009-11-09T15:53:44.2412829-07:00
2009-11-10T09:33:17.942678-07:00
2009-11-10T10:18:34.5119755-07:00
8/16/2009 6:53:19 PM
8/16/2009 6:53:19 PM
9/11/2009 9:06:41 AM
9/29/2009 3:12:07 PM
I want to update whole table and make it in one format like - 9/29/2009 3:12:07 PM (MM/DD/YYYY.....).
Thanks for your help!
March 25, 2010 at 2:42 pm
You have an answer on how to convert your character values into datetime format.
I also answered your question on how to get your input values transformed into datetime values (using CONVERT()).
Did you try to change your data into datetime values instead of character values?
If you insist in using character data type then you might have to use a double convert.
It would be alot easier for us to help you if you'd take the time to read and follow the first link in my signature and provide table def, some ready to use sample data and expected result. Please help us help you.
March 25, 2010 at 4:21 pm
Thanks so much!
Please see the following scripts for sample table and data:
Table Script:
CREATE TABLE [SSTrackTime](
[STrackID] [int] NOT NULL,
[Start] [nvarchar](50) NULL,
[Stop] [nvarchar](50) NULL,
CONSTRAINT [PK_SSTrackTime] PRIMARY KEY CLUSTERED
(
[STrackID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
Insert script:
SET NOCOUNT ON;
SET XACT_ABORT ON;
GO
SET IDENTITY_INSERT [dbo].[SSTrackTime] ON;
BEGIN TRANSACTION;
INSERT INTO [dbo].[SSTrackTime]([STrackID], [Start], [Stop])
SELECT '1', '2009-11-09T15:53:44.2412829-07:00', '2009-11-09T16:02:13.612613-07:00' UNION ALL
SELECT '2', '2009-11-10T09:33:17.942678-07:00', '2009-11-10T09:55:10.7253183-07:00' UNION ALL
SELECT '3', '2009-11-10T10:18:34.5119755-07:00', '2009-11-10T10:24:25.2191484-07:00' UNION ALL
SELECT '4', '8/16/2009 18:53', '8/16/2009 18:56' UNION ALL
SELECT '5', '8/16/2009 18:53', '8/16/2009 18:56' UNION ALL
SELECT '6', '9/11/2009 9:06', '9/11/2009 9:36' UNION ALL
SELECT '7', '9/29/2009 15:12', '9/29/2009 15:16' UNION ALL
SELECT '8', '9/30/2009 8:49', '9/30/2009 8:50' UNION ALL
SELECT '9', '9/2/2009 8:34', '9/2/2009 9:03' UNION ALL
SELECT '10', '9/8/2009 8:16', '9/8/2009 8:39'
COMMIT;
RAISERROR (N'[dbo].[SSTrackTime]: Insert Batch: 1.....Done!', 10, 1) WITH NOWAIT;
GO
SET IDENTITY_INSERT [dbo].[SSTrackTime] OFF;
Now i want to update the table to update both the start and stop column to make in a obe format from 2009-11-10T10:18:34.5119755-07:00 this kind of TimeeZone to
8/16/2009 18:56.
Please let me know if you need more information.
Thanks once again for oyur help!
March 25, 2010 at 4:33 pm
ALTER TABLE [dbo].[SSTrackTime] ADD Start_datetime DATETIME
ALTER TABLE [dbo].[SSTrackTime] ADD Stop_datetime DATETIME
GO
UPDATE [dbo].[SSTrackTime]
SET
start_datetime = CASE WHEN ISDATE(START)=1 THEN START ELSE CAST(START AS XML).value('xs:dateTime(.[1])', 'datetime') END,
stop_datetime = CASE WHEN ISDATE(stop)=1 THEN stop ELSE CAST(stop AS XML).value('xs:dateTime(.[1])', 'datetime') END
FROM [dbo].[SSTrackTime]
SELECT *
FROM [SSTrackTime]
March 27, 2010 at 10:36 pm
Thanks so much for your help!
I think as per you example, i need to add two column and update the table.
Can you show me what will be the best idea to implement as i might need to keep the start and stop column as it is and when i add Start_datetime and Stop_datetime column, how can i avoid to update the table regulary ot best way to handle this extra column.
It will be very helpful for me when i have to pull the report for weekly or monthly but its ok to update frequently after adding these columns?
thanks,
Poratips
March 30, 2010 at 1:21 pm
Hi Lutz,
Thanks so much for your help!
When i alter to add columns as datatime datatype and update my both the columns, can you show me how to make date format like 9/4/2008 9:00:01 AM
instead of 2009-10-13 21:42:24.223
thanks,
Poratips
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply