SQL function to convert Julian Date

Dear Reader,

Earlier I have already written a logic to convery Julian to Datetime in C#

today we had a requirment to convert the same in SQL server. So wrote a function for this opetaion

Syntax:

Create FUNCTION dbo.from_julian(@julian char(6)) RETURNS datetime AS

BEGIN

RETURN (select DATEADD(YEAR, @julian / 1000,DATEADD(DAY, @julian % 1000,'18991231')))

END

//TEST Query

--select dbo.from_julian(110269)

Explaination of conversion Logic:

(select DATEADD(YEAR, @julian / 1000,DATEADD(DAY, @julian % 1000,'18991231')))

18991231 is the last day in 19^th century

Step #1:

Select (DATEADD(DAY, @julian % 1000,'18991231'))

Exmaple: Select (DATEADD(DAY, 110269 % 1000,'18991231'))

Output: 1900-09-26 00:00:00.000

Step#2:

(select DATEADD(YEAR, @julian / 1000, Step#1))

(select DATEADD(YEAR, @julian / 1000, 1900-09-26 00:00:00.000))

(select DATEADD(YEAR, 110269 / 1000, 1900-09-26 00:00:00.000))

Select (110269 / 1000)

Output: 110

Adding Year 110 + 1900 = 2010

Result is datetime: 2010-09-26 00:00:00.000

Hope It Helps

4Z ~ Fauzi

SQL Query to delete duplicate records

Hey Guys...
There was a small logical check missed in my automated blog(KuwaitFresh.com). And because of this there occured duplicate records which means duplicate posts! which will be not be accepted by any user :) Here is the query to delete duplicate records in your table...

List Duplicate Rows:

SELECT Field,Count(*)

FROM TABLE

GROUP BY FIELD

HAVING ( COUNT(*) > 1 )

DELETE duplicate Rows:

General Syntax:

DELETE FROM TABLENAME WHERE POSTID in

(SELECT T1.POSTID

from TABLENAME T1, TABLENAME T2

where T1.DUPLICATEFIELD = T2. DUPLICATEFIELD

and T1. UNIQUEFIELD > T2.UNIQUEFIELD)

Example:

DELETE FROM TABLENAME WHERE POSTID in

(SELECT T1.POSTID

from TABLENAME T1, TABLENAME T2

where T1.TITLE = T2.TITLE

and T1.POSTID > T2.POSTID)

Hope It is Helpful :-)

Regards

Fauzi ~ 4Z

How to connect to SQL Server Desktop Engine Remotely

Hi all,

Lesson learnt for the day is when you try to connect to SQL Server Desktop Engine (MSDE) remotely, the scenario where i tried to connect was from SSIS - Sql Server Integration Services. I tried to create a OLEDB connection. When i tried to create the OLEDB connect the same like we use to do for SQL server; the following error message was shown



Solution: When i googled, came to know that we need to give the Server name with instance name when we create a connection.

like: <ServerName>/<InstanceName>
And we need to give the database name if not displayed in drop down list.

Hope It helps

Regrads
Fauzi ~4Z

SQL Server: Linked Server

Hi...

Seems in SQL Server 2005 there is a way to link to other Databases like Oracle,DB2 for extracting data through LinkedServer.

Once you add them as a Linked server, later you can write queries like how you use to write in SQL Server.

Syntax:
EXEC sp_addlinkedserver

server='LINKEDDB2NAME',

@srvproduct='Microsoft OLE DB Provider for DB2',

@catalog='DB2',

@provider='DB2OLEDB',

@provstr='Initial Catalog=PUBS;Data Source=DB2;HostCCSID=1252;Network Address=SOME;Network Port=50000;Package Collection=admin;Default Schema=admin;'

To check:

SELECT * FROM LinkedServer.Catalog.Schema.Table

Related Links:
To know more on Linked Sever : http://msdn.microsoft.com/en-us/library/ms190479.aspx

Defining a DB2 as a linked server in SQL Server 2005: http://blogs.msdn.com/dotnetinterop/archive/2006/01/20/defining-a-db2-as-a-linked-server.aspx

Hope it helps...
Regards
Fauzi

Cannot convert between unicode and non-unicode string data types

Hey,

I was creating a Integration service which needs to fetch data from a POS (Point of Sale) application and migrate to JDE ERP system. The Source is MS Access Database & the destination tables are in JD-Edwards DB2. In between there are vital manipulation logic to be done, which is taken care in MS SQL Server with the help of SSIS. When i was creating OleDB data destination for DB2 there were lot of issues. I am really thankful to this thread in MSDN http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/4128e62f-92d1-4125-baa3-fdd9fed59d48 which solved the connectivety issues. The final data flow there supposed to migrate data from SQL server to DB2. The problem i faced & invested lot of hours was on this error..

"Cannot convert between unicode and non-unicode string data types"

To solve lot of people gave lot of advices in forum http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/21646e0b-d742-467a-8880-255ebf198c46 .
Some people told Data conversion component is helpful ,In my case I am transferring data into Z-files. These are tables F4001Z & F4011Z which have 149 columns & 249 columns each. And doing mapping for each fields is a boring task...

Solution: From the error it is clear that the issue is converting unicode to non-unicode string. So What i did was, I created a Local table in SQL Server with same table structure of F4001Z & F4011Z tables (like F4001Z_local & F4011Z_local). And in Table creation SQL script, where ever there was nchar datatype, I replaced with varchar . Now in the source Data Flow section, the manipulated data will we inserted into these local tables F4001Z_local & F4011Z_local and then a Select query is run through a stored procedure. Doing so in the string is non-unicode string or DT_STR format we can say... The Error got resolved :) The screen shots says the rest, The data conversion component was placed before. I believe if we remove the component also the application would work fine....

Best Regards
Fauzi

SSIS: cannot be processed because more than one code page (1256 and 1252) are specified for it

Hi,

In My SSIS, I got the following error when I changed the Data type through Data Conversion component.

Error:
Data Flow Task: The column "" cannot be processed because more than one code page (1256 and 1252) are specified for it. (SQL Server Import and Export Wizard)

When we searched the forums, most common answer found was to set AlwaysUseDefaultCodePage=true in Properties.

When I did the same it worked for me

Regards

Fauzi

SQL: Georgian date to Julian date

Hi...

Here is the function to convert Georgian date to Julian date. Today it was useful when I was writing a application to transfer data to ERP system.

Syntax:

CREATE FUNCTION dbo.to_julian(@date datetime) RETURNS char(6) AS

BEGIN

RETURN (SELECT '1'+ RIGHT(CAST(YEAR(@date) AS CHAR(4)),2) + RIGHT('000' + CAST(DATEPART(dy, @date) AS varchar(3)),3))

END

Execute:

SELECT dbo.to_julian(getdate())

The output is 109217 for today(2009-08-05) Where 1-> 21st century, 09 -> Current Year & 217 -> Is the number of days since the start of this year.

Regards

Fauzi

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

Hi...

Came across this error today when i run a simple query in query analyzer...

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

It was when i gave a test date with dd/mm/yyyy format. Seems we need to give in mm/dd/yyyy format... now it works fine....

people discussed on the same here

correct query is:

(SELECT
TEMP.Sre,
TEMP.Tket,
TEMP.RDate,
TEMP.SKU,
TEMP.Qty,
TEMP.Esion,
SAM.AverageCost*TEMP.Qty AS Expr2
FROM
TEMP INNER JOIN SAM ON (TEMP.SKU = SAM.SKU) AND (TEMP.Store = SAM.Sre)
WHERE
TEMP.RDate >= '07/13/2009'
And TEMP.RDate <= '07/19/2009'
) ORDER BY TEMP.Sre, TEMP.Tket

Regards
Fauzi

Sample to use XML in Stored Procedure

Hi,

Here is a sample to use XML string as a input to a Stored procedure. The stored procedure uses SP sp_xml_preparedocument. Which is capable of reading a XML string. It parses the XML and provides a parsed document which could be used like a temporary table in run time.

Following is the simple code:

1. SQL Script to Create Table:

/****** Object: Table [dbo].[Fund_Index_Relationship] Script Date: 05/24/2009 12:21:20 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[Fund_Index_Relationship](

[ID] [int] IDENTITY(1,1) NOT NULL,

[Fund_ID] [int] NULL,

[FundIndex_ID] [int] NOT NULL,

CONSTRAINT [PK_Fund_Index_Relationship] PRIMARY KEY CLUSTERED

(

[ID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

2. Stored Procedure:

-- =============================================

-- Author: Fauzi

-- Create date: 180808

-- Description: TO update Relationship between Fund & Index

-- =============================================

CREATE PROCEDURE [dbo].[XML_SP_Sample]

@xmlString TEXT,

@Fund_ID int,

@Output VARCHAR(1000) OUTPUT

AS

BEGIN

DECLARE @idoc INT

EXEC sp_xml_preparedocument @idoc OUTPUT, @xmlString

-- Insert Section

INSERT into [Fund_Index_Relationship]

(

Fund_ID,

FundIndex_ID

)

SELECT

Fund_ID,

FundIndex_ID

FROM OPENXML (@idoc, '/Items/Item',1)

WITH (

Fund_ID int './Fund_ID',

FundIndex_ID int './FundIndex_ID'

)

END

3. XML string to Test SP:

[XML_SP_Sample] '<Items><Item><Fund_ID>34</Fund_ID><FundIndex_ID>2</FundIndex_ID></Item><Item><Fund_ID>34</Fund_ID><FundIndex_ID>3</FundIndex_ID></Item><Item><Fund_ID>34</Fund_ID><FundIndex_ID>4</FundIndex_ID></Item><Item><Fund_ID>34</Fund_ID><FundIndex_ID>5</FundIndex_ID></Item></Items>'

Hope it Helps :)

Regards

Fauzi