So long as the night shift is not going to stuff up your sleeping pattern.
I couldn't do that; I've tried doing night work, shift work, and various other things, but I am an early riser, a lark rather than an owl.
You could always tell your boss if you think the night shift won't work, that you are willing to try something.
There could be some merit in being honest with him, letting him know you want something more challenging, but need some sort of routine.
6 years is a long time, and they owe you something so far as trying to find you a right fit if you are willing to remain loyal to them, especially if you're honest with them, tell they you like working there, but need something different to make you feel engaged and appreciated.