Vinit Patel

Posts Tagged ‘Sql Server

Hi,

If you have the Data in Sql table like

Value

15

17

 

Now, If you want to print the values like $15 and $17. So you will have to do like this…

SELECT LEFT(‘$’ + CONVERT(VARCHAR, CAST(‘15.00’ AS MONEY), 1), LEN(‘$’+ CONVERT(VARCHAR, CAST(‘15.00’ AS MONEY), 1)) – 3)

Tags: ,

Let suppose we have in the notepad file or other type of text file the data is in the tabular format.

Like,

___________________________________________

1             xyz                  abc@info.com

2            abc                   xyz@info.com

___________________________________________

Now, If you want to store the data according to its fields like,

No.               Name                  EMailId

then do the following.

DECLARE @doesExist INT

SET NOCOUNT ON
EXEC xp_fileexist ‘C:\CategoryTable.txt’, @doesExist OUTPUT
SET NOCOUNT OFF
Select @doesExist

The above line is for check that if the file exists or not !!!

If the File exists then it returns the value = 1.

If yes then do the following.

Create Table #Temp
(
[CategoryId] [int],
[UserId] [int] NULL,
[ParentId] [int] NULL,
[CategoryName] [varchar](500),
[Description] [varchar](1000),
[Active] [bit] NULL,
[ImagePath] [varchar](max),
[CreatedDate] [datetime] NULL,
[DeleteStatus] [bit] NULL,
)

IF @doesExist = 1
BEGIN

BULK INSERT #Temp
FROM ‘C:\CategoryTable.txt’
WITH
(
FIELDTERMINATOR =’\t’,
ROWTERMINATOR = ‘\n’,
FIRE_TRIGGERS
)
END

Select * From #Temp

Drop table #Temp

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

ALTER PROCEDURE [dbo].[GetEmpBookIssueDetail]

@DeptExamBookIssueID int,

@FromDate varchar(100)=null,

@ToDate varchar(100)=null

AS

BEGIN

Declare @FDate Datetime

Declare @TDate Datetime

if @FromDate is not null

Begin

Exec GetDateByDate @FromDate=@FromDate,@Date=@FDate output

ENd

if @ToDate is not null

Begin

Exec GetDateByDate @FromDate=@ToDate,@Date=@TDate output

ENd

SELECT dd.DeptExamBookIssueID,dd.EmployeeName,dd.Designation,dd.BookCode,b.BookTitle,b.Price,

Convert(varchar,dd.IssueDate,103) as IssueDate,

Convert(varchar,dd.ReturnDate,103) as ReturnDate

FROM DeptExamBookIssueDetails dd

LEFT JOIN DeptExamPaper de ON de.DeptExamPaperID = dd.DeptExamPaperID

LEFT JOIN Books b ON dd.BookCode = b.BookCode

WHERE dd.SoftStatus = 0

AND dd.DeptExamBookIssueID = @DeptExamBookIssueID

OR

(

(

Convert(char(10),IssueDate,101)>=Convert(char(10),@FDate,101) AND

Convert(char(10),IssueDate,101)<=Convert(char(10),@TDate,101)

)

Or

(

Convert(char(10),ReturnDate,101)>=Convert(char(10),@FDate,101)

AND Convert(char(10),ReturnDate,101)<=Convert(char(10),@TDate,101)

)

)

ORDER BY DeptExamBookIssueID DESC

END


Calendar

July 2017
M T W T F S S
« Jan    
 12
3456789
10111213141516
17181920212223
24252627282930
31  

Stats Of Blog

  • 6,848 hits

Cluster Map