Convert DATE type

Advertisement
November 30

CreateFUNCTION [dbo].[dates](@strDate
NVARCHAR(10))
RETURNS Date
AS
BEGIN
DECLARE @strInputDate   
VARCHAR(10)
DECLARE @strOutPutDate  Date
DECLARE @strLen         
INT 
SELECT @strInputDate =
LTRIM(RTRIM(ISNULL(@strDate,
SELECT @strLen =
LEN(LTRIM(RTRIM(@strDate)))
IF CHARINDEX('0000', @strInputDate)
> 0 OR
CHARINDEX('*', @strInputDate)
> 0
BEGIN
SET @strOutPutDate
= ' '
END
ELSE
BEGIN
SELECT @strOutPutDate
= CONVERT(Date,
CAST(STUFF(STUFF(RIGHT('0'
+ @strInputDate, @strLen), 3, 0,
'-'), 6, 0,
AS DATE), 1)
END
RETURN ISNULL(@strOutPutDate,
END
SELECT [dbo].[dates](101213)
Result is: 2013-10-12
But i want the result to be as 10-12-13. What I am missing here

Advertisement

Replay

DECLARE
@dt VARCHAR(20)='101213'
SELECT
STUFF(STUFF(@dt,3,0,'-'),6,0,'-')
Best Regards,Uri Dimant SQL Server MVP,
http://sqlblog.com/blogs/uri_dimant/
MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting:
Large scale of database and data cleansing
Remote DBA Services:
Improves MS SQL Database Performance
SQL Server Integration Services:
Business Intelligence

View 4 Replies

Saving result from sp_executesql into a variable and using dynamic column name - getting error "Error converting data type varchar to numeric"

October 11

Im getting an error when running a procedure that includes this code. I need to select from a dynamic column name and save the result in a variable, but seem to be having trouble with the values being fed to sp_executesql DECLARE @retval AS DECIMAL(1

View All Replies

Problem with converting data types

November 30

Hi Experts, I am stuck up with a little problem, i need your help i am converting all the data types to CHAR but i am not getting the expected data out......... My code is as below...where do i go wrong... LOOP AT <T_ITAB> ASSIGNING <FIELD>. D

View All Replies

Error converting data type nvarchar to numeric 'witholding tax' (OWHT)

October 11

Hi Guys. After I have upgraded my clients 2007 PL 30 database to 8.81 PL05, and running a repair to fix inconsistincies, I am receiveing some error when opening any sales documents. It takes place when I open a sales document, or browse previous docs

View All Replies

CF9, no hotfix, VendorErrorCode 8114, cfprocresult error converting data type

November 30

Hello, everyone. I've got an issue that really has me scratching my head.  Not sure where the issue lies, but I'll explain as best I can. Basically, I _think_ I'm being told that one of the fields in the recordset is not being converted. On one page,

View All Replies

No-rounding while converting data type F to Packed

November 30

Hi All, I want to pass value of a variable of data type F to another variable of dtata type P with decimals 3. While converting values decimals are getting rounded to next higher value. Can any one tell me how to retain the actual values,without roun

View All Replies

How to Convert Data types here

November 30

Hi All, My stored proc is as below, create or replace procedure proc_sec_synch ( Begin FOR sec_Update in ( Select SsM_ID , CYCLE_DATE , TARGET_TABLE , COLUMN_NAME , NEW_DATA  , COLUMN_TYPE from PM_OWN.test_synch order by target_table Loop *'Update' |

View All Replies

Error converting data when updating table

November 30

Hi, I need to understand why I cannot gat my update to work properly, I have to concat 4 fields (numbers with leading zeros so I can create a new number with leading 1. My field is already set as a bigint but I cannot convert the resulting string pro

View All Replies

Convert data

November 30

i m trying to use except to match some columns  select [src] from book5 except select [home_phone] from Lead the src column is float, and the home_phone col is char, i m getting this error  Msg 8114, Level 16, State 5, Line 1 Error converting data ty

View All Replies

Problem accessing basic data types

October 11

Hi, I am a newbie at using JNI so please don't mind if I am asking something trivial. I have a JNI wrapper for a native C code. The C code is a Gtk+ application using GLib library. This library has it's own basic data types. For example, "gchar"

View All Replies

CAST Not working for me - Arithmetic overflow error converting int to data type numeric - error

October 11

GPM is DECIMAL(5,2) PRICE is DECIMAL(11,4) COST is DECIMAL(7,2) Trying to update the Gross Profit Margin % field and I keep getting the "Arithmetic overflow error converting int to data type numeric" error. UPDATE SMEMODETAIL SET SMD_GPM = (SMD_

View All Replies

Conversion failed when converting the nvarchar value to data type int when returning nvarchar(max) from stored procedure

November 30

Thank you for your help!!!  I've created a stored procedure to return results as xml.  I'm having trouble figuring out why I'm getting the error message "Conversion failed when converting the nvarchar value '<tr>.....' to data type int.    It s

View All Replies

'DATE' type in SQL converting to a 'STRING' in CR2008.

October 11

Help! We have just installed the stand-alone version of CR2008 and running against a SQL db back end. The newest version of SQL consists of a data type, 'DATE' that's being recognized in CR as a 'STRING' when the stored procedures are accessed. Howev

View All Replies

How to fix "cannot convert between unicode and non-unicode string data types" :/

November 30

Environment: SQL Server 2008 R2 Introduction:Staging_table is a table where data is being stored from source file. Individual and ind_subject_scores are destination tables. Purpose: To load the data from a source file .csv while SSIS define table  fi

View All Replies

Converting premitive data types into strings

November 30

Hello everybody, I have a small doubt. Is it possible to convert all data types like int, boolean etc to strings. I know it is possible to convert them individually. But I would like to have it a common method to convert them all. Because I am readin

View All Replies

Conversion failed when converting the varchar value to data type int error

November 30

Hi, I am working on a report which is off of survey information and I am using dynamic pivot on multiple columns. I have questions like Did you use this parking tag for more than 250 hours? If yes specify number of hours. and the answers could be No,

View All Replies

Convert/Cast string data type from file to DT_GUID into table

November 30

Hi to all is there anyone who help me please?!? I'm not able to resolve the following problem. I have a .csv file with a string field A and its values are GUID. I must export this .csv file into SQL Server Table which have a uniqueidentifier (GUID) f

View All Replies

Function module to convert character type to required data type

November 30

Hi, Is there a function module which can convert data in character type to any other required data type? Like i have a date 12.2.2008 and it is stored as character now i want to convert it into type d. so that when i use Describe field <field_name>

View All Replies

How to convert high_value to date data type on dba_tab_partitions

November 30

I just want to query and get the max date available on the dba_tab_partitions for the high_value field. high_value data type is LONG it has the value for Partition bound value expression. For example on my partition table the expression is like TO_DA

View All Replies

Error message: Conversion failed when converting the varchar value 'G001985' to data type int

November 30

Here is my script: select name_id, physican_id, primary_name, last_name, first_name from dbo.tblphysiciannames where (physician_id = 'G001985') How to convet the varchar va;e tp data type int? Thank you for your help. SallyDo you have varchar column

View All Replies

Tags:

  1. onblur onchange onclick EN APEX ORACLE
  2. input VAT.xml
  3. sap error message fh061
  4. wifi no pasward janva ni app
  5. imovie program for a1151
  6. how to fix error recovery my27
  7. difference in fgi0 and ke30
  8. ke30 restrict company
  9. inch5aa
  10. throughoutrpq
Copyrights 2019 Fcffair BigData Resource, All rights reserved