Datetime format checking in SQL server stored procedure
-
Hi, I am wondering if there is a way of proofing a stored procedure in terms of the datetime localisation. For example, in the C# we may use the web.config or machine.config to set the application's localised date settings:
globalization uiCulture="en" culture="en-GB">
This helps when we do a date comparison in a static class of ours to return a certain rating factor. Is there an equivalent way of us checking whether or not the date we pass into the stored procedure is in e.g. UK format and will be compared against our dates in the UK format too? We have 2 datetime columns, DateEffectiveFrom and DateEffectiveTo in our table and we use those dates to compare with the date passed in to return the relevant data. Thanks
-
Hi, I am wondering if there is a way of proofing a stored procedure in terms of the datetime localisation. For example, in the C# we may use the web.config or machine.config to set the application's localised date settings:
globalization uiCulture="en" culture="en-GB">
This helps when we do a date comparison in a static class of ours to return a certain rating factor. Is there an equivalent way of us checking whether or not the date we pass into the stored procedure is in e.g. UK format and will be compared against our dates in the UK format too? We have 2 datetime columns, DateEffectiveFrom and DateEffectiveTo in our table and we use those dates to compare with the date passed in to return the relevant data. Thanks
Just to clarify one thing: if your columns are datetime columns, why do you need to worry about date formats? You can just compare one datetime with another. Is the parameter to the stored proc being passed as a string/varchar and you need to figure out what format it is in order to do the comparison?
-
Hi, I am wondering if there is a way of proofing a stored procedure in terms of the datetime localisation. For example, in the C# we may use the web.config or machine.config to set the application's localised date settings:
globalization uiCulture="en" culture="en-GB">
This helps when we do a date comparison in a static class of ours to return a certain rating factor. Is there an equivalent way of us checking whether or not the date we pass into the stored procedure is in e.g. UK format and will be compared against our dates in the UK format too? We have 2 datetime columns, DateEffectiveFrom and DateEffectiveTo in our table and we use those dates to compare with the date passed in to return the relevant data. Thanks
date formatting is a human thing, it does not relate to databases at all, unless you make a mistake. What you should do is define datetime fields, and pass DateTime SQLParameters, that avoids all datetime formatting issues on the DB side; obviously you still have to deal with the datetime formatting in the GUI, however that is the only place it should matter (and there you should follow the system's regional settings). :)
Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles] Nil Volentibus Arduum
Please use <PRE> tags for code snippets, they preserve indentation, and improve readability.
-
Hi, I am wondering if there is a way of proofing a stored procedure in terms of the datetime localisation. For example, in the C# we may use the web.config or machine.config to set the application's localised date settings:
globalization uiCulture="en" culture="en-GB">
This helps when we do a date comparison in a static class of ours to return a certain rating factor. Is there an equivalent way of us checking whether or not the date we pass into the stored procedure is in e.g. UK format and will be compared against our dates in the UK format too? We have 2 datetime columns, DateEffectiveFrom and DateEffectiveTo in our table and we use those dates to compare with the date passed in to return the relevant data. Thanks
-
Just to clarify one thing: if your columns are datetime columns, why do you need to worry about date formats? You can just compare one datetime with another. Is the parameter to the stored proc being passed as a string/varchar and you need to figure out what format it is in order to do the comparison?
Hi David, the parameter will be a datetime. I need to find which date range this falls in. For example, I pass in the date 11/11/2010 and the columns are: ID StartEffDate EndEffDate Rate 1 01/01/2000 31/12/2003 1.05 2 01/01/2004 31/03/2011 1.15 3 01/04/2011 01/01/2012 1.25 I need to return the appropriate Rate, e.g. in this case 1.15