Specific day of current month and year

Question

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.

Solution 1

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

Solution 2

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)

Solution 3

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

Solution 4

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

View more solutions

Comments

[Adir D]

Datename? Be very careful. Regional / language settings might not treat you well here.

[muhmud]

In what way? It's only there to facilitate a cast to datetime, i.e. it's not used in two different contexts. This could have used month(), but I don't think this would break.

[Adir D]

Why would you rely on a regional date string? How do you know how the OP (or any future reader) will use it?

[Adir D]

Please don't use date shorthands like dw or convert(varchar without length. These are bad practices to use yourself, never mind suggesting to other users...

[Sk1X1]

Thanks, I use: 'cast(CONVERT(DATETIME,CONVERT(VARCHAR(6),GETDATE(),112)+'15‌​') as float)' and it looks like working right.

[Sk1X1]

Is here a way, how can I this use for actual date + 7? By example, if today is 5.3, so I need 12.3.

[Lamak]

@Sk1X1 I'm lost at what is it that you really want. Why do you want to cast the date as FLOAT?, what do you mean with your last comment?, do you want to add 7 days to a date?, so use DATEADD(DAY,7,GETDATE()), no need to cast it to float an add 7

[Sk1X1]

I need date in float to compare it with another date. Thanks, on this function I forgot. I'm new in SQL and I convert SQL scripts from Firebird to MSSQL.

[Lamak]

@Sk1X1 Ok, whatever works for you. I still don't know why you need to convert a date to a float to compare it to another date.

[Sk1X1]

All dates in my databases are saved as float.

[Lamak]

@Sk1X1 Ok then. So, a word of advice, in the future, store dates as a date datatype on your database

[Sk1X1]

Thanks for advice. This is company database and unfortunately I have to work with this what I get.

[muhmud]

After having to do this myself today, I've updated my answer.

[Adir D]

But this has a time component. If it is being used as an endpoint in a range query, for example, it won't capture the whole day unless you happen to run this code precisely at midnight.

[Adir D]

don't Geez me, please. Accuracy is important. The question is clear, they need to compare a DATE, not a specific point in time. And for this conversion, it is very expensive to convert to string, and you should never use varchar without length. These details may not be important to you, but they are important to the OP and other readers.

[muhmud]

Actually, he put in an update to ask for the date in a specific format, so I've updated for that.

[Adir D]

@Sk1X1 Oy vey. Dates as floats! Do you store salary as strings and names in varbinary?

[swasheck]

my username is 0x737761736865636B

[Zane]

@Sk1X1 is this your company? If so you have to get them to change that. There is NO I repeat NO reason to store date/datetimes as a float. One more time NO reason to store Dates as float. None. Ever.

[JNK]

Float is probably among the worst choices to use for a date field since it's an approximate data type.

[Jeremy Caney]

Why do you find this more straightforward than the existing answers?