Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
cms9651
Starting Member
28 Posts |
Posted - 2013-02-15 : 07:48:38
|
hi there, hope in your help.I need convert the nvarchar(255) field [myDates] to Datetime.I tried this query, but I've the error.I'm not sure where to go from here. Any help would be greatly appreciated.SELECT CONVERT ( DATETIME, [DB1].[tkt].[myDates], 103 )FROM [DB1].[tkt];[Err] 22007 - [SQL Server]Conversion failed when convertingdate and/or time from character string.DROP TABLE [DB1].[tkt]GOCREATE TABLE [DB1].[tkt] ([myDates] nvarchar(255) NULL)GODBCC CHECKIDENT(N'[DB1].[tkt]', RESEED, 3806913)GOINSERT INTO [tkt] ([myDates]) VALUES ('15/02/2013 10:34');GOINSERT INTO [tkt] ([myDates]) VALUES ('15/02/2013 10:28');GOINSERT INTO [tkt] ([myDates]) VALUES ('15/02/2013 10:38');GOINSERT INTO [tkt] ([myDates]) VALUES ('15/02/2013 10:28');GOINSERT INTO [tkt] ([myDates]) VALUES ('15/02/2013 10:29');GOINSERT INTO [tkt] ([myDates]) VALUES ('15/02/2013 10:27');GOINSERT INTO [tkt] ([myDates]) VALUES ('15/02/2013 11:19');GOINSERT INTO [tkt] ([myDates]) VALUES ('15/02/2013 10:27 15/02/2013 10:27');GOINSERT INTO [tkt] ([myDates]) VALUES ('15/02/2013 10:33');GOINSERT INTO [tkt] ([myDates]) VALUES ('15/02/2013 10:28');GOINSERT INTO [tkt] ([myDates]) VALUES ('15/02/2013 10:32');GOINSERT INTO [tkt] ([myDates]) VALUES ('15/02/2013 10:27');GOINSERT INTO [tkt] ([myDates]) VALUES ('15/02/2013 10:45');GOINSERT INTO [tkt] ([myDates]) VALUES ('15/02/2013 10:27');GOINSERT INTO [tkt] ([myDates]) VALUES ('15/02/2013 10:39');GOINSERT INTO [tkt] ([myDates]) VALUES ('15/02/2013 10:26');GOINSERT INTO [tkt] ([myDates]) VALUES ('15/02/2013 10:31');GOINSERT INTO [tkt] ([myDates]) VALUES ('15/02/2013 10:26');GOINSERT INTO [tkt] ([myDates]) VALUES ('15/02/2013 10:39');GOINSERT INTO [tkt] ([myDates]) VALUES ('15/02/2013 10:26');GOINSERT INTO [tkt] ([myDates]) VALUES ('15/02/2013 10:44');GOINSERT INTO [tkt] ([myDates]) VALUES ('15/02/2013 10:26');GOINSERT INTO [tkt] ([myDates]) VALUES ('15/02/2013 10:26');GOINSERT INTO [tkt] ([myDates]) VALUES ('15/02/2013 11:49');GOINSERT INTO [tkt] ([myDates]) VALUES ('15/02/2013 10:31 15/02/2013 10:31');GOINSERT INTO [tkt] ([myDates]) VALUES ('15/02/2013 10:24');GOINSERT INTO [tkt] ([myDates]) VALUES ('15/02/2013 10:30');GOINSERT INTO [tkt] ([myDates]) VALUES ('15/02/2013 10:25');GOINSERT INTO [tkt] ([myDates]) VALUES ('15/02/2013 10:25');GOINSERT INTO [tkt] ([myDates]) VALUES ('15/02/2013 10:24');GO |
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-02-15 : 07:54:15
|
Your sample data shows data such as the one shown in red. Did you really insert those into the table? If you did, that is the reason:INSERT INTO [tkt] ([myDates]) VALUES ('15/02/2013 11:19');GOINSERT INTO [tkt] ([myDates]) VALUES ('15/02/2013 10:27 15/02/2013 10:27');GOINSERT INTO [tkt] ([myDates]) VALUES ('15/02/2013 10:33');GO In any case, it might be a good idea to make the data type of the column DATETIME, and make sure the values that you insert into the column can be converted to DATETIME before you insert the data. |
|
|
cms9651
Starting Member
28 Posts |
Posted - 2013-02-15 : 08:01:56
|
thank you for reply.I'm not an administrator of the database and I can't change the table.Can't be excluded in the query the data shown in red? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-02-15 : 08:48:23
|
quote: Originally posted by cms9651 thank you for reply.I'm not an administrator of the database and I can't change the table.Can't be excluded in the query the data shown in red?
If you have only DateTime data as shown above, then try this-- Update your Table as followsUPDATE [DB1].[tkt] SET [myDates] = LEFT([myDates], 16)GOSELECT CONVERT ( DATETIME, [DB1].[tkt].[myDates], 103 )FROM [tkt];GO NOTE:Whenever you want to store DATETIME Values, must use DATETIME Datatype--Chandu |
|
|
cms9651
Starting Member
28 Posts |
Posted - 2013-02-15 : 09:01:14
|
thanks a lot! |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-02-15 : 09:12:17
|
quote: Originally posted by cms9651 thanks a lot!
Welcome--Chandu |
|
|
|
|
|
|
|