CODEWITHSUNDEEP
sqlsql-servert-sqldatetime
Sk1X1
Sk1X1

Reputation: 1373

Specific day of current month and year

I have problem with return of specific day of current month and year. I need for example 15th day. Until now I used in FB/IB existing function:

IB_EncodeDate(EXTRACT(YEAR FROM Current_Date),EXTRACT(Month FROM Current_Date),15)

Does it exist a simply way to convert this for MSSQL database?

edit. I need output in OLE format (41,348 by example) to compare date with another date. I compare date from database with 15th day of current month.

Upvotes: 5

Views: 28566

Answers (7)

user12809806
user12809806

Reputation: 1

A bit more straightforward approach:

CAST(FORMAT(GETDATE(), 'yyyy-MM-15') AS DateTime)

Upvotes: 0

Saikh Rakif
Saikh Rakif

Reputation: 135

To get 10th day of current day

declare @cur_month int,@cur_yr int,@tenth_dt date
    set @cur_month=month(getdate())
    set @cur_yr=YEAR(getdate())
    set @tenth_dt=convert(date,'10/'+convert(varchar(5),@cur_month)+'/'+convert(varchar(5),@cur_yr),103)
    select @tenth_dt

Upvotes: 1

Joe Johnston
Joe Johnston

Reputation: 2936

This seems like a quick answer. 

declare @OLEDate int 
declare @currentDate as datetime
set @currentDate = DATEADD(DAY, 14, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0))
set @OLEDate = convert(float, @currentdate) 
-- PRINT @OLEDate

based on Aaron Bertrand's answer and your need for the integer conversion

Upvotes: 1

Aaron Bertrand
Aaron Bertrand

Reputation: 280252

For the 15th day of current month:

SELECT DATEADD(DAY, 14, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0));

To get the silly OLE representation based on this "magic" date, 1899-12-30:

SELECT DATEDIFF(DAY, -2, DATEADD(DAY, 14, 
  DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)));

Answer (on March 11th, when I updated this answer for the changed requirement):

-----
41348

Upvotes: 8

muhmud
muhmud

Reputation: 4604

Current_Date in SQL Server would be getdate().

To get the 15th day in OLE Automation format, try:

select datediff(day, '18991230', dateadd(day, -day(getdate()) + 15, getdate()))

Upvotes: 0

Kaf
Kaf

Reputation: 33809

Not sure if you after Day or Date. This gives both dayOfWeek and specificDate for any culture

declare @myDay int = 15

select convert(date,myday) specificDate, datename(dw,myday) dayOfWeek
from (
   select convert(varchar(6),getdate(),112) + convert(varchar, @myDay) myday
) x

Fiddle Demo Here

| SPECIFICDATE | DAYOFWEEK |
----------------------------
|   2013-02-15 |    Friday |

Upvotes: 0

Lamak
Lamak

Reputation: 70638

So, you have a date, and want to return the 15th day of the same month?. Well, assuming SQL Server 2008, you could do this:

SELECT CONVERT(DATE,CONVERT(VARCHAR(6),GETDATE(),112)+'15',112)

For Previous versions of SQL Server:

SELECT CONVERT(DATETIME,CONVERT(VARCHAR(6),GETDATE(),112)+'15',112)

Upvotes: 3

Related Questions