convert to date from datetime

Question

Post reply

convert to date from datetime

Bhagat

SSC-Addicted

Points: 405
More actions
November 2, 2023 at 12:29 pm

#4319678
I created the following variable to return the date 25 months ago - all fine.
```
 DATEADD("m", -25, DATEADD("d", -DAY(GETDATE()) + 1, GETDATE()))
```
I am having difficulty casting to date datatype, when I add DT_DBDATE
```
 (DT_DBDATE) DATEADD("m", -25, DATEADD("d", -DAY(GETDATE()) + 1, GETDATE()))
```
it throws an error - expression cannot be evaluated. with the message of:
The expression "(DT_DBDATE) DATEADD("m", -25, DATEADD("d", -DAY(GETDATE()) + 1, GETDATE()))" has a result type of "DT_DBDATE", which cannot be converted to a supported type.
Any ideas how to correct it?
Thank you

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

below86 SSChampion Points: 11553 More actions · Answer 1

Why not just use CAST?

CAST( DATEADD("m", -25, DATEADD("d", -DAY(GETDATE()) + 1, GETDATE())) AS DATE)

-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.

Steve Collins SSCrazy Points: 2176 More actions · Answer 2

Also there's EOMONTH which has an optional second parameter 'offset'. Regardless of which date/datetime datatype is passed to the EOMONTH function it returns DATE. Ha, some SSC members were wondering whether or not that's a feature or a flaw lol. Imo it's a feature

declare @datetime       datetime=getdate();

select DATEADD(month, -25, DATEADD(day, -DAY(@datetime) + 1, @datetime))  how_now,
       CAST( DATEADD("m", -25, DATEADD("d", -DAY(@datetime) + 1, @datetime)) AS DATE) use_cast;

select dateadd(day, 1, eomonth(@datetime, -26)) minus_26mo_plus_1day,
       sql_variant_property(dateadd(day, 1, eomonth(@datetime, -26)), 'BaseType') ret_datatype;

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Bhagat SSC-Addicted Points: 405 More actions · Answer 3

Sorry, forgot to mention this is to be used within SSIS / Visual Studio 2019, so those do not work.

So, casting the below output to date datatype:

date

Phil Parkin SSC Guru Points: 246990 More actions · Answer 4

Try this:

(DT_DATE)(DT_DBDATE)DATEADD("m", -25, DATEADD("d", -DAY(GETDATE()) + 1, GETDATE()))

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel