Converting Varchar To Datetime
Oct 23, 2007
Hi,
I'm facing a small issue with date conversions. It would have been great if someone could help me out. I have a field in my database (SQL Server 2000) called SavDateTime of type varchar(50). I store dates in this field in the format "dd/MM/yy hh:mms". A sample date would be "23/10/2007 10:15:30 AM". Now I need to have an order by for this field, say like,
select * from sample order by SavDateTime desc
When I execute this query I get an error saying "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.". I tried using the convert method also like CONVERT(DATETIME,SavDateTime,103), but the problem still exists. It would have been helpful if you could provide me with a solution to this problem.
Thanks & Regards,
Frens
View 7 Replies
ADVERTISEMENT
Sep 17, 2007
Hi,
I wanted to convert the varchar to date time and here is what i am doing
DECLARE @dt VARCHAR(20)
SET @dt = '20070111' -- YYYYMMDD format
select CONVERT(datetime, @dt, 120)
This works perfectly fine and the result would be- 2007-01-11 00:00:00.000
But if i changed my datetime format from YYYYMMDD to YYYYMMDDHHMM then this is failing and throwing
"Conversion failed when converting datetime from character string."
Can any one please let me know how do we achieve this?
~Mohan
View 3 Replies
View Related
Mar 19, 2015
I am using a custom sql query to import data into Tableau. Problem is I need to change the varchar column data in SQL currently returning 18/01/2014 08:35:13 as a format into the date format 05/21/2014 3:55:51 PM before I can use it.
View 9 Replies
View Related
Jun 14, 2015
I have a column in a sql Table with a datatype nvarchar 255 and the value in the column is 07MAR2012:00:00:00.000. I want to convert into a datetime.
View 4 Replies
View Related
Feb 16, 2007
Hi,
It is not exactly what I stated in the subject - It's an outcome - exception thrown while executing non-query command.
I get this exception when I try to execute my stored procedure that takes datetime as one of its parameters.
I am using dataset designer to create table adapters and build queries. Then I simply use objectdatasource component that uses one of the table adapters and bind it to for example a detailsview control.
When I run this in debug mode and trace the objects everything looks perfect including these datetime parameters. It is sql server that throws the exception. I ran the sql profiler to see what exactly is going on, and I captured the command that is sent by ADO - it's broken into several lines right in the middle of my datetime parameters... this is the source of the problem. Everything is working fine when I take this command and execute it as a single line in the sql management studio.
Is there anything about ADO that I do not know?
View 2 Replies
View Related
Dec 4, 2004
hi
i want to pass a datetime variable from a asp page to MSSQL through a stored procedure, & i get this error:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E07)
[Microsoft][ODBC SQL Server Driver][SQL Server]Error converting data type varchar to datetime.
this is the code that i use in page:
strsql="sp_inventory @timee=' " & RecievedDate & " ' "
also i use this one:
but i still get this error
strsql="sp_inventory @timee=' " & cdate(RecievedDate) & " ' "
this is maybe useful that i get this errot on some PCs, not all the computeres.
sometimes work well, & sth not work.
View 2 Replies
View Related
Dec 22, 2004
Here is the stored procedure
the data type for the column b_trn_tentdte is decimal(8,0).
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER Procedure Prc_New_cus_wo_prev_posted
@b_trn_tendte_Beg_Date datetime
,@b_trn_tendte_End_Date datetime
@b_trn_tendte_Beg_Date1 datetime
,@b_trn_tendte_End_Date2 datetime
as
declare @Beg_tentdte datetime
declare @End_tentdte datetime
declare @Beg_tentdte1 datetime
declare @Beg_tentdte2 datetime
set @Beg_tentdte = convert(varchar(10), @b_trn_tendte_Beg_Date, 112)
set @End_tentdte = convert(varchar(10), @b_trn_tendte_End_Date, 112)
set @Beg_tentdte1 = convert(varchar(10), @b_trn_tendte_Beg_Date1, 112)
set @Beg_tentdte2 = convert(varchar(10), @b_trn_tendte_End_Date2, 112)
/* this query looks for customers with posted
transactions but none prior to the date range
in question */
Select distinct b_cus_cname
from
bar_cus_db_rec
,bar_trn_db_rec b
where b.b_trn_instid = ''
and b.b_trn_instid = b_cus_instid
and b.b_trn_actid = b_cus_cusid
and convert(varchar(10), b.b_trn_tentdte) between @Beg_tentdte and @End_tentdte
and not exists (select c.b_trn_actid
from bar_trn_db_rec c
where c.b_trn_instid = b.b_trn_instid
--and c.b_trn_tentdte between 19970901 and 20030229
and convert(varchar(10), c.b_trn_tentdte) between @Beg_tentdte1 and @Beg_tentdte2
and c.b_trn_actid = b.b_trn_actid)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
every time I run the query, I get this error message:
Error converting data type varchar to datetime.
thanks for your assistance.
View 5 Replies
View Related
Jun 21, 2006
An application i am using creates an error.
Using SQL-profiler i catch this statement:
exec sp_executesql N'insert into RECORDING_SCHEDULE (programme_oid, capture_source_oid, filename, status, recording_group, recording_type, manual_channel_oid,
manual_start_time, manual_end_time, quality_level, pre_pad_minutes, post_pad_minutes) values (@P1,@P2,@P3,@P4,@P5,@P6,@P7,@P8,@P9,@P10,@P11,@P1 2)',N'@P1 int,@P2 int,@P3
nvarchar(19),@P4 int,@P5 int,@P6 int,@P7 int,@P8 datetime,@P9 datetime,@P10 int,@P11 int,@P12 int',177748,2,N'Lilla röda traktorn',4,40,5,16,''2006-06-21
17:00:00:000'',''2006-06-21 20:00:00:000'',1,1,2
After removing the double ' around the date and time parts i get the following error when running in QA:
Msg 8114, Level 16, State 5, Line 0
Error converting data type varchar to datetime.
If a remove the time portion it works.
The DDL for the table is:
CREATE TABLE [dbo].[RECORDING_SCHEDULE](
[oid] [int] IDENTITY(1,1) NOT NULL,
[programme_oid] [int] NULL,
[capture_source_oid] [int] NULL,
[status] [smallint] NOT NULL,
[filename] [varchar](255) NULL,
[recording_type] [int] NULL,
[recording_group] [int] NULL,
[manual_start_time] [datetime] NULL,
[manual_end_time] [datetime] NULL,
[manual_channel_oid] [int] NULL,
[quality_level] [int] NULL CONSTRAINT [DF__RECORDING__quali__38996AB5] DEFAULT ((0)),
[pre_pad_minutes] [int] NULL CONSTRAINT [DF__RECORDING__pre_p__398D8EEE] DEFAULT ((0)),
[post_pad_minutes] [int] NULL CONSTRAINT [DF__RECORDING__post___3A81B327] DEFAULT ((0)),
CONSTRAINT [aaaaaRECORDING_SCHEDULE_PK] PRIMARY KEY NONCLUSTERED
What am I missing?
View 2 Replies
View Related
Oct 13, 2006
Hi,
I am havin problems with the following giving a message
Msg 8114, Level 16, State 4, Line 0
Error converting data type varchar to datetime.
exec sp_executesql
@stmt=N'UPDATE CUSTOMER
SET [REQUEST] = @19, [DISC_EXPRY] = @28, [GROUP1] = @29, [GROUP2] = @31, [PR_LEVEL] = @48, [MOD_DATE] = @55, [MEM_CODE] = @63, [MEM_DATE] = @64, [HO_MOD] = @66, [LASTUPDATE] = @78
WHERE [ID] = @Old_1'
, @params=N'@19 nvarchar(4),@28 DateTime,@29 nvarchar(5),@31 nvarchar(5),@48 nvarchar(1),@55 DateTime,@63 nvarchar(7),@64 DateTime,@66 Integer,@78 Float,@Old_1 Integer',
@19= 'NRMA',@28= '1752-09-14',@29= 'ALBUM',@31= 'FRAME',@48= 'A',@55= '2006-10-10',@63= '1003.50',@64= '2006-10-10',@66= 3,@78= 39000.190633,@Old_1= 454636
however, as soon as I remove the datetime fields it works
exec sp_executesql
@stmt=N'UPDATE CUSTOMER
SET [REQUEST] = @19, [GROUP1] = @29, [GROUP2] = @31, [PR_LEVEL] = @48, [MEM_CODE] = @63, [HO_MOD] = @66, [LASTUPDATE] = @78
WHERE [ID] = @Old_1'
, @params=N'@19 nvarchar(4),@29 nvarchar(5),@31 nvarchar(5),@48 nvarchar(1),@63 nvarchar(7),@66 Integer,@78 Float,@Old_1 Integer',
@19= 'NRMA',@29= 'ALBUM',@31= 'FRAME',@48= 'A',@63= '1003.50',@66= 3,@78= 39000.190633,@Old_1= 454636
what am I doing wrong with the datetime parameters?
Grimhael
View 4 Replies
View Related
Sep 11, 2007
Hi all,
I need to migrate from a DB with a column containing dates as varchar to a new DB which will have a column with the same content as the source DB but in datetime format.
I have tried to use the cast function but i always get a message saying that the conversion result in an out of range error.
Someone can help me? PLEASE
Thanks
View 8 Replies
View Related
Sep 11, 2007
hi,
How do i convert a varchar field into the datetime data type? the reason i need this lies in the requirement that in the earlier data base the column that is hlding the date value is having the data type as varchar. and in the new design the column data type is datetime. i am using sql scripts for the data migration from the older design to the newer and got stuck with this datetime convertion issue. do let me know the best possible solution.
following are the sample data that is theer in the older table for the date.
12/12/2003
1/13/2007
01132004
1-1-2004
1.2.2001
there is no uniformity of the data that is stored currently.
thnkx in adv.
rahul jha
View 11 Replies
View Related
Oct 29, 2004
Hi,
I'm converting a query from Access to SQL Server.
In this query I select from a column that contains numbers, the result I want is a varchar that is always 2 chars wide..
Ie:
7 should be selected as '07'
12 should be selected as '12'
In the Access-query it's rather nicely done with:
Format(Str(mycolumn),"00")
I could not find a way to make CONVERT do the same job... but I found that:
LEFT('00',2-LEN(CAST(mycolumn as varchar)))+CAST(mycolumn as varchar)
will do the job.
But it feels like it could be done nicer.. any suggestions?
View 2 Replies
View Related
Mar 18, 2007
Hi,
I am trying ot do a count with a clause like this "amtdue >=10000 and amtdue <=25000" --- I get and error that says "Syntax error converting the varchar value '52.91' to a column of data type int" There are 20 million records in the table - this field is formatted as a varchar - can someone help me find the problem?
Thanks
John
View 3 Replies
View Related
Feb 28, 2008
hi all.
in my database i have two fields ..aprsal and datatype is varchar(100)
second field is maysal datatype is varchar(100)
i want to add these values after converting in to the int
how can i do this in sql
View 3 Replies
View Related
Dec 12, 2006
I am struggling with converting a certain varchar column into an int.I have a table that has 2 fields - one field holds the loan number andthe other field holds the codes associated with that loan number.Here's some example data:Loan# Codes11111 24-13-122222 133333 2-9I need to check the Codes field for certain code numbers. The Selectstatement I'd like to use is:SELECT Loan#FROM Table1 WHERE Codes IN (2, 13, 1)/*My desired results is that all loans from the above example would beselected because they all have one of these codes*/Of course I cannot use the above statement because the Codes field is avarchar. And if I put single quotes around the numbers in my INstatement I don't get the desired results; the fields with multiplecodes are excluded.But how do I convert this varchar to an int? A simple convert or caststatement doesn't work. I've looked all over the web to find how to dothis, but have not been able to figure it out. Any help would be muchappreciated.
View 5 Replies
View Related
Apr 5, 2007
Syntax error converting the varchar value '3.1.7.4.3.9.' to a column of data type int...
how to overcome this problem ..
View 6 Replies
View Related
Aug 20, 2007
I wrote a query that takes the field from one record and compares it to a different field in a different record. When I did it in access it worked. But I did a copie and paste into management studio and it is not working.
SELECT A.TRAN_ID, B.REFERENCE_DATA
FROM dbo.BSTRN_HEADER AS B LEFT OUTER JOIN
dbo.BSTRN_HEADER AS A ON B.REFERENCE_DATA LIKE A.TRAN_ID &'RECL'
I get the following error message:
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'RECL' to data type int.
So then I try:
SELECT A.TRAN_ID, B.REFERENCE_DATA
FROM dbo.BSTRN_HEADER AS B LEFT OUTER JOIN
dbo.BSTRN_HEADER AS A ON B.REFERENCE_DATA LIKE cast(A.TRAN_ID as varchar) & 'RECL'
I get the following error
Msg 402, Level 16, State 1, Line 1
The data types varchar and varchar are incompatible in the boolean AND operator.
Tran_id is an integer and reference data is a varchar and I need to convert tran_id to a Varchar
View 12 Replies
View Related
Jun 23, 2005
I have a password field which is of varbinary. Since its a varbinary I
cannot see the password in the database I only see hexadecimal values.
Now my question is that how can I convert those hexadecimal values to
string or varchar so I can read the password.
any ideas ??
View 1 Replies
View Related
Aug 28, 2000
Is it possible to convert from ntext to varchar or text??
This is the syntax: ALTER TABLE ssu ALTER COLUMN analysis varchar NULL. This is the error message
Server: Msg 4928, Level 16, State 1, Line 1
Cannot alter column 'ANALYSIS' because it is 'ntext'. Thanks in advance!
View 2 Replies
View Related
Nov 23, 2006
CREATE FUNCTION dbo.fnTS2VC
(
@ts BINARY(8)
)
RETURNS VARCHAR(16)
AS
BEGIN
RETURNSUBSTRING('0123456789ABCDEF', 1 + CAST(SUBSTRING(@ts, 1, 1) AS TINYINT) / 16, 1)
+ SUBSTRING('0123456789ABCDEF', 1 + CAST(SUBSTRING(@ts, 1, 1) AS TINYINT) % 16, 1)
+ SUBSTRING('0123456789ABCDEF', 1 + CAST(SUBSTRING(@ts, 2, 1) AS TINYINT) / 16, 1)
+ SUBSTRING('0123456789ABCDEF', 1 + CAST(SUBSTRING(@ts, 2, 1) AS TINYINT) % 16, 1)
+ SUBSTRING('0123456789ABCDEF', 1 + CAST(SUBSTRING(@ts, 3, 1) AS TINYINT) / 16, 1)
+ SUBSTRING('0123456789ABCDEF', 1 + CAST(SUBSTRING(@ts, 3, 1) AS TINYINT) % 16, 1)
+ SUBSTRING('0123456789ABCDEF', 1 + CAST(SUBSTRING(@ts, 4, 1) AS TINYINT) / 16, 1)
+ SUBSTRING('0123456789ABCDEF', 1 + CAST(SUBSTRING(@ts, 4, 1) AS TINYINT) % 16, 1)
+ SUBSTRING('0123456789ABCDEF', 1 + CAST(SUBSTRING(@ts, 5, 1) AS TINYINT) / 16, 1)
+ SUBSTRING('0123456789ABCDEF', 1 + CAST(SUBSTRING(@ts, 5, 1) AS TINYINT) % 16, 1)
+ SUBSTRING('0123456789ABCDEF', 1 + CAST(SUBSTRING(@ts, 6, 1) AS TINYINT) / 16, 1)
+ SUBSTRING('0123456789ABCDEF', 1 + CAST(SUBSTRING(@ts, 6, 1) AS TINYINT) % 16, 1)
+ SUBSTRING('0123456789ABCDEF', 1 + CAST(SUBSTRING(@ts, 7, 1) AS TINYINT) / 16, 1)
+ SUBSTRING('0123456789ABCDEF', 1 + CAST(SUBSTRING(@ts, 7, 1) AS TINYINT) % 16, 1)
+ SUBSTRING('0123456789ABCDEF', 1 + CAST(SUBSTRING(@ts, 8, 1) AS TINYINT) / 16, 1)
+ SUBSTRING('0123456789ABCDEF', 1 + CAST(SUBSTRING(@ts, 8, 1) AS TINYINT) % 16, 1)
ENDCall with
SELECT dbo.fnTS2VC(@@DBTS)
You can also use SELECT CAST(@@DBTS AS BIGINT) if you only need to convert the TIMESTAMP value to INTEGER.
Peter Larsson
Helsingborg, Sweden
View 4 Replies
View Related
Aug 14, 2007
Hi
I have a table named prodwin in which i have to update a column name mfgdt which is varchar(10) to 7 months which I am able to do but the problem is that since it is only 10 character it is cutting the end of the year for eg
Before the date was
5/22/2000
UPDATE PRODWIN_MM SET MFGDT = DATEADD(MM,7,MFGDT)
Now it is this
Dec 23 200
Please tell me what to do , should i use cast or convert ?
Thanks in Adv
View 7 Replies
View Related
Aug 22, 2007
Hi all,
I'm trying to update a table as follows:
Where in the table i got.
OldCol; a varchar
NewCol; Float
Update tbl set newCol = cast(float,oldcol)
Where isNumeric(oldcol) =1
But it give me the follwong error:
Error converting data type from Varchar to Float.
Any suggestion?
View 12 Replies
View Related
Jan 22, 2008
Hi all I would like anyone to help I want 2 convert a datetime to 2008-02-02 21:00 the output must leave the time just 2008-02-02
If (@DateCreated <> 0)
Begin
Set @varSelectSql = @varSelectSql + ' And convert(varchar(10),DateCreated,121) = '+ cast(@DateCreated as varchar(10))
End
View 4 Replies
View Related
Aug 2, 2007
why do i have this error? not converting a varchar into int 1 ALTER PROCEDURE dbo.RevertDB
2 (
3 @Log varchar(MAX) = NULL OUTPUT
4 )
5
6 /* Reverts Database to original "Clean" State */
7 AS
8 SET NOCOUNT OFF
9 DECLARE @RowsInDB AS int
10 SET @Log = 'RevertDB Started at ' + CAST(GETDATE() AS varchar(50)) + '<br />'
11
12 /* *** Disable Constraints ***
13 ALTER TABLE Booking NOCHECK CONSTRAINT ALL
14 ALTER TABLE InventoryPC NOCHECK CONSTRAINT ALL
15 ALTER TABLE PC NOCHECK CONSTRAINT ALL
16 ALTER TABLE Platform NOCHECK CONSTRAINT ALL*/
17
18 /* *** Start Deletes *** */
19 DELETE FROM Booking
20 SET @Log = @Log + 'Clear Table Booking - Done' + '<br />'
21 SET @RowsInDB = (SELECT COUNT(BookingID) FROM Booking)
22 SET @Log = @Log + '-- Rows Affected: ' + CAST(@@ROWCOUNT AS varchar(10)) + ', Rows in Table: ' + CAST(@RowsInDB AS varchar(10)) + '<br />'
23
24 DELETE FROM InventoryPC
25 SET @Log = @Log + 'Clear Table InventoryPC - Done' + ''
26 SET @RowsInDB = (SELECT COUNT(InventoryID) FROM InventoryPC)
27 SET @Log = @Log + '-- Rows Affected: ' + CAST(@@ROWCOUNT AS varchar(10)) + ', Rows in Table: ' + CAST(@RowsInDB AS varchar(10)) + '<br />'
28
29 DELETE FROM PC
30 SET @Log = @Log + 'Clear Table PC - Done' + '<br />'
31 SET @RowsInDB = (SELECT COUNT(PCID) FROM PC)
32 SET @Log = @Log + '-- Rows Affected: ' + CAST(@@ROWCOUNT AS varchar(10)) + ', Rows in Table: ' + CAST(@RowsInDB AS varchar(10)) + '<br />'
33
34 DELETE FROM Platform
35 SET @Log = @Log + 'CLear Table Platform - Done' + ''
36 SET @RowsInDB = (SELECT COUNT(PlatformID) FROM Platform)
37 SET @Log = @Log + '-- Rows Affected: ' + CAST(@@ROWCOUNT AS varchar) + ', Rows in Table: ' + CAST(@RowsInDB AS varchar(10)) + '<br />'
38
39 /* *** Enable Constraints ***
40 ALTER TABLE Booking WITH CHECK CHECK CONSTRAINT ALL
41 ALTER TABLE InventoryPC WITH CHECK CHECK CONSTRAINT ALL
42 ALTER TABLE PC WITH CHECK CHECK CONSTRAINT ALL
43 ALTER TABLE Platform WITH CHECK CHECK CONSTRAINT ALL*/
44
45 SET @Log = @Log + '*** End Truncates ***' + '<br />'
46 /* *** End Truncates *** */
47
48 /* *** Start Insert Platform *** */
49 SET @Log = @Log + 'Start Insert Platform' + '<br />'
50
51 EXEC dbo.InsertPlatfrom 'Windows XP SP2 Professional Edition', 'Some description for Windows XP SP2 Professional Edition over here …'
52 EXEC dbo.InsertPlatfrom 'Windows Vista Ultimate', 'See everything you''re working on more clearly with Windows Aero, and quickly switch between windows or tasks using Windows Flip 3D and Live Thumbnails. You can easily find what you need—when you need it―with Instant Search and live icon previews that display the actual contents of your files. And while you''re at it, give your personal productivity a boost with instant access to the information you care about using Windows Sidebar and Gadgets. Put these easy-to-use and customizable mini-applications on your desktop and reveal the information you''re looking for at a glance. Website: http://www.microsoft.com/windows/products/windowsvista/seeit/default.mspx'
53 EXEC dbo.InsertPlatfrom 'Apple Mac OS X Tiger', 'Some description for Apple Mac OS X Tiger over here …'
54 EXEC dbo.InsertPlatfrom 'Apple Mac OS X Leopard', 'Desktop: The new look of Leopard showcases your favorite desktop image and puts new file Stacks at your fingertips for a stunning, clutter-free workspace. Finder: Browse your files like you browse your music with Cover Flow. Time Machine: See how your system looked on any given day and restore files with a click. Website: http://www.apple.com/macosx/leopard/features/'
55 EXEC dbo.InsertPlatfrom 'Red Hat Linux', 'Some description for Red Hat Linux over here …'
56
57 SET @Log = @Log + 'Rows In Platform: ' + CAST((SELECT COUNT(PlatformID) FROM Platform) AS varchar(10)) + '<br />'
58 /* *** Start Insert PC *** */
59 SET @Log = @Log + 'Start Insert PC' + '<br />'
60
61 DECLARE @WinXP int, @WinVista int, @OSXTiger int, @OSXLeopard int, @RedHat int
62 SET @WinXP = (SELECT PlatformID FROM Platform WHERE Title = 'Windows XP SP2 Professional Edition')
63 SET @WinVista = (SELECT PlatformID FROM Platform WHERE Title = 'Windows Vista Ultimate')
64 SET @OSXTiger = (SELECT PlatformID FROM Platform WHERE Title = 'Apple Mac OS X Tiger')
65 SET @OSXLeopard = (SELECT PlatformID FROM Platform WHERE Title = 'Apple Mac OS X Leopard')
66 SET @RedHat = (SELECT PlatformID FROM Platform WHERE Title = 'Red Hat Linux')
67
68 EXEC dbo.InsertPC 'Fusion PC One', 'Description here ...', 'Intel Core2 Duo E6600 2.4 GHz 1066MHz', '1GB Dual Channel DDR2 667 SDRAM', '120GB SATA2 NCQ HDD', 'NVIDIA GeForce 8600 256MB GDDR3', '22" 3000:1 Wide Screen LCD', @WinXP
69 EXEC dbo.InsertPC 'Fusion PC Two', 'Description here ...', 'Intel Core2 Duo E6850 3 GHz 1333MHz', '2GB Dual Channel DDR2 800 SDRAM', '240GB SATA2 NCQ HDD', 'NVIDIA GeForce 8800 Ultra 256MB GDDR3 SLI', '24" 3000:1 Wide Screen LCD', @WinVista
70 EXEC dbo.InsertPC 'Fusion PC Three', 'Description here ...', 'AMD Athlon 64 X2 Dual Core 6000+ 3 GHz', '2GB Dual Channel DDR2 667 SDRAM', '240GB SATA2 NCQ HDD', 'ATI Radeon Cross Fire 2900 256MB GDDR3', '24" 3000:1 Wide Screen LCD', @WinVista
71 EXEC dbo.InsertPC 'Fusion X1', 'Description here ...', 'Intel Core2 Extreme Q6850 3 GHz 1333MHz', '6GB Dual Channel DDR2 800 SDRAM', '500GB SATA2 NCQ HDD', 'NVIDIA GeForce 8800 Ultra 256MB GDDR3 SLI', '30" 3000:1 Wide Screen LCD', @OSXLeopard
72 EXEC dbo.InsertPC 'Fusion X2', 'Description here ...', 'AMD Athlon 64 FX 74 3 GHz', '6GB Dual Channel DDR2 800 SDRAM', '500GB SATA2 NCQ HDD', 'NVIDIA GeForce 8900 Ultra SLI 256MB GDDR3', '30" 3000:1 Wide Screen LCD', @WinVista
73 EXEC dbo.InsertPC 'Fusion Tiger 1', 'Description here ...', 'Intel Core2 Duo E6600 2.4 GHz 1066MHz', '2GB Dual Channel DDR2 800 SDRAM', '120GB SATA2 NCQ HDD', 'NVIDIA GeForce 8600 256MB GDDR3 SLI', '22" 3000:1 Wide Screen LCD', @OSXTiger
74 EXEC dbo.InsertPC 'Fusion Linux 1', 'Description here ...', 'AMD Athlon 64 X2 6000+ 3 GHz', '1GB Dual Channel DDR2 800 SDRAM', '120GB SATA2 NCQ HDD', 'NVIDIA GeForce 8600 256MB GDDR3', '22" 3000:1 Wide Screen LCD', @RedHat
75
76 SET @Log = @Log + 'Rows In PC: ' + CAST((SELECT COUNT(PCID) FROM PC) AS varchar(10)) + '<br />'
77
78 /* *** Start Insert Inventory *** */
79 SET @Log = @Log + 'Start Insert Inventory' + '<br />'
80
81 DECLARE @F1 int, @F2 int, @F3 int, @FX1 int, @FX2 int, @FT1 int, @FR1 int
82 SET @F1 = (SELECT PCID FROM PC WHERE Title = 'Fusion PC One')
83 SET @F2 = (SELECT PCID FROM PC WHERE Title = 'Fusion PC Two')
84 SET @F3 = (SELECT PCID FROM PC WHERE Title = 'Fusion PC Three')
85 SET @FX1 = (SELECT PCID FROM PC WHERE Title = 'Fusion X1')
86 SET @FX2 = (SELECT PCID FROM PC WHERE Title = 'Fusion X2')
87 SET @FT1 = (SELECT PCID FROM PC WHERE Title = 'Fusion Tiger 1')
88 SET @FR1 = (SELECT PCID FROM PC WHERE Title = 'Fusion Linux 1')
89
90 EXEC dbo.InsertInventory 10, @F1, 2.5, 'iCluster Fusion One'
91 EXEC dbo.InsertInventory 10, @F2, 2.5, 'iCluster Fusion Two'
92 EXEC dbo.InsertInventory 10, @F3, 2.5, 'iCluster Fusion Three'
93 EXEC dbo.InsertInventory 6, @FX1, 6, 'iCluster Fusion X1'
94 EXEC dbo.InsertInventory 6, @FX2, 6, 'iCluster Fusion X2'
95 EXEC dbo.InsertInventory 10, @FT1, 3, 'iCluster Fusion Tiger One'
96 EXEC dbo.InsertInventory 30, @FR1, 2, 'iCluster Fusion Linux One'
97
98 SET @Log = @Log + 'Rows In Inventory: ' + CAST((SELECT COUNT(InventoryID) FROM InventoryPC) AS varchar(10))
99
100 RETURN @Log
if i remove the last line then all is well. but i get "An SqlParameter with ParameterName '@Log' is not contained by this
SqlParameterCollection."
View 2 Replies
View Related
Nov 18, 2003
i have a huge stored procedure abt 500 lines..and i am calling this sp from an asp.net page...thn i got this error - error converting varchar to numeric - and am trying to debug...is there any way we can find out where the error is coming from...like aproxly which line number..etcor do i have to go through each line manually and see where i am doing the conversion....
thanks
View 2 Replies
View Related
Jul 20, 2004
for some odd reason our other programmer used varchar datatype to store dates. he claims it gives him more control. now i am trying to sort it based on date. so i create a procedure:
CREATE PROCEDURE GetAllWeekEnding
AS
Select convert(datetime, we) as we2 FROM tblArchive order by we2
GO
if i use the convert function in the procedure, i'll get an error msg when i run the code. this is the code i am using.
Dim MyConnection As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("ConnectionStringSQL"))
Dim MyCommand As SqlCommand
MyCommand = New SqlCommand("GetAllWeekEnding", MyConnection)
MyCommand.CommandType = CommandType.StoredProcedure
MyConnection.Open()
Dim mydr As SqlDataReader = MyCommand.ExecuteReader()
While mydr.Read()
DropDownList1.Items.Add(mydr("we2"))
End While
mydr.Close()
MyConnection.Close()
the error message is: No accessible overloaded 'ListItemCollection.Add' can be called without a narrowing conversion
any ideas?
View 5 Replies
View Related
Nov 4, 2004
Hey gurus!
I've got a stored proc I call to obtain user info from a table. I've used the same format (but is slightly different) for another stored proc which works. But this one gives me "Error: System.Data.SqlClient.SqlException: Syntax error converting the varchar value 'SELECT User_UserName AS Username, User_LastLogin AS LastLogin, User_DateCreated AS Created FROM Community_Users WITH(nolock) WHERE (User_CommunityID = '' to a column of data type int. at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) at System.Data.SqlClient.SqlCommand.ExecuteReader() at ASP.Recent_Logins_ascx.BindGrid() at ASP.Recent_Logins_ascx.Page_Load(Object Src, EventArgs e) :"
Why is this stored proc converting my SQLStatement to type int???
Here's my stored proc:
CREATE PROCEDURE Community_RecentLogins
(
@CommunityID int,
@SortField nvarchar(75)
)
AS
-- Create a variable @SQLStatement
DECLARE @SQLStatement varchar(255)
SELECT @SQLStatement = 'SELECT User_UserName AS Username, User_LastLogin AS LastLogin, User_DateCreated AS Created FROM Community_Users WITH(nolock) WHERE (User_CommunityID = ''' + @CommunityID + ''') ORDER BY ' + @SortField
-- Execute the SQL statement
EXEC(@SQLStatement)
And here's the (relevant) code in the ascx:
Sub BindGrid()
Dim conPortal as SqlConnection = New SqlConnection(CommunityGlobals.ConnectionString)
Dim cmdGet as SqlCommand = new SqlCommand("Community_RecentLogins", conPortal)
cmdGet.CommandType = CommandType.StoredProcedure
cmdGet.Parameters.Add("@CommunityID", CommunityGlobals.CommunityID)
If SortField = String.Empty Then
cmdGet.Parameters.Add("@SortField", "User_LastLogin DESC")
Else
cmdGet.Parameters.Add("@SortField", SortField & " DESC")
End If
conPortal.Open()
Dim dr as SqlDataReader = cmdGet.ExecuteReader()
LoginGrid.DataSource = dr
LoginGrid.DataBind()
conPortal.Close()
End Sub
Property SortField() As String
Get
Dim o As Object = ViewState("SortField")
If o Is Nothing Then
Return String.Empty
End If
Return CStr(o)
End Get
Set(ByVal Value As String)
ViewState("SortField") = Value
End Set
End Property
Any help is appreciated! Thanks in advance...
View 2 Replies
View Related
Dec 6, 2006
I am trying to have the aggregate value 1 replaced by 'only one' in my results.
My data looks like this:
Customer_id (int) ; Salesperson_id
1 ; 1
2 ; 1
3 ; 1
4 ; 2
5 ; 2
6 ; 3
...this is the solution I tried:
select
case when count(convert(varchar(15), customer_id)) = 1
Then 'only one customer'
else count(convert(varchar(15), kund_id)) end as no_of_customers,
salesperson_id
from customerdb
group by
salesperson_id
...and this is the result I was hoping for
no_of_customers ; salesperson_id
3 ; 1
2; 2
only one customer;3
...but SQL server only returns: Conversion failed when converting the varchar value ...
View 1 Replies
View Related
Feb 25, 2004
How should I convert a binary/hexadecimal value to Varchar
Thanks in Advance,
Jake
View 1 Replies
View Related
Apr 3, 2008
Haiii..
I save number in database as varchar, but i want to call it back and
convert as double/int. Can it be done
View 13 Replies
View Related
May 26, 2008
Hi guys,
My query was working fine until I added the red writing below.
Basically what I'm doing with this is getting the lowest open purchase order number (docnum) and its matching docduedate.
Now when I run it I get
Conversion failed when converting the varchar value 'po_num' to data type int.
Any ideas? Thanks!
SELECT t3.product ,
t7.itemname ,
t2.u_vlgx_plc,
t3.shorted ,
t4.onhand ,
MIN(
CASE
WHEN t8.linestatus = 'O'
THEN t9.docnum
ELSE NULL
END) po_num,
t10.docduedate
FROM
(SELECT t0.product product ,
SUM(
CASE
WHEN t0.qty_topick <> t0.qty_picked
THEN t0.qty_topick - t0.qty_picked
ELSE 0
END) shorted
FROM rbeacon.dbo.shipline2 t0
INNER JOIN rbeacon.dbo.shiphist t1
ON t0.packslip = t1.packslip
WHERE t1.date_upld = CONVERT(VARCHAR(10), GETDATE()-1, 101)
GROUP BY t0.product
) t3
INNER JOIN comparison.dbo.vlgxplc t2
ON t2.itemcode = t3.product COLLATE Latin1_General_CI_AS
LEFT JOIN
(SELECT t0.product AS product,
SUM(t0.quantity) AS onhand
FROM rbeacon.dbo.binlocat t0
GROUP BY t0.product
) t4
ON t3.product = t4.product
INNER JOIN wbau.dbo.oitm t5
ON t3.product = t5.itemcode COLLATE SQL_Latin1_General_CP850_CI_AS
LEFT JOIN wbau.dbo.ocrd t6
ON t5.cardcode = t6.cardcode
INNER JOIN wbau.dbo.oitm t7
ON t3.product = t7.itemcode COLLATE SQL_Latin1_General_CP850_CI_AS
LEFT JOIN wbau.dbo.por1 t8
ON t3.product = t8.itemcode COLLATE SQL_Latin1_General_CP850_CI_AS
LEFT JOIN wbau.dbo.opor t9
ON t8.docentry = t9.docentry
LEFT JOIN comparison.dbo.podatetest('po_num') t10
ON t10.docnum = t9.docnum
WHERE t3.shorted <> 0
GROUP BY t3.product ,
t7.itemname ,
t2.u_vlgx_plc,
t3.shorted ,
t4.onhand ,
t6.cardname,
t10.docduedate
ORDER BY t2.u_vlgx_plc,
t6.cardname ,
t3.product
View 9 Replies
View Related
Jun 13, 2008
Hello,
I have table1
ID PATH
1 .166.184.185.186.
Need get data from table2 using PATH data from table1
select * from table2 where id in (
select Substring(replace(path,'.',','),2,Len(path)-2) from table1)
ERROR:
Syntax error converting the varchar value '166,184,185,186' to a column of data type int.
Any solution please.
Regard,
M.Z.
View 2 Replies
View Related
Oct 31, 2005
Not sure how I can convert varchar to numeric values in SQL Server? Trying to do so in Design and get error:
Unable to modify table.
ODBC error: [Microsoft][ODBC Server Driver][SQL Server] Error converting data type varchar to numeric.
Any suggestions?
View 5 Replies
View Related
