469,579 Members | 1,899 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,579 developers. It's quick & easy.

Converting an access query to SQL

347 100+
I have the following query that was written by someone else that I need help converting to a SQL query, can anyone assist me with this?

Thank you

Expand|Select|Wrap|Line Numbers
  1. SELECT [1_1Employee].Last_First_Name, [1_1Employee].Employee_Number, 
  2. format((([Timestamp]-(([LoggedIn]/1000)/60))/1440)+1,"mm/dd/yy") AS [Date], 
  3. format((([Timestamp]-(([LoggedIn]/1000)/60))/1440)+1,"dddd") AS [Day], 
  4. Sum(([LoggedIn]/1000/60)) AS LogIn, Sum(([OnTime]/1000/60)) AS OnTime1,
  5. Format((([Timestamp]-(([LoggedIn]/1000)/60))/1440)+1,"yy/mm/dd") AS Da INTO 1_1ScratchPad
  6.  
  7. FROM 1_1Employee INNER JOIN dbo_mOpLogout ON [1_1Employee].Employee_Login = dbo_mOpLogout.Opname
  8.  
  9. GROUP BY [1_1Employee].Last_First_Name, [1_1Employee].Employee_Number, 
  10. Format((([Timestamp]-(([LoggedIn]/1000)/60))/1440)+1,"mm/dd/yy"),
  11. Format((([Timestamp]-(([LoggedIn]/1000)/60))/1440)+1,"dddd"),
  12. Format((([Timestamp]-(([LoggedIn]/1000)/60))/1440)+1,"yy/mm/dd")
  13.  
  14. HAVING (((Format$((([Timestamp]-(([LoggedIn]/1000)/60))/1440)+1,"yy/mm/dd")) 
  15. Between [Start (YY/MM/DD)] And [End (YY/MM/DD)]));
  16.  
Aug 26 '10 #1
11 2404
NeoPa
32,198 Expert Mod 16PB
Sure.

Where specifically do you need help?

What have you tried already and what results (or errors) have you had?
Aug 26 '10 #2
dougancil
347 100+
Well I'm not really sure on the syntax differences here. The "format" command is the first thing that I've stumbled into.

So here is where I'm starting from
Expand|Select|Wrap|Line Numbers
  1. SELECT [1_1Employee].Last_First_Name, [1_1Employee].Employee_Number, 
  2. format((([Timestamp]-(([LoggedIn]/1000)/60))/1440)+1,"mm/dd/yy") AS [Date], 
  3. format((([Timestamp]-(([LoggedIn]/1000)/60))/1440)+1,"dddd") AS [Day], 
  4. Sum(([LoggedIn]/1000/60)) AS LogIn, Sum(([OnTime]/1000/60)) AS OnTime1,
  5. Format((([Timestamp]-(([LoggedIn]/1000)/60))/1440)+1,"yy/mm/dd") AS Da INTO 1_1ScratchPad
  6.  
  7. FROM 1_1Employee INNER JOIN dbo_mOpLogout ON [1_1Employee].Employee_Login = dbo_mOpLogout.Opname
  8.  
  9. GROUP BY [1_1Employee].Last_First_Name, [1_1Employee].Employee_Number, 
  10. Format((([Timestamp]-(([LoggedIn]/1000)/60))/1440)+1,"mm/dd/yy"),
  11. Format((([Timestamp]-(([LoggedIn]/1000)/60))/1440)+1,"dddd"),
  12. Format((([Timestamp]-(([LoggedIn]/1000)/60))/1440)+1,"yy/mm/dd")
  13.  
  14. HAVING (((Format((([Timestamp]-(([LoggedIn]/1000)/60))/1440)+1,"yy/mm/dd")) 
  15. Between [Start (YY/MM/DD)] And [End (YY/MM/DD)]));
  16.  
and here is the error I have so far:

Server: Msg 195, Level 15, State 10, Line 2
'format' is not a recognized function name.
Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near 'Timestamp'.
Server: Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near 'Timestamp'.
Server: Msg 170, Level 15, State 1, Line 10
Line 10: Incorrect syntax near 'Timestamp'.
Server: Msg 170, Level 15, State 1, Line 11
Line 11: Incorrect syntax near 'Timestamp'.
Server: Msg 170, Level 15, State 1, Line 12
Line 12: Incorrect syntax near 'Timestamp'.
Server: Msg 170, Level 15, State 1, Line 14
Line 14: Incorrect syntax near 'Format'.
Server: Msg 170, Level 15, State 1, Line 14
Line 14: Incorrect syntax near 'Timestamp'.

and I know that the incorrect syntax has to be the format command that is in there.
Aug 26 '10 #3
NeoPa
32,198 Expert Mod 16PB
Doug, I'll deal with the format issue, but this is not what I was asking for. If you have a problem with your format command I don't want to have to swim through 15 odd lines of questionable SQL to find it. That may be your eventual aim, but I asked for specific questions deliberately. If you can work with me then I can work with you.

Anyway, T-SQL, the language used in SQL Server, has a couple of functions that do the job of formatting data from one type to another. These are CAST() and CONVERT(). Be warned. They are markedly different from Jet SQL (The SQL used in Access). There are various options available in CONVERT() (I would expect this to be the more appropriate for you on this one), too many to list here. One of the problems is that they are a finite list. They don't have the flexibility you'd be used to with Format() I'm afraid.

Context-sensitive Help works well from Management Studio. Type CONVERT and press F1 to see the relevant page with full details appear before you. 101 for mm/dd/yyyy, 103 for dd/mm/yyyy, 111 for yyyy/mm/dd, etc.
Aug 26 '10 #4
dougancil
347 100+
Neo,

Specifically where I need help is as follows. I didn't write this query and while I vaguely understand what it does, I need to try to simplify it, as well as making it as clean and efficient as possible while still performing it's task. This is the first time that I've had to recode someone elses work and since I'm not the DBA here, and I'm not that well versed in SQL this is taking me a considerable amount of time. At this point, I havent tried anything because before I step into that realm, I want to see what I'm up against. I have two more blocks of queries like this that I'll need to reformat from Access to SQL after this. If this can be converted relatively easily from Access to SQL then I'm all for that but if I have to go back and rebuilt the query from scratch, then that's what I'll need to look at.
Aug 26 '10 #5
NeoPa
32,198 Expert Mod 16PB
Most things in T-SQL work, more or less, as they do in Jet SQL. There are of course exceptions.

I'm not aware of anything around that can do the job (or possibly even most of it) for you. That doesn't mean they're not out there, or even that no-one here would be able to point you to them. It's just never been my requirement to find them or work that way.

Unfortunately for you, you seem to have been lumbered with SQL that, in my estimation at least, has a high level of incompatibility. I can't say how you would approach this, but I would pick it off one concept at a time. I very well may end up recoding the whole thing from scratch, but I wouldn't say that should be necessary. You should be able to fix one bit at a time until all the issues are resolved. It may be a little laborious, but that's your situation as I see it.

I'm happy to help with specifics, such as "How the f*$! do I translate something like Format()??!?", but I'll leave you with the process of fixing all you don't need specific help with. Does that seem fair?
Aug 26 '10 #6
dougancil
347 100+
Neo,

So by looking at least at the query as I have it right now, which do you think would be the best way to approach this, with cast or convert?

Thanks

Doug
Aug 30 '10 #7
ck9663
2,878 Expert 2GB
Use CONVERT on those that requires formatting and use CAST on those that simply needs direct data conversion. You're also going to need this function or this function to get the day portion of your date expression.

Remember, once formatted, your date might no longer be a date datatype but a formatted string/character type.

Good Luck!!!

~~ CK
Aug 30 '10 #8
dougancil
347 100+
CK or Neo,

Ok here's a small query that I'm trying to run:

Expand|Select|Wrap|Line Numbers
  1. SELECT        Opname, LoggedIn, OnTime, OpNumber
  2. FROM            mOpInterval
  3. cast (Timestamp(([LoggedIn]/1000)/60))/1440)+1,"mm/dd/yy")
  4.  
What I'm needing to do is to pull that information from the mOpInterval table (which is in minutes) and convert them into a more "readable" format. Also it's my way of finding which information to pull from the table. Any thoughts or ideas? I know this isn't very well worded so I know that what I'm trying to retrieve may not be that clear.
Sep 2 '10 #9
NeoPa
32,198 Expert Mod 16PB
Doug, do you mean :
Expand|Select|Wrap|Line Numbers
  1. SELECT Opname
  2.      , LoggedIn
  3.      , CONVERT(nvarchar(10),[LoggedIn],101) AS strLoggedIn
  4.      , OnTime
  5.      , OpNumber
  6. FROM   mOpInterval
And doesn't this just take us back to post #4 where this was covered previously?
Sep 6 '10 #10
dougancil
347 100+
Neo,

Actually that worked because I found out that in order to do most of my queries, I had to import data into a table from a .csv file. I'm in the process of working through this at the moment. What my next step is is the step preceeding this. That step will give me the starting date for this query. I'll post more information as I get it.


Thank you,

Doug
Sep 7 '10 #11
NeoPa
32,198 Expert Mod 16PB
That sounds like good news, though the next step being the preceding one sounds a little Alice-in-Wonderland in a funny sort of way.

Let us know when you have more to say :D
Sep 7 '10 #12

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

17 posts views Thread by chicha | last post: by
reply views Thread by suresh191 | last post: by
4 posts views Thread by guiromero | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.