convert dates: below

Showing posts with label below. Show all posts

Tuesday, March 20, 2012

Convert VB.NET to TSQL PROC & Reference a Proc from another Proc

Howdy,
ISSUE 1: See issue 2 below. I have a distance calculator on my site which wo
rks great. However, the users need to sort by distance, which make sense. I'
m not sure how to do it other than like this. With the returning query inclu
de the distance from origin. Here's my dilemma, I have the script working gr
eat in VB which provides the distance, but that is not sortable, but when I
port it over to TSQL I get differing results. Here is the code in VB:
x = (Math.Sin(DegToRads(_Lat1)) * Math.Sin(DegToRads(_Lat2)) + Math.Cos(DegT
oRads(_Lat1)) * Math.Cos(DegToRads(_Lat2)) * Math.Cos(Math.Abs((DegToRads(_L
ong2)) - (DegToRads(_Long1)))))
x = Math.Atan((Math.Sqrt(1 - x ^ 2)) / x)
x = 60.0 * ((x / Math.PI) * 180) * 1.1507794480235425
return x
Function DegToRads(ByVal Deg)
DegToRads = CDbl(Deg * Math.PI / 180)
End Function
As you can see, nice and simple. Here is how I ported it over to TSQL
CREATE PROCEDURE [dbo].[cp_FindDistance]
@.FromLat as decimal(38,18),
@.FromLong as decimal(38,18),
@.ToLat as decimal(38,18),
@.ToLong as decimal(38,18)
AS
DECLARE @.X as decimal(38,20)
DECLARE @.PI as decimal(38,20)
SET @.PI = 3.14159265358979323846
SET @.X = (Sin(CAST((@.FromLat * @.PI / 180) as int)) * Sin(CAST((@.ToLat * @.PI
/ 180) as int)) + Cos(CAST((@.FromLat * @.PI / 180) as int)) * Cos(CAST((@.ToLa
t * @.PI / 180) as int)) * Cos(Abs(CAST((@.ToLong * @.PI / 180) as int)) - (CAS
T((@.FromLong * @.PI / 180) as int))))
SET @.X = Atan((Sqrt(1 - SQUARE(@.X))) / @.X)
SET @.X = (1.852 * 60.0 * ((@.X / @.PI) * 180))
SET @.X = @.X / 1.609344
SELECT @.X as Miles
The VB is returning accurate miles while the TSQL is returning some number w
ay out of reach, for example when entering cp_FindDistance 41.63,-87.73,41.7
,-88.07, the PROC returns -4516.23854688618468000000 while the VB script ret
urns 15.81.
ISSUE 2: Once I get this proc working, how do I get it into the proc that is
returning the recordset of locations? i.e. select *, cp_GetDistance(fromlat
,fromlong,places.lat,places.long) as distance from places.
Thanks a ton!!!
David LozziDavid,
My math skills are not as good as yours, however, my SQL skills are strong.
I played with your logic some, to attempt to help you out, but, when you lo
ok at it, you'll probably find my math error right away. You got 15 miles,
and I'm getting 18 ...
this is probably a rounding error somewhere that you'll be able to find.
I started from your VB code, instead of trying to use the SQL code. I looke
d at the SQL code and recognized issues, so I started over from the VB Code.
When you find my rounding error, I'd appreciate a response
to ctruett3 at gmail.
hope this was helpful. As to the second portion of the post, try creating a
function (Like below) instead of a stored procedure, this will allow you to
use it in-line like:
/*
Select Top 1
dbo.fnuFindDistance(41.63, -87.73, 41.7, -88.07) Distance
, name
From
master.dbo.sysobjects
*/
--spuFindDistance 41.63,-87.73,41.7,-88.07
--Your answer = 15.81
Create Procedure
dbo.spuFindDistance
(
@.FromLat float
, @.FromLong float
, @.ToLat float
, @.ToLong float
)
As
Declare @.Miles float
Select @.Miles = Sin(dbo.fnuDegToRads(@.FromLat))
* Sin(dbo.fnuDegToRads(@.ToLat))
+ Cos(dbo.fnuDegToRads(@.FromLat))
* Cos(dbo.fnuDegToRads(@.ToLat))
* Cos(Abs(dbo.fnuDegToRads(@.ToLong) - dbo.fnuDegToRads(@.FromLong)))
Select @.Miles = Atan(Sqrt(1 - Power(@.Miles, 2)) / @.Miles)
Select @.Miles = (60.0 * ((@.Miles / PI()) * 180) * 1.1507794480235425)
Select @.Miles [Distance]
Go
Create Function
dbo.fnuDegToRads
(
@.Deg float
)
Returns float
As
Begin
Declare @.RetVal float
Select @.RetVal = Cast(@.Deg * Pi() / 180 as float)
Return @.RetVal
End
Go
****************************************
******************************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET
resources...|||
Tim Heap
Software & Database Manager
POSTAR Ltd
www.postar.co.uk
tim@.postar.co.uk
*** Sent via Developersdex http://www.examnotes.net ***

Sunday, March 11, 2012

Convert to date

I am using SQL Server 2005.
I have a table as below:
LCode varchar(5)
PeriodFrom varchar(8)
PeriodTo varchar(8)
The data stored in this table looks like this:
E1 01-Jan 30-Jun
E2 01-Jul 31-Dec
How to query to check whether a date is lying between Period of E1 or
E2. For example, if I have a date like 03-Mar, I want to search in the
above table to know in which period it is falling.
I have taken PeriodFrom and PeriodTo as varchar.RP -
Is there any particular reason on why you can not make the PeriodFrom and
PeriodTo a DateTime Field. I think it might be easy if you can make it a
DateTime field.
If it is not possible, one way to do this that I can think of immediately is
that concat an arbitrary year to the PeriodFrom and PeriodTo field and then
convert it to the date field, an example of the query would be:
DECLARE @.Check AS VARCHAR(25)
DECLARE @.Year AS VARCHAR(8)
SET @.Check = '03-Mar'
SET @.Year = '2000'
SET @.Check = @.Check + '-' + @.Year
SELECT LCode
FROM dbo.Test
WHERE @.Check >= CAST(PeriodFrom + '-' + @.Year AS DATETIME) AND
@.Check <= CAST(PeriodTo + '-' + @.Year AS DATETIME)
Hope that works
Lucas
"RP" wrote:
> I am using SQL Server 2005.
> I have a table as below:
> LCode varchar(5)
> PeriodFrom varchar(8)
> PeriodTo varchar(8)
> The data stored in this table looks like this:
> E1 01-Jan 30-Jun
> E2 01-Jul 31-Dec
> How to query to check whether a date is lying between Period of E1 or
> E2. For example, if I have a date like 03-Mar, I want to search in the
> above table to know in which period it is falling.
> I have taken PeriodFrom and PeriodTo as varchar.
>|||I don't want the year part. Only date-month need to be stored and
that's why I used varchar for Data field.
On Sep 5, 2:48 am, Lucas Kartawidjaja
<LucasKartawidj...@.discussions.microsoft.com> wrote:
> RP -
> Is there any particular reason on why you can not make the PeriodFrom and
> PeriodTo a DateTime Field. I think it might be easy if you can make it a
> DateTime field.
> If it is not possible, one way to do this that I can think of immediately is
> that concat an arbitrary year to the PeriodFrom and PeriodTo field and then
> convert it to the date field, an example of the query would be:
> DECLARE @.Check AS VARCHAR(25)
> DECLARE @.Year AS VARCHAR(8)
> SET @.Check = '03-Mar'
> SET @.Year = '2000'
> SET @.Check = @.Check + '-' + @.Year
> SELECT LCode
> FROM dbo.Test
> WHERE @.Check >= CAST(PeriodFrom + '-' + @.Year AS DATETIME) AND
> @.Check <= CAST(PeriodTo + '-' + @.Year AS DATETIME)
> Hope that works
> Lucas

Wednesday, March 7, 2012

convert rtf to plain text

Hi All,
can anybody please tell me how to use below sql function.I have a
requirement to convert rtf formatted string as plain text from sql
table.I found below sql function in google search. but I dont no how
to use this.what is 'RICHTEXT.RichtextCtrl' ?
CREATE function dbo.RTF2Text(@.in varchar(8000)) RETURNS varchar(8000)
AS
BEGIN
DECLARE @.object int
DECLARE @.hr int
DECLARE @.out varchar(8000)
-- Create an object that points to the S
-- QL Server
EXEC @.hr = sp_OACreate 'RICHTEXT.RichtextCtrl', @.object OUT
EXEC @.hr = sp_OASetProperty @.object, 'TextRTF', @.in
EXEC @.hr = sp_OAGetProperty @.object, 'Text', @.out OUT
EXEC @.hr = sp_OADestroy @.object
RETURN @.out
END
GO
All i need is to retrive this rtf formatted string as plain text.
Any help is appreciated!
ThanksThat is a COM object of some kind that needs to be registered on the server.
I was able to call the object in VBScript, but it sounds like something that
may need to be installed with Word. My guess is that you will be better off
doing this conversion from an application that understands RTF -> text
conversion rather after it retrieves the SQL data, than to call this
external process from within SQL Server...
A
"munnyAnu" <dkanumolu@.gmail.com> wrote in message
news:1188320430.440778.81540@.g4g2000hsf.googlegroups.com...
> Hi All,
> can anybody please tell me how to use below sql function.I have a
> requirement to convert rtf formatted string as plain text from sql
> table.I found below sql function in google search. but I dont no how
> to use this.what is 'RICHTEXT.RichtextCtrl' ?
>
> CREATE function dbo.RTF2Text(@.in varchar(8000)) RETURNS varchar(8000)
> AS
> BEGIN
> DECLARE @.object int
> DECLARE @.hr int
> DECLARE @.out varchar(8000)
> -- Create an object that points to the S
> -- QL Server
> EXEC @.hr = sp_OACreate 'RICHTEXT.RichtextCtrl', @.object OUT
> EXEC @.hr = sp_OASetProperty @.object, 'TextRTF', @.in
> EXEC @.hr = sp_OAGetProperty @.object, 'Text', @.out OUT
> EXEC @.hr = sp_OADestroy @.object
> RETURN @.out
> END
> GO
>
> All i need is to retrive this rtf formatted string as plain text.
> Any help is appreciated!
>
> Thanks
>

Convert Rows into Columns... (Cross tab).

Hi genius,

I got the result set as shown below (By executing another query i got this).

Month Status Count
===== ====== =====
April I 129
April O 4689
April S 6
July I 131
July O 4838
July S 8
June I 131
June O 4837
June S 8
May I 131
May O 4761
May S 7

But, I need the same result set as below

Month I O S
===== = = =
April 129 4689 6
July 131 4838 8
June 131 4837 8
May 131 4761 7

Can anyone provide me the tips/solution.

Thanks in advance

Regards,
j_jst

You can try "Pivot transformation". Book online has examples.|||

You can also select the cells, copy, shift F10, select paste and transpose. The selected rows will copy in columnar form.

Good Luck

|||

Hi,

I also have a same problem. Can you suggest what was your solution?

regards

Josh

|||

I also need to do something similar...

It was suggested that I would probably need to use a cursor in a stored procedure which loops through the rows and updates a temporary table with the values I need.

I haven’t got round to doing this yet… so if anybody has a solution which I could have a look at I’d be very grateful.

I’ll post my solution when I’ve got it.

|||

Jon:

What version of SQL Server are you using? Also, a cursor is NOT normally a good idea. Take a look at some of these posts:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1372104&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=892822&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=447559&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=437891&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=588762&SiteID=1

Please open a new thread and post what you are trying to accomplish.

|||

select [Month],
max(case when [Status]='I' then [Count] end) as I,
max(case when [Status]='O' then [Count] end) as O,
max(case when [Status]='S' then [Count] end) as S
from mytable
group by [Month]
order by [Month]

|||Mark's response is why I want you to post a new thread. Sorry, Mark. Should I split this thread?|||

Thanks for the links kent.

I have managed to solve my problem.

Convert Rows into Columns... (Cross tab).

Hi genius,

I got the result set as shown below (By executing another query i got this).

Month Status Count
===== ====== =====
April I 129
April O 4689
April S 6
July I 131
July O 4838
July S 8
June I 131
June O 4837
June S 8
May I 131
May O 4761
May S 7

But, I need the same result set as below

Month I O S
===== = = =
April 129 4689 6
July 131 4838 8
June 131 4837 8
May 131 4761 7

Can anyone provide me the tips/solution.

Thanks in advance

Regards,
j_jst

You can try "Pivot transformation". Book online has examples.|||

You can also select the cells, copy, shift F10, select paste and transpose. The selected rows will copy in columnar form.

Good Luck

|||

Hi,

I also have a same problem. Can you suggest what was your solution?

regards

Josh

|||

I also need to do something similar...

It was suggested that I would probably need to use a cursor in a stored procedure which loops through the rows and updates a temporary table with the values I need.

I haven’t got round to doing this yet… so if anybody has a solution which I could have a look at I’d be very grateful.

I’ll post my solution when I’ve got it.

|||

Jon:

What version of SQL Server are you using? Also, a cursor is NOT normally a good idea. Take a look at some of these posts:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1372104&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=892822&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=447559&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=437891&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=588762&SiteID=1

Please open a new thread and post what you are trying to accomplish.

|||

|||Mark's response is why I want you to post a new thread. Sorry, Mark. Should I split this thread?|||

Thanks for the links kent.

I have managed to solve my problem.

Convert Rows into Columns... (Cross tab).

Hi genius,

I got the result set as shown below (By executing another query i got this).

Month Status Count
===== ====== =====
April I 129
April O 4689
April S 6
July I 131
July O 4838
July S 8
June I 131
June O 4837
June S 8
May I 131
May O 4761
May S 7

But, I need the same result set as below

Month I O S
===== = = =
April 129 4689 6
July 131 4838 8
June 131 4837 8
May 131 4761 7

Can anyone provide me the tips/solution.

Thanks in advance

Regards,
j_jst

You can try "Pivot transformation". Book online has examples.|||

You can also select the cells, copy, shift F10, select paste and transpose. The selected rows will copy in columnar form.

Good Luck

|||

Hi,

I also have a same problem. Can you suggest what was your solution?

regards

Josh

|||

I also need to do something similar...

It was suggested that I would probably need to use a cursor in a stored procedure which loops through the rows and updates a temporary table with the values I need.

I haven’t got round to doing this yet… so if anybody has a solution which I could have a look at I’d be very grateful.

I’ll post my solution when I’ve got it.

|||

Jon:

What version of SQL Server are you using? Also, a cursor is NOT normally a good idea. Take a look at some of these posts:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1372104&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=892822&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=447559&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=437891&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=588762&SiteID=1

Please open a new thread and post what you are trying to accomplish.

|||

|||Mark's response is why I want you to post a new thread. Sorry, Mark. Should I split this thread?|||

Thanks for the links kent.

I have managed to solve my problem.

Convert row data into Column [Didnt get the exact result......]

Hi members

I got the result set as shown below (By executing another query i got this).

Month Status Count
===== ====== =====
April I 129
April O 4689
April S 6
July I 131
July O 4838
July S 8
June I 131
June O 4837
June S 8
May I 131
May O 4761
May S 7

But, I need the same result set as below

Month I O S
===== = = =
April 129 4689 6
July 131 4838 8
June 131 4837 8
May 131 4761 7

Can anyone provide me the tips/solution.

Select Month
SUM(CASE WHEN Status = 'I' THEN Count END) AS I,
SUM(CASE WHEN Status = 'O' THEN Count END) AS O,
SUM(CASE WHEN Status = 'S' THEN Count END) AS S
FROM SomeTable
GROUP BY MONTH

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

|||

Thanks but this is not the required query. I know anout that but i require the row data will group as a column. I dont wana specify column name.

Please reply

|||If you're using SQL Server 2005 you can use the PIVOT command, but you still have to specify the columns to be listed in the IN clause.|||

You will have to use dynamic SQL to do this without specifying column names. It isn't too awful hard, you just have to use the values you want to pivot on to build the second bits:

Select Month
SUM(CASE WHEN Status = 'I' THEN Count END) AS I,
SUM(CASE WHEN Status = 'O' THEN Count END) AS O,
SUM(CASE WHEN Status = 'S' THEN Count END) AS S
FROM SomeTable
GROUP BY MONTH

create table someTable
(
month char(3),
status char(1),
count int
)

insert into someTable (month, status, count)
select 'jan','I',10
union all
select 'jan','O',12
union all
select 'jan','S',22
union all
select 'feb','I',10
union all
select 'apr','O',12
union all
select 'apr','S',22
go
Select Month,
SUM(CASE WHEN Status = 'I' THEN Count END) AS I,
SUM(CASE WHEN Status = 'O' THEN Count END) AS O,
SUM(CASE WHEN Status = 'S' THEN Count END) AS S
FROM SomeTable
GROUP BY MONTH
go

In 2005, you can do something like:

declare @.query varchar(8000)
select @.query = 'select month ' + (
SELECT distinct
',SUM(CASE WHEN Status = ''' + status + ''' THEN Count END) AS [' +
status + ']' AS [text()]
FROM
sometable s
FOR XML PATH('') ) + ' from SomeTable group by month'

select @.query
exec (@.query)

or in 2000, you can use a slightly less favorable solution:

declare @.query varchar(8000)
select @.query = ''
select @.query = @.query + ',SUM(CASE WHEN Status = ''' + status + ''' THEN Count END) AS [' + status + ']'
from (select distinct status from someTable) as someTable

select @.query = 'select month ' + @.query + ' from SomeTable group by month'
exec (@.query)

Can't put distinct in the @.query bit because it gives wierd results. The @.query = @.query +
thing has some known issues when sorting is required

For more on why the 2005 solution is best: http://www.aspfaq.com/show.asp?id=2529

|||

I am not getting the out put i require , Again you are using hardcoding stuff. I know the Data in table but , as i cant specify them..... Becuase they are also coming from different table. Please help me. i am stucked.....

The query sould be running on sql and oracle both

|||

What you then could do is to query for a table as the following

Month January February March (...)
=======================================
O 2 10 100
-
I 15 22 32
-
S 100 45 3
-

an afterwards pivoting it with the appropiate function mentioned. This works cause there won′t be more than 12 month as far as someone invents one :-)

But the thing that you have to both support Oracle and SQL Server for the query won′t work with this special functions, because although TSQL and PLSQL have some similar syntax components they differ in specialized ones.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||Nope, this is not desire result, plz help me out.................|||I know that this is not the result as you need it, that is where the PIVOT command would take care of, but unless you don′t have any operator like that in Oracle (can′t remember if there is any operator which can do this) you can′t do crossuse this query.

-Jens Suessmeyer.|||help me out , by the result require , either for SQL or PL SQL.........|||

Don't know if you have gotten your answer as of yet, but if not, post some example tables, with create statements, some insert statements for the data, what your exact requirements are and perhaps your "dream" syntax that would make you happy (we'll try to match it) and the exact output.

No guarantees, but it will make sure we are all on the same page...

|||

Louis Davidson wrote:

You will have to use dynamic SQL to do this without specifying column names. It isn't too awful hard, you just have to use the values you want to pivot on to build the second bits:
Select Month
SUM(CASE WHEN Status = 'I' THEN Count END) AS I,
SUM(CASE WHEN Status = 'O' THEN Count END) AS O,
SUM(CASE WHEN Status = 'S' THEN Count END) AS S
FROM SomeTable
GROUP BY MONTH
create table someTable
(
month char(3),
status char(1),
count int
)
insert into someTable (month, status, count)
select 'jan','I',10
union all
select 'jan','O',12
union all
select 'jan','S',22
union all
select 'feb','I',10
union all
select 'apr','O',12
union all
select 'apr','S',22
go
Select Month,
SUM(CASE WHEN Status = 'I' THEN Count END) AS I,
SUM(CASE WHEN Status = 'O' THEN Count END) AS O,
SUM(CASE WHEN Status = 'S' THEN Count END) AS S
FROM SomeTable
GROUP BY MONTH
go
In 2005, you can do something like:

declare @.query varchar(8000)
select @.query = 'select month ' + (
SELECT distinct
',SUM(CASE WHEN Status = ''' + status + ''' THEN Count END) AS [' +
status + ']' AS [text()]
FROM
sometable s
FOR XML PATH('') ) + ' from SomeTable group by month'
select @.query
exec (@.query)
or in 2000, you can use a slightly less favorable solution:
declare @.query varchar(8000)
select @.query = ''
select @.query = @.query + ',SUM(CASE WHEN Status = ''' + status + ''' THEN Count END) AS [' + status + ']'
from (select distinct status from someTable) as someTable
select @.query = 'select month ' + @.query + ' from SomeTable group by month'
exec (@.query)
Can't put distinct in the @.query bit because it gives wierd results. The @.query = @.query +
thing has some known issues when sorting is required
For more on why the 2005 solution is best: http://www.aspfaq.com/show.asp?id=2529

This is the correct answer = Cross Tab Query

You want to use CASE WHEN ... THEN ... ELSE ... END AS []

Adamus

|||

I guess, the threader hate the hardcoding stuff like "I","S",or so, cause there might be other unposted status value.

Here I post one dynamic SQL aiming to resolve the uncertainty of status value.

Regards.

--create table
create table Raw_Table
(
RecID bigint identity(1,1) not null,
RecDate smalldatetime not null,
Status varchar(1) not null,
Constraint PK_Raw_Table primary key (RecID)
)
--insert data, Repeat following T-Sql with different 'Status' Value for several times
declare @.d datetime
set @.d='2006-07-10'
declare @.mtimes int
set @.mtimes=0
while(@.mtimes<453)
begin
insert into Raw_Table(RecDate,Status)
values(@.d,'K')
set @.mtimes=@.mtimes+1
end
--Aggregate Raw_Table
select datename(month,recdate) as [month],Status,count(*)
as [count]
into ##tempresult
from Raw_Table
group by datename(month,recdate),Status
order by datename(month,recdate)

--Dynamic SQL
declare @.Dsql nvarchar(4000)
declare @.VLastStatus varchar(1)
declare @.VNextStatus varchar(1)

--declare cursor
declare MyCurs Cursor for
select distinct Status from ##tempresult

open MyCurs
fetch next from MyCurs into @.VNextStatus
set @.VLastStatus=''
while @.@.Fetch_Status=0
begin
--this if makes the initial comb Result
if not exists (select * from tempdb.dbo.sysobjects where [name]='##TempResult_Comb_')
begin
set @.Dsql = 'select distinct month into ##TempResult_Comb_ from ##TempResult'
exec sp_executeSql @.Dsql
end
--this @.dsql makes each status table
set @.Dsql=
'select Month ,status,count into ##TempResult_'
+ @.VNextStatus
+' from ##tempresult where Status='
+''''+@.VNextStatus+''''
exec sp_executeSql @.Dsql

--this dsql combined each status table and last comb table
set @.Dsql= 'select a.*,b.count as '+@.VNextStatus+' into ##tempResult_Comb_'+
@.VNextStatus+' from ##TempResult_Comb_'+@.VLastStatus+' as a full join ##TempResult_'+@.VNextStatus+' as b
on a.month=b.month'
exec sp_executeSql @.Dsql

set @.VLastStatus=@.VNextStatus
fetch next from MyCurs into @.VNextStatus
end
close MyCurs
Deallocate MyCurs
--this @.Dsql Select Final Result
set @.Dsql='select * from ##tempResult_Comb_'+@.VLastStatus
exec sp_executeSql @.Dsql

Saturday, February 25, 2012

Convert row data into Column

Hi members

I got the result set as shown below (By executing another query i got this).

Month Status Count
===== ====== =====
April I 129
April O 4689
April S 6
July I 131
July O 4838
July S 8
June I 131
June O 4837
June S 8
May I 131
May O 4761
May S 7

But, I need the same result set as below

Month I O S
===== = = =
April 129 4689 6
July 131 4838 8
June 131 4837 8
May 131 4761 7

Can anyone provide me the tips/solution.

Select Month
SUM(CASE WHEN Status = 'I' THEN Count END) AS I,
SUM(CASE WHEN Status = 'O' THEN Count END) AS O,
SUM(CASE WHEN Status = 'S' THEN Count END) AS S
FROM SomeTable
GROUP BY MONTH

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

|||

Thanks but this is not the required query. I know anout that but i require the row data will group as a column. I dont wana specify column name.

Please reply

|||If you're using SQL Server 2005 you can use the PIVOT command, but you still have to specify the columns to be listed in the IN clause.|||

You will have to use dynamic SQL to do this without specifying column names. It isn't too awful hard, you just have to use the values you want to pivot on to build the second bits:

Select Month
SUM(CASE WHEN Status = 'I' THEN Count END) AS I,
SUM(CASE WHEN Status = 'O' THEN Count END) AS O,
SUM(CASE WHEN Status = 'S' THEN Count END) AS S
FROM SomeTable
GROUP BY MONTH

create table someTable
(
month char(3),
status char(1),
count int
)

In 2005, you can do something like:

select @.query
exec (@.query)

or in 2000, you can use a slightly less favorable solution:

select @.query = 'select month ' + @.query + ' from SomeTable group by month'
exec (@.query)

Can't put distinct in the @.query bit because it gives wierd results. The @.query = @.query +
thing has some known issues when sorting is required

For more on why the 2005 solution is best: http://www.aspfaq.com/show.asp?id=2529

|||

The query sould be running on sql and oracle both

|||

What you then could do is to query for a table as the following

Month January February March (...)
=======================================
O 2 10 100
-
I 15 22 32
-
S 100 45 3
-

an afterwards pivoting it with the appropiate function mentioned. This works cause there won′t be more than 12 month as far as someone invents one :-)

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

-Jens Suessmeyer.|||help me out , by the result require , either for SQL or PL SQL.........|||

No guarantees, but it will make sure we are all on the same page...

|||

Louis Davidson wrote:

You will have to use dynamic SQL to do this without specifying column names. It isn't too awful hard, you just have to use the values you want to pivot on to build the second bits:
Select Month
SUM(CASE WHEN Status = 'I' THEN Count END) AS I,
SUM(CASE WHEN Status = 'O' THEN Count END) AS O,
SUM(CASE WHEN Status = 'S' THEN Count END) AS S
FROM SomeTable
GROUP BY MONTH
create table someTable
(
month char(3),
status char(1),
count int
)
insert into someTable (month, status, count)
select 'jan','I',10
union all
select 'jan','O',12
union all
select 'jan','S',22
union all
select 'feb','I',10
union all
select 'apr','O',12
union all
select 'apr','S',22
go
Select Month,
SUM(CASE WHEN Status = 'I' THEN Count END) AS I,
SUM(CASE WHEN Status = 'O' THEN Count END) AS O,
SUM(CASE WHEN Status = 'S' THEN Count END) AS S
FROM SomeTable
GROUP BY MONTH
go
In 2005, you can do something like:

declare @.query varchar(8000)
select @.query = 'select month ' + (
SELECT distinct
',SUM(CASE WHEN Status = ''' + status + ''' THEN Count END) AS [' +
status + ']' AS [text()]
FROM
sometable s
FOR XML PATH('') ) + ' from SomeTable group by month'
select @.query
exec (@.query)
or in 2000, you can use a slightly less favorable solution:
declare @.query varchar(8000)
select @.query = ''
select @.query = @.query + ',SUM(CASE WHEN Status = ''' + status + ''' THEN Count END) AS [' + status + ']'
from (select distinct status from someTable) as someTable
select @.query = 'select month ' + @.query + ' from SomeTable group by month'
exec (@.query)
Can't put distinct in the @.query bit because it gives wierd results. The @.query = @.query +
thing has some known issues when sorting is required
For more on why the 2005 solution is best: http://www.aspfaq.com/show.asp?id=2529

This is the correct answer = Cross Tab Query

You want to use CASE WHEN ... THEN ... ELSE ... END AS []

Adamus

|||

I guess, the threader hate the hardcoding stuff like "I","S",or so, cause there might be other unposted status value.

Here I post one dynamic SQL aiming to resolve the uncertainty of status value.

Regards.

--Dynamic SQL
declare @.Dsql nvarchar(4000)
declare @.VLastStatus varchar(1)
declare @.VNextStatus varchar(1)

--declare cursor
declare MyCurs Cursor for
select distinct Status from ##tempresult

Convert row data into Column

Hi members

I got the result set as shown below (By executing another query i got this).

Month Status Count
===== ====== =====
April I 129
April O 4689
April S 6
July I 131
July O 4838
July S 8
June I 131
June O 4837
June S 8
May I 131
May O 4761
May S 7

But, I need the same result set as below

Month I O S
===== = = =
April 129 4689 6
July 131 4838 8
June 131 4837 8
May 131 4761 7

Can anyone provide me the tips/solution.

Select Month
SUM(CASE WHEN Status = 'I' THEN Count END) AS I,
SUM(CASE WHEN Status = 'O' THEN Count END) AS O,
SUM(CASE WHEN Status = 'S' THEN Count END) AS S
FROM SomeTable
GROUP BY MONTH

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

|||

Thanks but this is not the required query. I know anout that but i require the row data will group as a column. I dont wana specify column name.

Please reply

|||If you're using SQL Server 2005 you can use the PIVOT command, but you still have to specify the columns to be listed in the IN clause.|||

You will have to use dynamic SQL to do this without specifying column names. It isn't too awful hard, you just have to use the values you want to pivot on to build the second bits:

Select Month
SUM(CASE WHEN Status = 'I' THEN Count END) AS I,
SUM(CASE WHEN Status = 'O' THEN Count END) AS O,
SUM(CASE WHEN Status = 'S' THEN Count END) AS S
FROM SomeTable
GROUP BY MONTH

create table someTable
(
month char(3),
status char(1),
count int
)

In 2005, you can do something like:

select @.query
exec (@.query)

or in 2000, you can use a slightly less favorable solution:

select @.query = 'select month ' + @.query + ' from SomeTable group by month'
exec (@.query)

Can't put distinct in the @.query bit because it gives wierd results. The @.query = @.query +
thing has some known issues when sorting is required

For more on why the 2005 solution is best: http://www.aspfaq.com/show.asp?id=2529

|||

The query sould be running on sql and oracle both

|||

What you then could do is to query for a table as the following

Month January February March (...)
=======================================
O 2 10 100
-
I 15 22 32
-
S 100 45 3
-

an afterwards pivoting it with the appropiate function mentioned. This works cause there won′t be more than 12 month as far as someone invents one :-)

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

-Jens Suessmeyer.|||help me out , by the result require , either for SQL or PL SQL.........|||

No guarantees, but it will make sure we are all on the same page...

|||

Louis Davidson wrote:

You will have to use dynamic SQL to do this without specifying column names. It isn't too awful hard, you just have to use the values you want to pivot on to build the second bits:

Select Month
SUM(CASE WHEN Status = 'I' THEN Count END) AS I,
SUM(CASE WHEN Status = 'O' THEN Count END) AS O,
SUM(CASE WHEN Status = 'S' THEN Count END) AS S
FROM SomeTable
GROUP BY MONTH

create table someTable
(
month char(3),
status char(1),
count int
)

insert into someTable (month, status, count)
select 'jan','I',10
union all
select 'jan','O',12
union all
select 'jan','S',22
union all
select 'feb','I',10
union all
select 'apr','O',12
union all
select 'apr','S',22
go
Select Month,
SUM(CASE WHEN Status = 'I' THEN Count END) AS I,
SUM(CASE WHEN Status = 'O' THEN Count END) AS O,
SUM(CASE WHEN Status = 'S' THEN Count END) AS S
FROM SomeTable
GROUP BY MONTH
go

In 2005, you can do something like:

declare @.query varchar(8000)
select @.query = 'select month ' + (
SELECT distinct
',SUM(CASE WHEN Status = ''' + status + ''' THEN Count END) AS [' +
status + ']' AS [text()]
FROM
sometable s
FOR XML PATH('') ) + ' from SomeTable group by month'

select @.query
exec (@.query)

or in 2000, you can use a slightly less favorable solution:

declare @.query varchar(8000)
select @.query = ''
select @.query = @.query + ',SUM(CASE WHEN Status = ''' + status + ''' THEN Count END) AS [' + status + ']'
from (select distinct status from someTable) as someTable

select @.query = 'select month ' + @.query + ' from SomeTable group by month'
exec (@.query)

Can't put distinct in the @.query bit because it gives wierd results. The @.query = @.query +
thing has some known issues when sorting is required

For more on why the 2005 solution is best: http://www.aspfaq.com/show.asp?id=2529

This is the correct answer = Cross Tab Query

You want to use CASE WHEN ... THEN ... ELSE ... END AS []

Adamus

|||

I guess, the threader hate the hardcoding stuff like "I","S",or so, cause there might be other unposted status value.

Here I post one dynamic SQL aiming to resolve the uncertainty of status value.

Regards.

--Dynamic SQL
declare @.Dsql nvarchar(4000)
declare @.VLastStatus varchar(1)
declare @.VNextStatus varchar(1)

--declare cursor
declare MyCurs Cursor for
select distinct Status from ##tempresult

Convert real to Varchar.

Hello All,

I am facing problem to get exacltly value using convert function. Please follow below steps.

Crete table A (ID Real)

Insert into A values(0.0000013)

Select * from A

------
1.3 e-05

I am getting '1.3 e-05' values because datatype is real so that is why I am getting but want to get '0.0000013' values. Please let me know what have to that for that.

Please let me know asap.

Regards,
M Jain
NJ,USAYou can convert "REAL" into "NUMERIC" first and then
convert to varchar.

like,

select convert(varchar, convert(NUMERIC(30,20), id)) from A

Convert real to Varchar.

Hello All,

I am facing problem to get exacltly value using convert function. Please follow below steps.

Crete table A (ID Real)

Insert into A values(0.0013)

select convert(varchar, convert(DECIMAL(30,6), id)) from A

------
0.001300

I am getting '0.001300' values because scale is 6 in Decimal so that is why I am getting but want to get '0.0013' values. Please let me knowHEY NJ!

isn't it "Jersey"?

Doesn't this work?

USE Northwind
GO

CREATE TABLE A ([ID] Real)
GO

INSERT INTO A ([ID]) values(0.0013)

SELECT CONVERT(varchar(45), [ID]) FROM A
GO
DROP TABLE A
GO|||Hello All,

I am facing problem to get exacltly value using convert function. Please follow below steps.

Crete table A (ID Real)

Insert into A values(0.000013)

select * from a

Ouput
------
1.13 e05

But I want '0.000013' to display so that is why I have written below Query.

select convert(varchar, convert(DECIMAL(30,6), id)) from A

------
0.00001300

I am getting '0.001300' values because scale is 6 in Decimal so that is why I am getting but want to get '0.0013' values. Please let me know

-- Is any function using that I can remove added/padded zero suffix.

Current Expected result.
0.00001300 to 0.000013|||Did you try what I gave you?|||You use 2 different numbers:

0.0013 and 0.000013 - which one is it ?

If it is the first then just change the decimal from 6 to 4.|||Does the precision matter if you're going to varchar?

And I still want to know

What exit?

Friday, February 24, 2012

Convert Money to Char

I have a sql query listed below, I would like to count the number of values
that the first 5 characters are '80438' from iNum field.
Please help me correct the sql query listed below.
Thank You,
SELECT count(iNum) from Call_Movement
where substring(cast(iNum as money) as char(20),1,5) like '80438'
iNum Money Format
iNum Data In Call_Movement Table
803482000146220.0000
803482000147143.0000
803482000153805.0000You are very close. You're mistake is mostly with the "like."
Select count(iNum)
from Call_Movement
where left(cast(iNum as varchar(30)),5) = '80348'
I do not know your data as well as you, but I can see a day when there
will be more than twenty characters available and this code failing.
Joe K. wrote:
> I have a sql query listed below, I would like to count the number of value
s
> that the first 5 characters are '80438' from iNum field.
> Please help me correct the sql query listed below.
> Thank You,
> SELECT count(iNum) from Call_Movement
> where substring(cast(iNum as money) as char(20),1,5) like '80438'
> iNum Money Format
> iNum Data In Call_Movement Table
> 803482000146220.0000
> 803482000147143.0000
> 803482000153805.0000|||Dear joe,
instead of cast and substring, u can use convert()
e.g.,
SELECT count(iNum) from Call_Movement
where convert(varchar(50), iNum,0) like '80438%'
Thanks & Regards
Ravi
"Joe K." wrote:

> I have a sql query listed below, I would like to count the number of value
s
> that the first 5 characters are '80438' from iNum field.
> Please help me correct the sql query listed below.
> Thank You,
> SELECT count(iNum) from Call_Movement
> where substring(cast(iNum as money) as char(20),1,5) like '80438'
> iNum Money Format
> iNum Data In Call_Movement Table
> 803482000146220.0000
> 803482000147143.0000
> 803482000153805.0000
>
>

Convert money columns to update?

Hi Guys

I need your help again, I am try to update several columns and the data type is 'money'.

Below is the code I have used:

UPDATE CAT_Products
SET

UnitCost ='10.00',
UnitCost2 = '10.00',
UnitCost3 = '10.00',
UnitCost4 = '10.00',
UnitCost5 = '10.00',
UnitCost6 = '10.00'

WHERE ProductCode = '0008'

But it will not update, instead I get this error:

-----------------------------------------------------------

>[Error] Script lines: 1-9 --------
Disallowed implicit conversion from data type varchar to data type money, table 'dbo.CAT_Products', column 'UnitCost'. Use the CONVERT function to run this query.

More exceptions ... Disallowed implicit conversion from data type varchar to data type money, table '.dbo.CAT_Products', column 'UnitCost2'. Use the CONVERT function to run this query.

-----------------------------------------------------------

The error message indicates that I need to use the convert function. But the columns data type is set at 'money' not 'varcher' . So do I need to convert data type to 'varcher' in order to update and convert back to data type 'money' when update complete? Or do I need to indicate in the update statement that data type is already 'money'? I am not sure how I would either.

Thanks

try:

UPDATE CAT_Products
SET

UnitCost =convert(money,'10.00'),
UnitCost2 = convert(money,'10.00'),
UnitCost3 = convert(money,'10.00'),
UnitCost4 =convert(money,'10.00'),
UnitCost5 = convert(money,'10.00'),
UnitCost6 = convert(money,'10.00')

WHERE ProductCode = '0008'

|||

Hi jpazgier

Thank you for your reply and code.

I am pleased to say that your code worked first time.

A great help.

Cheers.

CONVERT Money Char(20)

I have a sql query listed below that I would like to only output values like
'6024', the Bonus values are converted to char(20) from Call_Movement table.
SELECT cast(cast(Bonus as money) as char(20)) like = '6024' from
Call_Movement
where DATEDIFF(mi, Start_Time, GETDATE()) <=60
Please help me complete this task.Joe K. wrote:
> I have a sql query listed below that I would like to only output values like
> '6024', the Bonus values are converted to char(20) from Call_Movement table.
>
> SELECT cast(cast(Bonus as money) as char(20)) like = '6024' from
> Call_Movement
> where DATEDIFF(mi, Start_Time, GETDATE()) <=60
> Please help me complete this task.
Please post with table structure and simple data and desired output.
if you are not using wildcard then no need to use like in the query
just use =.
Also you can not put = after like and you have to put like operator in
where clause.
Regards
Amish Shah

CONVERT Money Char(20)

I have a sql query listed below that I would like to only output values like
'6024', the Bonus values are converted to char(20) from Call_Movement table.
SELECT cast(cast(Bonus as money) as char(20)) like = '6024' from
Call_Movement
where DATEDIFF(mi, Start_Time, GETDATE()) <=60
Please help me complete this task.Joe K. wrote:

> I have a sql query listed below that I would like to only output values li
ke
> '6024', the Bonus values are converted to char(20) from Call_Movement tabl
e.
>
> SELECT cast(cast(Bonus as money) as char(20)) like = '6024' from
> Call_Movement
> where DATEDIFF(mi, Start_Time, GETDATE()) <=60
> Please help me complete this task.
Please post with table structure and simple data and desired output.
if you are not using wildcard then no need to use like in the query
just use =.
Also you can not put = after like and you have to put like operator in
where clause.
Regards
Amish Shah

Sunday, February 19, 2012

convert integer to string

How can I convert an integer to string?

When I use convert function to convert an integer to varchar as below, I get incorrect value of @.EmployeeID as '1'. In the Employee table, EmployeeID is of type int.

I want to pass @.EmployeeID and @.NewID as string to another stored proc which accepts @.EmployeeID and @.NewID as TEXT parameters.

Declare @.RowID INT
DECLARE @.EmployeeID VARCHAR
DECLARE @.NewID VARCHAR

SELECT @.EmployeeID = CONVERT(varchar, EmployeeID)
FROM dbo.Employee
WHERE Row = @.RowID

...

EXEC calculateSalary @.EmployeeID, @.NewID

My guess would be that your EmployeeID starts with a "1", see you're declaring EmployeeID to be a varchar of length 1, so it's only storing the first character of the EmployeeID, for example:

declare @.employeeid varchar
select @.employeeid = convert(varchar, 54321)
select @.employeeid

returns "5" where:

declare @.employeeid varchar(5)
select @.employeeid = convert(varchar, 54321)
select @.employeeid

returns the whole integer.|||

You might wish to add a size parameter to a varchar() datatype. Using just [ varchar ] defaults to a single character -truncating the rest if the number is greater than 9.

Your SELECT statement, using convert() 'should' be functioning ok. I would prefer using CAST( EmployeeID as varchar(5) ).

|||Note that varchar defaults to one character in a declare statement, but 30 in a cast/convert:

declare @.i int
set @.i = 1234567890
select cast(@.i as varchar)

And

select cast('12345678901234567890123456789012345678901234567890' as varchar)

Horrible, horrible thing |||

Converting from NUMERIC data type to string never truncate the value it will produce * symbol on your output.

Code Snippet

Select Convert(varchar(2),99) A, cast(99as varchar(2)) B

A B

- -

99 99

Select Convert(varchar(2),100) A, cast(100 as varchar(2)) B
A B
- -
* *

|||

Manivannan.D.Sekaran wrote:

Converting from NUMERIC data type to string never truncate the value it will produce * symbol on your output.

Very true, however that is not the problem here. try running the code:

Code Snippet

DECLARE @.ShortChar as varchar

DECLARE @.LongChar as varchar(10)

SET @.ShortChar = CONVERT(varchar, 200)

SET @.LongChar = CONVERT(varchar, 200)

SELECT @.ShortChar, @.LongChar

The result is: 2 200

The problem is not that the convert is truncating the value but that the assignment is silently truncating the value at the length of the variable (1 character). It is trying to put 3 characters in but can only fit the first one and so "loses" the rest.

Sunday, February 12, 2012

convert dynamically generated parameters list into stored proc

I have the following ASP code that builds part of the example SQL statement below (it's the same SQL as in my earlier thread here (http://www.dbforums.com/showthread.php?t=1214044) but a very different question):

if sFindTicketEventId > 0 then sSQL = sSQL & " AND [tblEvents].[id]=" & sFindTicketEventId
if sFindTicketStandId > 0 then sSQL = sSQL & " AND [tblStands].[id]=" & sFindTicketStandId

SELECT
[tblC].[id] AS CombinationID,
[tblC].[availability],
[tblC].[description],
[tblC].[price] AS combinationPrice,
[tblC].[combination_open],
[tblT].[TicketID] AS TicketID,
[tblT].[price] AS ticketPrice,
[tblT].[availability],
[tblT].[ticket_open],
[tblT].[quantity],
[tblT].[event_name],
[tblT].[event_open],
[tblT].[stand_name],
[tblT].[stand_open],
[tblT].[admission_start_date],
[tblT].[admission_end_date],
[tblT].[date_open],
[tblT].,
[tblT].,
[tblT2].[description],
[tblT2].[admin_description]
FROM(
SELECT
[tblCombinations].[id],
[tblTickets].[id] As TicketID, [tblTickets].[price], [tblTickets].[availability], [tblTickets].[ticket_open],
[tblCombinations_Tickets].[quantity],
[tblEvents].[event_name],
[tblEvents].[event_open],
[tblStands].[stand_name],
[tblStands].[stand_open],
[tblAdmissionDates].[admission_start_date],
[tblAdmissionDates].[admission_end_date],
[tblAdmissionDates].[date_open],
[tblBookingDates].[booking_start_date],
[tblBookingDates].[booking_end_date]
FROM [tblCombinations]
LEFT JOIN [tblCombinations_Tickets] ON [tblCombinations_Tickets].[combination_id] = [tblCombinations].[id]
LEFT JOIN [tblTickets] ON [tblCombinations_Tickets].[ticket_id] = [tblTickets].[id]
LEFT JOIN [tblEvents] ON [tblEvents].[id] = [tblTickets].[event_id]
LEFT JOIN [tblStands] ON [tblStands].[id] = [tblTickets].[stand_id]
LEFT JOIN [tblAdmissionDates] ON [tblAdmissionDates].[id] = [tblTickets].[admission_date_id]
LEFT JOIN [tblBookingDates] ON [tblBookingDates].[id] = [tblTickets].[booking_date_id]
LEFT JOIN [tblTicketConcessions] ON [tblTicketConcessions].[id] = [tblTickets].[ticket_concession_id]
LEFT JOIN [tblBookingQuantities] AS [tblBookingMinQuantities] ON [tblBookingMinQuantities].[id] = [tblTickets].[booking_min_quantity_id]
LEFT JOIN [tblBookingQuantities] AS [tblBookingMaxQuantities] ON [tblBookingMaxQuantities].[id] = [tblTickets].[booking_max_quantity_id]
LEFT JOIN [tblMemberships] ON [tblMemberships].[id] = [tblTickets].[membership_id]
WHERE 1=1
[B]AND [tblEvents].[id]=2
[B]AND [tblStands].[id]=3
--AND [tblAdmissionDates].[id]=@.admissionDateId
--AND [tblBookingDates].[id]=@.bookingDateId
--AND [tblTicketConcessions].[id]=@.concessionId
--AND [tblBookingMinQuantities].[id]=@.bookingMinQuantityId
--AND [tblBookingMaxQuantities].[id]=@.bookingMaxQuantityId
--AND [tblMemberships].[id]=@.membershipId
GROUP BY
[tblCombinations].[id],
[tblTickets].[id],
[tblTickets].[price], [tblTickets].[availability], [tblTickets].[ticket_open],
[tblCombinations_Tickets].[quantity],
[tblEvents].[event_name],
[tblEvents].[event_open],
[tblStands].[stand_name],
[tblStands].[stand_open],
[tblAdmissionDates].[admission_start_date],
[tblAdmissionDates].[admission_end_date],
[tblAdmissionDates].[date_open],
[tblBookingDates].[booking_start_date],
[tblBookingDates].[booking_end_date]
) as [tblT]
JOIN [tblCombinations] as [tblC] on [tblT].[id]=[tblC].[id]
LEFT JOIN [tblTickets] as [tblT2] on [tblT].[TicketID]=[tblT2].[id]

I want to turn this SQL into a stored proc; there are currently about 8 parameters that I want to pass into it. The field value for each will be either NULL or a positive integer, and the paramater will be passed in as an integer.

If the passed parameter value is a positive integer then it should return all records where the corresponding field value matches that integer. If the passed parameter is 0, it should return all rows regardless of whether the field value is an integer or NULL.

And I can't for the life of me figure out how to do it. Do I need an IF statement in there or something?

:confused:Hi
A common method is:
WHERE (MyField = @.MyParam OR @.MyParam = 0)
I read an article somewhere though that poohed poohed this as the optimiser can't use the index or something though.|||Well, seeing as my state of blissful ignorance safely censored your "optimiser" comment, I can happily report that the solution works great :) Thanks.

Convert datetime different results

I'm new to handling dates in T-SQL having mainly used the more forgiving Access.

I am trying to extract the date from a 40 chr field example below - dates are dd/mm/yy

Input By Angelab On 13/08/06 19:55:33

using

SELECT CONVERT(datetime, SUBSTRING(A.Comments,22,8),3) AS EntryDate
FROM
(SELECT CommentDetails.Comments, InvoiceDetails.TransRef FROM (Transactions INNER JOIN CommentDetails ON Transactions.TransRef = CommentDetails.TransRef) INNER JOIN InvoiceDetails ON Transactions.TransRef = InvoiceDetails.TransRef WHERE ((Transactions.TransDate Between DATEADD(day,-5, '26-Dec-2004' ) And DATEADD(day,5, '01-Jan-2005' )) AND (SUBSTRING(CommentDetails.Comments,1,8)='Input by'))) AS A

WHERE (CONVERT(datetime, SUBSTRING(A.Comments,22,8),3) Between '26-Dec-2004' And '01-Jan-2005' )

Although without the final WHERE it works fine, so the data is OK, with the final WHERE I get "out of range datetime value", how can it be valid in the SELECT statement but not the WHERE statement.

Our SQL server is set to american dates mm/dd/yyyy.

rgds

Peter

If you don't have just millions of rows, you could do something along these lines to find any values that won't work. You could just change the first block to a variable assignment if you don't want to have lots of output. 2005 is easier because of TRY...CATCH. For 2000 you could do something similar, but just with a dynamic SQL statement:

set nocount on
drop table test
go
create table test
(
comments varchar(40)
)
go
insert into test
select 'Input By Angelab On 13/08/06 19:55:33'
union all
select 'Input By Angelab On 13/09/06 19:55:33'
union all
select 'Input By Angelab On 13/13/06 19:55:33'
go
declare @.cursor cursor, @.comments varchar(40)
set @.cursor = cursor for (select comments from test)
open @.cursor

while (1=1)
begin
fetch next from @.cursor into @.comments
if @.@.fetch_status <> 0
break
begin try
select CONVERT(datetime, SUBSTRING(@.Comments,22,8),3)
end try
begin catch
select @.comments as bad_value
end catch
end

Output:

--
2006-08-13 00:00:00.000

--
2006-09-13 00:00:00.000

bad_value
-
Input By Angelab On 13/13/06 19:55:33

|||

Never use BETWEEN with dates. Timestamps will kill you. It incorporates the time value as well. You will get inaccurate results.

Always use (date >= Today or date <=Tomorrow)

...and you're on the right track with the CONVERT function

Adamus

|||

History

The data has been downloaded into our archive SQL database, I'm extracting data from this 40 chrs text field that on the original machine was only informational and not "useable data".

For the CONVERT function I'm only extracting the date portion so all time values are 0.

CONVERT(datetime, SUBSTRING(A.Comments,22,8),3) works perfectly well in the SELECT statement so the data is valid

where it doesn't work is

WHERE (CONVERT(datetime, SUBSTRING(A.Comments,22,8),3) Between '26-Dec-2004' And '01-Jan-2005' )

Having tried many things I have a work around avoiding CONVERT by generating an unambigous date string.

My conclusion is that is that SUBSTRING(A.Comments,22,8) which returns 'dd/mm/yy' is implicitly and wrongly being converted into American date BEFORE it is being passed to CONVERT.. some of the time!!

The SAME function on the same data works in the SELECT but not in the WHERE.

I suspect the BETWEEN date AND date is forcing the implicit conversion early.

rgds

peter

|||

>>Having tried many things I have a work around avoiding CONVERT by generating an unambigous date string.<<

That was a good idea. I really wish you could have found a value that failed though. It would be very interesting to figure out what the actual problem was (though probably not as much to you as it would be to me at this point :) One suggestion I would have made would be to put the substring into the derived table so you only had to do this convert once, but that wouldn't have worked if the data was still bad.

Good luck

Friday, February 10, 2012

Convert date and time to string?

Hi,

Can anyone tell me how to convert date and time to string.

I got one field inside my database which contain data like below:

4/16/2004 10:19:01 AM

if i wan to call out a record which have to refer to the data above

Select *
From table_A
where field_date= ???

what to fill in the ??

ThanxMan, you were so close! Use:Select *
FROM table_A
WHERE field_date = '4/16/2004 10:19:01 AM'-PatP|||Originally posted by Pat Phelan
Man, you were so close! Use:Select *
FROM table_A
WHERE field_date = '4/16/2004 10:19:01 AM'-PatP

hi there again....

I tired like what you hav told me but i cant get any output from the query statement lo.its like the record doesn't exist.

its like the format i keyed in is not the same.

Can u pls explain .
thanx|||My guess would be that you are missing the milliseconds. The value in the database probably has milliseconds, but your constant does not.

Databases are finicky about things like that! ;)

-PatP

Tuesday, March 20, 2012

Convert VB.NET to TSQL PROC & Reference a Proc from another Proc

Sunday, March 11, 2012

Convert to date

Wednesday, March 7, 2012

convert rtf to plain text

Convert Rows into Columns... (Cross tab).

Convert Rows into Columns... (Cross tab).

Convert Rows into Columns... (Cross tab).

Convert row data into Column [Didnt get the exact result......]

Saturday, February 25, 2012

Convert row data into Column

Convert row data into Column

Convert real to Varchar.

Convert real to Varchar.

Friday, February 24, 2012

Convert Money to Char

Convert money columns to update?

CONVERT Money Char(20)

CONVERT Money Char(20)

Sunday, February 19, 2012

convert integer to string

Sunday, February 12, 2012

convert dynamically generated parameters list into stored proc

Convert datetime different results

Friday, February 10, 2012

Convert date and time to string?

convert dates

Blog Archive

About Me