Converting an access query to SQL

[dougancil]

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

SELECT [1_1Employee].Last_First_Name, [1_1Employee].Employee_Number, 
format((([Timestamp]-(([LoggedIn]/1000)/60))/1440)+1,"mm/dd/yy") AS [Date], 
format((([Timestamp]-(([LoggedIn]/1000)/60))/1440)+1,"dddd") AS [Day], 
Sum(([LoggedIn]/1000/60)) AS LogIn, Sum(([OnTime]/1000/60)) AS OnTime1,
Format((([Timestamp]-(([LoggedIn]/1000)/60))/1440)+1,"yy/mm/dd") AS Da INTO 1_1ScratchPad

FROM 1_1Employee INNER JOIN dbo_mOpLogout ON [1_1Employee].Employee_Login = dbo_mOpLogout.Opname

GROUP BY [1_1Employee].Last_First_Name, [1_1Employee].Employee_Number, 
Format((([Timestamp]-(([LoggedIn]/1000)/60))/1440)+1,"mm/dd/yy"),
Format((([Timestamp]-(([LoggedIn]/1000)/60))/1440)+1,"dddd"),
Format((([Timestamp]-(([LoggedIn]/1000)/60))/1440)+1,"yy/mm/dd")

HAVING (((Format$((([Timestamp]-(([LoggedIn]/1000)/60))/1440)+1,"yy/mm/dd")) 
Between [Start (YY/MM/DD)] And [End (YY/MM/DD)]));

[NeoPa]

Sure.

Where specifically do you need help?

What have you tried already and what results (or errors) have you had?

[dougancil]

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

SELECT [1_1Employee].Last_First_Name, [1_1Employee].Employee_Number, 
format((([Timestamp]-(([LoggedIn]/1000)/60))/1440)+1,"mm/dd/yy") AS [Date], 
format((([Timestamp]-(([LoggedIn]/1000)/60))/1440)+1,"dddd") AS [Day], 
Sum(([LoggedIn]/1000/60)) AS LogIn, Sum(([OnTime]/1000/60)) AS OnTime1,
Format((([Timestamp]-(([LoggedIn]/1000)/60))/1440)+1,"yy/mm/dd") AS Da INTO 1_1ScratchPad

FROM 1_1Employee INNER JOIN dbo_mOpLogout ON [1_1Employee].Employee_Login = dbo_mOpLogout.Opname

GROUP BY [1_1Employee].Last_First_Name, [1_1Employee].Employee_Number, 
Format((([Timestamp]-(([LoggedIn]/1000)/60))/1440)+1,"mm/dd/yy"),
Format((([Timestamp]-(([LoggedIn]/1000)/60))/1440)+1,"dddd"),
Format((([Timestamp]-(([LoggedIn]/1000)/60))/1440)+1,"yy/mm/dd")

HAVING (((Format((([Timestamp]-(([LoggedIn]/1000)/60))/1440)+1,"yy/mm/dd")) 
Between [Start (YY/MM/DD)] And [End (YY/MM/DD)]));

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.

[NeoPa]

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.

[dougancil]

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.

[NeoPa]

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?

[dougancil]

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

[ck9663]

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

[dougancil]

CK or Neo,

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

SELECT        Opname, LoggedIn, OnTime, OpNumber
FROM            mOpInterval
cast (Timestamp(([LoggedIn]/1000)/60))/1440)+1,"mm/dd/yy")

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.

[NeoPa]

Doug, do you mean :

SELECT Opname
     , LoggedIn
     , CONVERT(nvarchar(10),[LoggedIn],101) AS strLoggedIn
     , OnTime
     , OpNumber
FROM   mOpInterval

And doesn't this just take us back to post #4 where this was covered previously?

[dougancil]

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

[NeoPa]

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