Syntax for dates - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Syntax for dates

Author

Topic

sri99860
Starting Member

7 Posts

Posted - 2012-12-11 : 09:02:57

Hello,

I am new to SQL Server 2008. Need replacement functions for the below queries(sql)in sql server 2008.

1. trunc(sysdate)-1.0001
2. Trunc(sysdate - 3)
3. Trunc(sysdate, 'MM')
4. trunc(sysdate, 'DAY')
5. TO_YMINTERVAL('03-00')
6. TO_YMINTERVAL('00-01'))) +.99999
7. Trunc(last_day(Trunc(sysdate, 'MM')
8. to_char(Trunc(sysdate-1), 'D')
9. next_day(trunc(sysdate)-7.00001,'Saturday')
10.trunc(last_day(Trunc(sysdate, 'MM')
11.add_months(trunc(sysdate,'Q'),- 3)

Appreciate for your quick response.

Regards,
Sri.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-12-11 : 09:14:10

use this for equivalent in SQL.

http://www.sqlines.com/oracle/functions/trunc_datetime

sri99860
Starting Member

7 Posts

Posted - 2012-12-12 : 05:33:13

Thanks for your reply.

The link was provided had only trunc replacement functions.

Any one knows other functions.

Regards,
Sri.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-12-12 : 05:47:13

to_char(Trunc(sysdate-1), 'D') --If you want day part from a date, DATEPART( D, dateField)
For data type conversions, see CONVERT and CAST functions
To add -3 months, DATEADD( MM, -3, DateFileld)

EDIT:
See 2.4.8 section in this link
http://docs.oracle.com/cd/E10405_01/doc/appdev.120/e10379/ss_oracle_compared.htm#i1038077

--
Chandu

sri99860
Starting Member

7 Posts

Posted - 2012-12-13 : 04:38:08

Thanks Bandi.

I am getting "Operand data type date is invalid for subtract operator." error if i subtract one query from the other. However if the query was running successfully if i split the query.

Select 'Previous 36 Months' as DateRange,
CONVERT(DATE,DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0)),120) - CONVERT(VARCHAR(10),DATEADD(m,-1+.99999,GETDATE()),120) as EndDate

Regards,
Sri.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-12-13 : 04:47:49

In mssql, You can't subract two dates. refer DATEDIFF(),DATEADD() date functions...

--
Chandu

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources