Provider=SQLNCLI11.1;Integrated Security="";Persist Security Info=False;User ID=sa;Initial Catalog=MYDATABASE;Data Source=MY-PC;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=MY-PC;Initial File Name="";Use Encryption for Data=False;Tag with column collation when possible=False;MARS Connection=False;DataTypeCompatibility=0;Trust Server Certificate=False;Application Intent=READWRITE;

In hotel stay,the day of departure is the next day.

so if you enter 26.6 till 27.6 that is one day stay.

jeff,thanks a lot...

Did not have the time to check this but it seems the last output is wrong

DBLMS 2011-04-25 00:00:00.000 2011-04-26 00:00:00.000 118.00 ...

"Result matches with what you want. You need to change dates & "<" / ">" signs according to your requirements."

this is the hard part ....i cant seem to make this...

No,this query does not work !

Changed :

else RATE_START_DATE

end,

case when rate_start_date > '2011-04-14'

then '2011-04-20'

else RATE_END_DATE

end

but how do I get column names ???

I did a quickie and this is what I get :

room_type(No column name)(No column name)ratedays

DBLMS2011-04-14 00:00:00.0002011-04-16 00:00:00.000104,00-101

DBLMS2011-04-16 00:00:00.0002011-04-20 00:00:00.000122,00-5

????

This seems to produce the right results:

SELECT

room_type,rate_start_date,rate_end_date,rate

, DATEDIFF(DAY,case when rate_end_date < '2011-04-20'

...

There's a price change on 2011-04-16 ...

when I run this :

SELECT

room_type,rate_start_date,rate_end_date,rate

, DATEDIFF(DAY,case when rate_end_date > '2011-04-14'

...

You're right (it's from mysql)

I tried your version but get :

Msg 174, Level 15, State 1, Line 3

The datediff function requires 3 argument(s).

Here is all the data :

http://www.sqlservercentral.com/Forums/Topic1131513-1292-1.aspx

Posted a new question...

Thank you all ...

But ...since everything seems to be going well, I have obviously overlooked something.....

😛

so basically I need this :

SET DATEFORMAT DMY

before every insert queery.

I dont really care how the server stores my data as long as I enter it

correctly....

I tried and it...

Convert thing just converts data view - it leaves field intact.

Converts data (date type) to string.

This is done just to view data.

However,the data (datetime) itself unless in the crazy format...