Equivalent SQL Server syntax to Oracle 1

[tcimis]

We are converting a database from Oracle to SQL Server. Does anyone know what the equivalent is in SQL Server for the following Oracle syntax:

1. trunc(sysdate) In Oracle this sets the time on the date to midnight.

2. Does SQL Server have a table like the Oracle dual table?
I can always add my own just wanted to know if a similar table existed in SQL Server.

3. to_char - converts Oracle date into a character string.

4. to_date - converts character string or number into an Oracle date.

Thanks!

 

[osjohnm]

Hi tcimis

1. getdate(), will return the current date and time but if you set a field to getdate() in an update statment it should insert the date but set the time part of a datetime datatype field to 00:00:00. you will have to test it to make sure, i can't remember if it does do that.

it works like that in a job that i execute on a price table to expire invalid prices so i think that it might do the trick.

2. can't help with the dual table my oracle knowledge isn't that extensive.

3. select/update ....convert(char(20), user_id)

4. i'm not sure about the date conversion but BOL will contain info on that.

Hope this has helped.

John

 

[karluk]

You don't need a table like DUAL in SQL Server. Oracle syntax demands a "from" clause in every select, SQL Server does not.

Oracle:
select sysdate from dual;

SQL Server:
select getdate()

 

[tlbroadbent]

Use the Convert function to convert strings or numbers to dates. See the following link for a MS technical paper on migrating Oracle to SQL Server 7. It contains a lot of detail about equivalent data types, functions, etc.

http://msdn.microsoft.com/library/en-us/dnsqlsm/html/oracle2sql.asp

Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[tcimis]

Thanks for all the assistance. It is greatly appreciated.