?
Solved

Query Syntax in SSMS

Posted on 2016-07-28
5
Medium Priority
?
39 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Quick Start: DOCKER

Sometimes you just need a Quick Start on a topic in order to begin using it.. this is just what you need to know to get up and running with Docker!

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

762 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