db time conversion: convert

Hi all,
I created a query in Access that I want to convert to SQL. The query is a
little complicated and I tried to work out the differences between the two
engines in terms of the functions I used. Still I'm getting error messages
regarding the ROUND function. Please Help!!
Create proc st_proc_FASP6M AS
(
SELECT SHR_Case_Name AS CaseName, RTrim(SHR_last_name)+",
"+RTrim(SHR_first_name) AS ChildName, CRS_CID,
((Round((6/1/2005-[CRS_cid])/182.625)+1)*182.625+ [CRS_cid],0) AS
Prelim_Plan_Date, DatePart("d",[CRS_cid]) AS ciddate,
DatePart("d",[prelim_plan_date]) AS pdate, [ciddate]-[pdate] AS difference,
Abs([difference]) AS [absolute],
IIf([absolute]< =3,[prelim_plan_date]+[difference],[prel
im_plan_date]-0) AS
Plan_Date, DateAdd("d",-25,[plan_Date]) AS ACS_Date,
DateAdd("d",-30,[plan_date]) AS Supervisor_DueDate INTO tbl_FaspFinal
FROM tbl_PreventionCases
GROUP BY RTrim([tbl_PreventionCases]![SHR_last_na
me])+",
" +RTrim([tbl_PreventionCases]![SHR_first_
name]), tbl_PreventionCases.CRS_CID
HAVING
(((((Round((6/1/2005-[tbl_PreventionCases]![CRS_cid])/182.625)+1)*182.625+[t
bl_PreventionCases]![CRS_cid]),0) Between 9/30/2005 And 11/30/2005));)
TSI found at least two things you need to change:
IIF.
No such animal in TSQL or ANSI SQL. Use CASE instead.

> Abs([difference]) AS [absolute],
Here you reference a column alias given by the prior column in the SELECT. I
.e., there is no column
named difference in the table, at least I guess so). No can do (as per ANSI
SQL), everything
"happens at once" in SQL.
If you post something we can execute, we might be able to help you further.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"TS" <TS@.discussions.microsoft.com> wrote in message
news:56D23A37-C8CB-4C12-8630-0A22E96864DD@.microsoft.com...
> Hi all,
> I created a query in Access that I want to convert to SQL. The query is a
> little complicated and I tried to work out the differences between the two
> engines in terms of the functions I used. Still I'm getting error messages
> regarding the ROUND function. Please Help!!
> Create proc st_proc_FASP6M AS
> (
> SELECT SHR_Case_Name AS CaseName, RTrim(SHR_last_name)+",
> "+RTrim(SHR_first_name) AS ChildName, CRS_CID,
> ((Round((6/1/2005-[CRS_cid])/182.625)+1)*182.625+ [CRS_cid],0) AS
> Prelim_Plan_Date, DatePart("d",[CRS_cid]) AS ciddate,
> DatePart("d",[prelim_plan_date]) AS pdate, [ciddate]-[pdate] AS difference,
> Abs([difference]) AS [absolute],
> IIf([absolute]< =3,[prelim_plan_date]+[difference],[prel
im_plan_date]-0) AS
> Plan_Date, DateAdd("d",-25,[plan_Date]) AS ACS_Date,
> DateAdd("d",-30,[plan_date]) AS Supervisor_DueDate INTO tbl_FaspFinal
> FROM tbl_PreventionCases
> GROUP BY RTrim([tbl_PreventionCases]![SHR_last_na
me])+",
> " +RTrim([tbl_PreventionCases]![SHR_first_
name]), tbl_PreventionCases.CRS_C
ID
> HAVING
> (((((Round((6/1/2005-[tbl_PreventionCases]![CRS_cid])/182.625)+1)*182.625+
[tbl_PreventionCases]![CRS_cid]),0)
> Between 9/30/2005 And 11/30/2005));)
> --
> TS|||how can I attach something you can excute? I don't see an attach option
--
TS
"Tibor Karaszi" wrote:

> I found at least two things you need to change:
> IIF.
> No such animal in TSQL or ANSI SQL. Use CASE instead.
>
> Here you reference a column alias given by the prior column in the SELECT.
I.e., there is no column
> named difference in the table, at least I guess so). No can do (as per ANS
I SQL), everything
> "happens at once" in SQL.
> If you post something we can execute, we might be able to help you further
.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "TS" <TS@.discussions.microsoft.com> wrote in message
> news:56D23A37-C8CB-4C12-8630-0A22E96864DD@.microsoft.com...
>|||Not attach. Post the CREATE TABLE and INSERT statements we need to re-create
your scenario.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"TS" <TS@.discussions.microsoft.com> wrote in message
news:7A6145D0-7F41-466C-94BA-AC0D056A55F6@.microsoft.com...
> how can I attach something you can excute? I don't see an attach option
> --
> TS
>
> "Tibor Karaszi" wrote:
>|||Here is the create table statement and you have the statement for the query
I
want to run. Thanks a million
CREATE TABLE [dbo].[tbl_PreventionCases] (
[SHR_LAST_NAME] [nvarchar] (22) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SHR_FIRST_NAME] [nvarchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CRS_CASE_NUMBER] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CRS_CID] [smalldatetime] NULL ,
[SHR_CASE_NAME] [nvarchar] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SHR_CIN] [nvarchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SHR_CHILD_DOB] [smalldatetime] NULL ,
[CRS_CASE_CITY] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CRS_CASE_STATE] [nvarchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CRS_CASE_STREET] [nvarchar] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CRS_CASE_ZIP] [nvarchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CHILD_ID] [int] IDENTITY (1, 1) NOT NULL
) ON [PRIMARY]
--
TS
"Tibor Karaszi" wrote:

> Not attach. Post the CREATE TABLE and INSERT statements we need to re-crea
te your scenario.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "TS" <TS@.discussions.microsoft.com> wrote in message
> news:7A6145D0-7F41-466C-94BA-AC0D056A55F6@.microsoft.com...
>|||I found several issues with the query. The basic problem is that Access does
n't follow ANSI SQL very
well, So you need to re-work some part and learn to recognize those parts to
port from Access to SQL
Server. I've did a little bit in your query, but as I didn't know the busine
ss requirements etc, I
can't do it all. Here's the current version (possibly some parenthesis misma
tch):
SELECT
SHR_Case_Name AS CaseName,
RTRIM(SHR_last_name)+", "+RTRIM(SHR_first_name) AS ChildName,
CRS_CID,
ROUND((6/1/2005-[CRS_cid])/182.625)+1) * 182.625 + [CRS_cid],) AS Prelim_Pla
n_Date,
DATEPART("d",[CRS_cid]) AS ciddate,
DATEPART("d",[prelim_plan_date]) AS pdate, [ciddate]-[pdate] AS difference,
ABS([difference]) AS [absolute],
CASE WHEN ABS([difference])<=3 THEN [prelim_plan_date]+[difference] ELSE [prelim_plan_date]-0 END AS
Plan_Date,
DATEADD("d",-25,[plan_Date]) AS ACS_Date,
DATEADD("d",-30,[plan_date]) AS Supervisor_DueDate INTO tbl_FaspFinal
FROM tbl_PreventionCases
GROUP BY RTRIM([tbl_PreventionCases]![SHR_last_na
me])+",
" +RTrim([tbl_PreventionCases]![SHR_first_
name]), tbl_PreventionCases.CRS_CID
HAVING
ROUND( ((((6/1/2005-[tbl_PreventionCases]![CRS_cid])
/182.625)+1)*182.625+[tbl_PreventionCases]![CRS_cid]),0) Between 9/30/2005 A
nd 11/30/2005)
Some comments:
Learn to format the query in a more readable manner. It will help you and ot
hers in the future.
Is 6/1/2005 a date?If so, it should be expressed ad '20050601'. See
http://www.karaszi.com/SQLServer/info_datetime.asp
What is [tbl_PreventionCases]![SHR_last_name]? There's no exclamation mark i
n the SQL language. Is
it a text box in a form? Is it a column in a table? If a column in a table,
then use
tblname.colname. If some GUI element, then you have to pass this in as a par
ameter to SQL Server.
SQL Server cannot access what is on the screen of a client application.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"TS" <TS@.discussions.microsoft.com> wrote in message
news:81B06B42-2C52-4DCA-B4DC-811112FDCE3A@.microsoft.com...
> Here is the create table statement and you have the statement for the quer
y I
> want to run. Thanks a million
> CREATE TABLE [dbo].[tbl_PreventionCases] (
> [SHR_LAST_NAME] [nvarchar] (22) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [SHR_FIRST_NAME] [nvarchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [CRS_CASE_NUMBER] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [CRS_CID] [smalldatetime] NULL ,
> [SHR_CASE_NAME] [nvarchar] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [SHR_CIN] [nvarchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [SHR_CHILD_DOB] [smalldatetime] NULL ,
> [CRS_CASE_CITY] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [CRS_CASE_STATE] [nvarchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [CRS_CASE_STREET] [nvarchar] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [CRS_CASE_ZIP] [nvarchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [CHILD_ID] [int] IDENTITY (1, 1) NOT NULL
> ) ON [PRIMARY]
> --
> TS
>
> "Tibor Karaszi" wrote:
>|||Thanks. There is a problem with the round function, it returns an error
message saying the round function requires 2 to 3 arguments.
--
TS
"Tibor Karaszi" wrote:

> I found several issues with the query. The basic problem is that Access do
esn't follow ANSI SQL very
> well, So you need to re-work some part and learn to recognize those parts
to port from Access to SQL
> Server. I've did a little bit in your query, but as I didn't know the busi
ness requirements etc, I
> can't do it all. Here's the current version (possibly some parenthesis mis
match):
> SELECT
> SHR_Case_Name AS CaseName,
> RTRIM(SHR_last_name)+", "+RTRIM(SHR_first_name) AS ChildName,
> CRS_CID,
> ROUND((6/1/2005-[CRS_cid])/182.625)+1) * 182.625 + [CRS_cid],) AS Prelim_P
lan_Date,
> DATEPART("d",[CRS_cid]) AS ciddate,
> DATEPART("d",[prelim_plan_date]) AS pdate, [ciddate]-[pdate] AS difference,
> ABS([difference]) AS [absolute],
> CASE WHEN ABS([difference])<=3 THEN [prelim_plan_date]+[difference] ELSE [prelim_plan_date]-0 END
AS
> Plan_Date,
> DATEADD("d",-25,[plan_Date]) AS ACS_Date,
> DATEADD("d",-30,[plan_date]) AS Supervisor_DueDate INTO tbl_FaspFinal
> FROM tbl_PreventionCases
> GROUP BY RTRIM([tbl_PreventionCases]![SHR_last_na
me])+",
> " +RTrim([tbl_PreventionCases]![SHR_first_
name]), tbl_PreventionCases.CRS_C
ID
> HAVING
> ROUND( ((((6/1/2005-[tbl_PreventionCases]![CRS_cid])
> /182.625)+1)*182.625+[tbl_PreventionCases]![CRS_cid]),0) Between 9/30/2005
And 11/30/2005)
> Some comments:
> Learn to format the query in a more readable manner. It will help you and
others in the future.
> Is 6/1/2005 a date?If so, it should be expressed ad '20050601'. See
> http://www.karaszi.com/SQLServer/info_datetime.asp
> What is [tbl_PreventionCases]![SHR_last_name]? There's no exclamation mark
in the SQL language. Is
> it a text box in a form? Is it a column in a table? If a column in a table
, then use
> tblname.colname. If some GUI element, then you have to pass this in as a p
arameter to SQL Server.
> SQL Server cannot access what is on the screen of a client application.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "TS" <TS@.discussions.microsoft.com> wrote in message
> news:81B06B42-2C52-4DCA-B4DC-811112FDCE3A@.microsoft.com...
>sql

Hello
I have string 6:10, 11:30, 23:10, â?¦, in one of column. I need to convert
these string to time with the same format (HH:MM) so that I can sort by time.
How should I do this?
Thanks,
Jim.You may find these articles helpful:
http://www.sommarskog.se/arrays-in-sql.html
http://www.bizdatasolutions.com/tsql/sqlarrays.asp
--
Keith
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:ED6FF7F2-7B23-488A-8881-88339EBD9103@.microsoft.com...
> Hello
> I have string 6:10, 11:30, 23:10, â?¦, in one of column. I need to convert
> these string to time with the same format (HH:MM) so that I can sort by
time.
> How should I do this?
> Thanks,
> Jim.
>|||Some examples:
select * from (
select '6:10' as timecol
union all select '11:30'
union all select '23:10'
union all select '6:03' )
as t1
order by cast(timecol as datetime)
order by case when len(timecol) < 5 then '0' + timecol else timecol end
select cast(timecol as datetime) as newcol from (
select '6:10' as timecol
union all select '11:30'
union all select '23:10'
union all select '6:03' )
as t1
order by newcol
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:ED6FF7F2-7B23-488A-8881-88339EBD9103@.microsoft.com...
> Hello
> I have string 6:10, 11:30, 23:10, ., in one of column. I need to convert
> these string to time with the same format (HH:MM) so that I can sort by
time.
> How should I do this?
> Thanks,
> Jim.
>|||or instead of
order by case when len(timecol) < 5 then '0' + timecol else timecol end
order by right('0' + timecol,5)
"Scott Morris" wrote:
> Some examples:
> select * from (
> select '6:10' as timecol
> union all select '11:30'
> union all select '23:10'
> union all select '6:03' )
> as t1
> order by cast(timecol as datetime)
> order by case when len(timecol) < 5 then '0' + timecol else timecol end
> select cast(timecol as datetime) as newcol from (
> select '6:10' as timecol
> union all select '11:30'
> union all select '23:10'
> union all select '6:03' )
> as t1
> order by newcol
>
> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> news:ED6FF7F2-7B23-488A-8881-88339EBD9103@.microsoft.com...
> > Hello
> > I have string 6:10, 11:30, 23:10, ., in one of column. I need to convert
> > these string to time with the same format (HH:MM) so that I can sort by
> time.
> > How should I do this?
> > Thanks,
> > Jim.
> >
>
>|||Good one. The left() function came to mind at first but I couldn't think of
a way to use it. I was looking at the wrong end!
"Nigel Rivett" <sqlnr@.hotmail.com> wrote in message
news:29C1C2D5-CB91-4102-85BC-07A7F18146E5@.microsoft.com...
> or instead of
> order by case when len(timecol) < 5 then '0' + timecol else timecol end
> order by right('0' + timecol,5)
>
> "Scott Morris" wrote:
> > Some examples:
> >
> > select * from (
> > select '6:10' as timecol
> > union all select '11:30'
> > union all select '23:10'
> > union all select '6:03' )
> > as t1
> > order by cast(timecol as datetime)
> > order by case when len(timecol) < 5 then '0' + timecol else timecol end
> >
> > select cast(timecol as datetime) as newcol from (
> > select '6:10' as timecol
> > union all select '11:30'
> > union all select '23:10'
> > union all select '6:03' )
> > as t1
> > order by newcol
> >
> >
> > "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> > news:ED6FF7F2-7B23-488A-8881-88339EBD9103@.microsoft.com...
> > > Hello
> > > I have string 6:10, 11:30, 23:10, ., in one of column. I need to
convert
> > > these string to time with the same format (HH:MM) so that I can sort
by
> > time.
> > > How should I do this?
> > > Thanks,
> > > Jim.
> > >
> >
> >
> >

Monday, March 26, 2012

Query

Monday, February 20, 2012

Q: time convert

Q: time conversion

db time conversion

Blog Archive

About Me