Datetime shenanigans

Question

Datetime shenanigans

Viewing 6 posts - 16 through 20 (of 20 total)

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

noeld SSC Guru Points: 96590 More actions · Answer 1

And by the way if somebody change DATEFORMAT inside their stored procedure is up to them to handle it inside that scope the outer scope will be unaffected

* Noel

Sergiy SSC Guru Points: 110208 More actions · Answer 2

And you must be sure the SP you are creating will never been called from another SP having SET DATEFORMAT ... inside.

_____________
Code for TallyGenerator

noeld SSC Guru Points: 96590 More actions · Answer 3

I would like to see if you check for Dateformat on all your SPs or if like the rest of us simply use standards so that when those values are to be changed, is simple enough to save previous status at entry and restore it at exit. And such operations have to be really justifiable to pass a code review

but hey Who I am to teach you how to program, right?

* Noel

Ian Yates SSCoach Points: 19738 More actions · Answer 4

As someone said, ISO format should also work (yyyymmdd), but the ODBC format should also work everywhere - it is something like {t'yyyy-mm-dd'} - can't remember off the top of my head, but if you use Crystal Reports to connect to SQL Server (even via OLE DB) it uses this dateformat without any trouble.

Sergiy SSC Guru Points: 110208 More actions · Answer 5

I just don't use to convert datetime to varchar.

The only place I need DATEFORMAT is reading from flat file. And here I use a lot of tricks, and I collect them all in one SP, and I use this SP every time I need to properly read set of datetime values. This SP is thoroughtly reviewed, debugged and tested for all cases I can invent.

So, there is no really much code to review.

But probably I don't need to tell you about good practices in programming.

_____________
Code for TallyGenerator

Jamie Thomson SSChampion Points: 11805 More actions · Answer 6

Guys,

Thank you for the help. I used Noel's code and proved the theory 'British' does indeed cause 2005-10-11 to be interpreted as YYYY-DD-MM.

Thanks again. I've really learnt something here today.

Of course, the lesson is, always do explicit conversions. I'll never forget that one.

-Jamie

Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson