Hi,

My SQL is a bit rusty and I need some advice on how to handle dateformats in module please.

Basically I use user-inputted dates in some of my modules (like the DOB field in the ST UserProfile module). Occasionally some users report errors with date conversions - the most recent was a German user.

To fix the problem, I inserted SET DATEFORMAT mdy at the top of the SPs. My logic for this is that regardless of what culture the DB thinks it is in (it gets it from Windows I think) - I treat all dates as mdy. I have seen a solution where the business layer appended this SQL statement to every single DB call, thereby ensuring it was consistent across the app.

The other part of this question is the user-inputted side - does the calendar control check the browser culture or the user's specified culture in DNN and mask the date accordingly? Is it then converted into the default DB format based? So are all dates converted internally to the same format as long as I use the core Calendar control, or do I have to do some code to check for this? Can I assume all dates in the DB have been converted?

So, my questions:

1. How do other people handle user-dates across different cultures?
2. Should I use ymd or mdy, or does it matter as long as I am consitient across the module
3. How does the UI handle different cultures and do I need to do anything around this/
4. Any advice??

I did a search on the forums for "Set Dateformat" and there was only 1 result in the last year, and that was a snippet from the Blog SQL that sets the format to be mdy...

Entrepreneur

PokerDIY Tournament Manager - PokerDIY Tournament Manager<
PokerDIY Game Finder - Mobile Apps powered by DNN
PokerDIY - Connecting Poker Players

setting date formats should not be done at the sql level but rather from the ui level. 

you should parse the date string based on the locale/culture of the logged user.  This way you can pass the data object to the the dal and not have to worry about the different formats.

Thanks for the replies. Something is not quite right as I use the DNN core Date Selector which I thought would handle the date formatting for me, but on a German installation my module fell over, so I'll have to try and trap where the problem is.

Entrepreneur

PokerDIY Tournament Manager - PokerDIY Tournament Manager<
PokerDIY Game Finder - Mobile Apps powered by DNN
PokerDIY - Connecting Poker Players