Converting Timestamp To Varchar Or Concatenating It With A String
Sep 20, 2007
Hello,
I apologise if this question has been asked before but I have searched forums and the web and have not found a solution. I am current creating a script that has a cursor that builds a sql statement to be executed e.g.
-- CHECK RESULT AND STATUS
-- IF OK LOG IN META_BATCH ELSE LOG ERROR' AS SQL
FROM Document
The problem I am having is trying to join the timestamp column into the sql string. I have tried to cast the time stamp to a varchar but I end up with the following output for the timestamp column values
which would not allow my delete script to work correctly. So I would really appreciate some advice to a pointer to where I might find out how to convert the timestamp.
I am populating oracle source in Sql Server Destination. after few rows it fails it displays this error:
[OLE DB Destination [16]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80004005 Description: "Invalid date format".
I used this script component using the following code in between the adapters, However after 9,500 rows it failed again giving the same above error:
To convert Oracle timestamp to Sql Server timestamp
If Row.CALCULATEDETADATECUST_IsNull = False Then
If IsDate(DateSerial(Row.CALCULATEDETADATECUST.Year, Row.CALCULATEDETADATECUST.Month, Row.CALCULATEDETADATECUST.Day)) Then
dt = Row.CALCULATEDETADATECUST
Row.CALCULATEDETADATECUSTD = dt
End If
End If
I don't know if my code is right . Please inform, how i can achieve this.
I have a table on two different servers, the only difference that I can see is that on server A columns first (varchar 32) and last (varchar 32) have ANSI_PADDING set ON and on server B those columns are OFF. No idea why this is true: I didn't specify that the table be set up this way and they both followed similar creation/upgrade paths. I execute "select last+first from <table>" on server A and the result looks like: <last1> <first1> <last2> <first2> ... On server B I get <last1><first1> <last2><first2> Now the docs say ANSI_PADDING has nothing to do with this behavior; in fact if I copy the data on server B to 2 new columns with ANSI_PADDING ON I get the same results. But that's the *only* thing that was different in syscolumns. What is causing the different output behaviors on these two servers? Thanks.
My ERP software stores all dates as integers. So originally, I wrote a T-SQL function to convert these integer dates to normal people dates in the query I use as the recordset for my report. Well...that worked fine on 1,000 rows, but NOT for 100,000. So I've figured out that if I convert my normal person date parameter to an integer date, then SQL only has to convert my 1 parameter instead of having to convert 100,000 fields, (actually, 300,000 because I have 3 date columns).
So my question is, what is the best way to do this? This is what I have so far:
SET @Macola = Cast(Datepart(yy,@MacolaDate) as varchar) + Cast(Datepart(mm,@MacolaDate) as varchar) + Cast(Datepart(dd,@MacolaDate) as varchar)
However, I want the leading zeros for the month and day. For example if I enter '1/1/2004' into this function, it returns 200411, but I need it to return 20040101.
Any suggestions would be greatly apprectiated. Thank you.
I am trying to write a select statement which will concatenate all values of a string column and provide me with a result set containing just one row of data containing a concatenation of all values.
For eg:
column1 abc def hij klm nop
is it possible to write a select statement which would return
Hey guys, I 'm coding my very first stored procedure as accessed by a .NET application. My input parameter is a dynamically built string. I need to concatenate to a sql query within the SP. I've tried using '+' as the concat. character but it doesn't work.
set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go
ALTER procedure [dbo].[tmptable_query] (@condition_cl varchar(100)) as select * from temp_table + @condition_cl
I am trying to create a job that, as one of its steps, will kick off aDTS package. As part of the command parameter, I need to concat asystem variable (@@SERVERNAME) to a constant string. I am receiving anerror about incorrect syntax near the +.Here is the code for the job step.-- Add the job stepsEXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep@job_id = @JobID,@step_id = 1,@step_name = N'Import OCC Series Data',@command = N'DTSRun /FD:DatabasesScriptsDTSImportOCCSeriesData.dts /A DbName:8=' +@@SERVERNAME,@database_name = N'',@server = N'',@database_user_name = N'',@subsystem = N'CmdExec',@cmdexec_success_code = 0,@flags = 2,@retry_attempts = 0,@retry_interval = 1,@output_file_name = N'',@on_success_step_id = 0,@on_success_action = 3,@on_fail_step_id = 0,@on_fail_action = 3IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackIf I just try SELECT N'DTSRun /FD:DatabasesScriptsDTSImportOCCSeriesData.dts /A DbName:8=' +@@SERVERNAME, everything works fine. I even tried declaring a localvariable named @command and setting it in the select statement, but nodice.
I'm trying to write a stored procedure in SQL that will make a copy of a file and then append the current date to the end of the copy's filename. I'm storing the date in a variable called @currentdate which is defined as:
I have a customer who has recently migrated their SQL server to a newserver. In doing so, a portion of a stored procedure has stoppedworking. The code snippet is below:declare @Prefixes varchar(8000),declare @StationID int-- ...select @Prefixes = ''select @Prefixes = @Prefixes + Prefix + '|||'from Devicewhere Station_ID = @StationIDEssentially, we are trying to triple-pipe delimit all the deviceprefixes located at a specified station. This code has workedflawlessly for the last 10 months, but when the database was restoredon the new server, @Prefixes only contains the prefix for the lastdevice.Is there a server, database, or connection option that permits this towork that I am not aware of? Why would this work on the old server andnot on the new? (BTW - both servers are running SQL 2000 StandardSP4).Thanks!
I have a RowVersion/Timestamp column in my table. I check against the RowVersion/Timestamp column when updating rows to check if a row has been updated by another user. I would like to call RAISERROR with a custom message which indicates the current RowVersion/Timestamp of the row in question.
What data type do I convert the RowVersion/Timestamp column into so I can make it part of the custom message which will be a string?
If I convert the RowVersion/Timestamp column into a bigint column, will the max value of RowVersion/Timestamp ever exceed the max value of a bigint? For example, convert(bigint, MyRowVersionColumn)
If the max value of the RowVersion/Timestamp column can exceed the max value for a bigint column, can I convert the RowVersion/Timestamp into a hex string?
Basically RowVersion/Timestamp columns are binary(8) or varbinary(8) so the question could also be answered if it is known if binary(8) can exceed the max value of bigint or if binary(8) can be converted into a hex string
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?
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?
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.
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
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.
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!
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 ?
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.
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."
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....
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
