Passing Date Variable in British format

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Passing Date Variable in British format

Author

Topic

MrSmallTime
Starting Member

32 Posts

Posted - 2014-01-01 : 19:25:24

This is driving me to despair

I want to pass a couple of date variables in British date format to a function

Here's the function


SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[ufnGetWorkingDays](@StartDate Date,@EndDate Date)
RETURNS int 
AS 
-- Returns WorkingDays.
BEGIN
    DECLARE @WorkingDay int;
		Select @WorkingDay=Count(*)-1
		From tblWDayCalendar
		Where WorkingDay='Yes'
		And CalDate Between @StartDate And @EndDate
		IF (@WorkingDay IS NULL) 
			SET @WorkingDay = 0;
		RETURN @WorkingDay;
END;
GO

All works fine with
select [dbo].ufnGetWorkingDays ('2014/01/01','2014/01/22')

However I'd really prefer to call the function like this
select [dbo].ufnGetWorkingDays ('01/01/2014','22/01/2014')

I've tried things like
And CONVERT(DATE,CalDate,103) Between CONVERT(DATE,@StartDate,103) And CONVERT(DATE,@EndDate,103)

but failed abysmally

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2014-01-01 : 19:48:41

Change @StartDate and @EndDate from date to varchar(10), and then use:
and caldate>=convert(date,@StartDate,103)
and caldate<=convert(date,@EndDate,103)

MrSmallTime
Starting Member

32 Posts

Posted - 2014-01-01 : 19:51:43

many thanks

MrSmallTime
Starting Member

32 Posts

Posted - 2014-01-01 : 20:07:32

Just tested and it worked perfectly. Now you've shown me the solution, I'm wondering why I didn't think of it myself, it seems so blasted obvious. Many thanks again

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-01-02 : 03:30:01

It must the worst suggestion ever.
Try @StartDate '22/1/2014' and @EndDate '1/3/2014'

1) ALWAYS use date datatyp and it will never fail.
2) If you pass varchar values to date parameters, use ISO dateformat for hasslefree operation.

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Kristen
Test

22859 Posts

Posted - 2014-01-02 : 07:38:17

[code]SET dateformat DMY[/code]

is another option. The implicit conversion will then use that.

However, I now only EVER send dates in unambiguous format "yyyymmdd" "yyyymmdd hh:mm:ss.mmm" or "yyyy-mm-ddThh:mm:ss:mmm" (I convert them to that in the APP before sending to SQL) as there is WAY too much that can go wrong in SQL's implicit conversion. Language, Locale, Server settings ... all sorts ... come into play in choosing implicit conversions.

Another option, if you can, is to put the date into a DATE datatype in your application language, and then include that variable in a parametrised query to SQL, then the conversion will be type-safe.

Of course if you wrap EVERY SINGLE date string in a suitable SQL CONVERT function you will be fine. Don't ever forget one though ... and then test only with dates that happen to be safe for the implicit conversion - e.g. days <= 12 for example.

MrSmallTime
Starting Member

32 Posts

Posted - 2014-01-03 : 11:27:49

Thanks All

Thank you for reminding me about implicit conversions, a while back I swore blind I'd never do this but then slipped-up. Point very well taken

I did ponder doing the conversion at the font-end but was trying to side with portability so wouldn't have to do this on each different app that will be built later on (bearing in mind users now want front ends for their various smartphones, tablets and the like)

Think I'll return to the drawing board with this on Monday with a fresh mind.

Many thanks for everyone's time and advice

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2014-01-06 : 01:58:09

You may also need to read this to know why you should use unambiguous date formats http://beyondrelational.com/modules/2/blogs/70/posts/10898/understanding-datetime-column-part-ii.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources