?
Solved

Query Syntax in SSMS

Posted on 2016-07-28
5
Medium Priority
?
47 Views
Last Modified: 2016-07-29
I have a query I am trying to write and I have a couple of issues retrieving data:

Issue #1: I am trying to return the right 2 characters in a field that appears to be left justified. The field is a combined 'CITY ST' with a space delimiter before the 2 character state. I obviously cannot determine precisely where in that 50 character, left justified field the actual 2 characters will be.

Issue #2: I am trying to write an expression that I did have working in an Access database (I am trying to move my queries to management studio). The query in Access was:

Round([PER_DIEM_INT]+(Round((GETDATE()-[PER_DIEM_DATE])*[PER_DIEM_AMT],2)),2)-[INT_COLL] AS INT_DUE

error: Implicit conversion from data type datetime to float is not allowed. Use the CONVERT function to run this query.
0
Comment
Question by:sparker1970
  • 2
  • 2
5 Comments
 
LVL 66

Assisted Solution

by:Jim Horn
Jim Horn earned 2000 total points
ID: 41733833
Give us some sample data to work with, but as a guess...

#1 - You'll need to trim the trailing spaces before you can get the last two characters, something like..
Declare @citystate char(50) = 'EDEN PRAIRIE, MN                  '
SELECT RIGHT(RTRIM(@citystate), 2) 

Open in new window


#2
>error: Implicit conversion from data type datetime to float is not allowed.
Explain for us the GETDATE()-[PER_DIEM_DATE] part, are you trying to get the difference in days?
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 41733848
try this for #2
ROUND( [PER_DIEM_INT] + ROUND( DATEDIFF(day,PER_DIEM_DATE, GETDATE() ) *[PER_DIEM_AMT] ,2) )

Think you need to use DATEDIFF() this returns an integer number of the units (I chose day as the unit)

Issue #1. Are you saying the state isn't always at the last 2 chars at the right?
0
 
LVL 66

Accepted Solution

by:
Jim Horn earned 2000 total points
ID: 41733851
Wild guess on #2..
SELECT ROUND(
  PER_DIEM_INT + 
  (DATEDIFF(days, GETDATE(), PER_DIEM_DATE) * PER_DIEM_AMT) -
  INT_COLL, 2) AS INT_DUE

Open in new window


btw check out Migrating your Access Queries to SQL Server Transact-SQL for a wompload of helpful Access SQL to SQL Server T-SQL tips.
0
 

Author Comment

by:sparker1970
ID: 41735214
Paul...with the 2 "ROUND" statements there was a missing argument for one of them.

St. Jimbo...the RTRIM statement worked perfectly...your "wild guess" was spot on with one exception, it was day not days.
0
 

Author Closing Comment

by:sparker1970
ID: 41735217
See my last post for response on solutions.

Thank you very much!
0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Microsoft provides a rich set of technologies for High Availability and Disaster Recovery solutions.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

615 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question