August 27, 2010 at 10:31 am
I've been tasked with converting 9 queries that were written in access to sql to write a payroll application. The problem is that I'm not terribly versed in the differences between access and sql and was wondering if anyone could help me out with some of the conversion syntax. Here (for example) is one of the queries that I'm trying to convert:
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)]));
I know off the bat that Format$ wont work but I have to decide which is my best option cast or convert. Can someone tell me what they think the advantage/disadvantages would be to using either on this query.
Thank you,
Doug
September 6, 2010 at 11:49 pm
refer this link,
http://searchsqlserver.techtarget.com/tip/The-difference-between-CONVERT-and-CAST-in-SQL-Server
[font="Verdana"]Regards
Kumar Harsh[/font]
September 8, 2010 at 7:14 am
Doug
Your formula is calculating a number and then changing it into a date, so you need CAST to accomplish that. You also need CONVERT to change the resulting date into a formatted string.
Be careful with the number-to-date conversion which is handled differently between Access and SQL --
In Access, CDate(1) => 12/31/1899
but
In SQL, SELECT CAST(1 AS datetime) => 01/02/1900
so you will have a difference of 2 days that has to be adjusted in your formulas.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply