• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 832
  • Last Modified:

T-SQL + ASP.NET Date formats

Hello,

I am writing some ASP.NET pages with SQL Server 2005 for a back end.  On several of these pages I use the calendar to select a date and pass that date either directly into a query or into a stored procedure.  The catch is that my SQL Server 2005 is US formatted and my users are French (mm/dd/yy vs. dd/mm/yy).  If it is a query I use the Convert(NVarChar, [ColumnName], 103) = @DateSelectedFromCalendar and it works fine.  However, if it is a stored procedure it is not accepting the @DateSelected.

Here is an example of some T-SQL that gives an error but runs successfully anyway.....  As you can see below it threw up an error (out of range date time value) but then went on to INSERT, SELECT, and DELETE with no problems.  When I pass @DateSelected to a stored procedure via an ASP.NET page it gives an error (see pic).  How can I use the selection calendar to pass a European date to a us database SP?

Thanks!


Simple example of the problem.....

Declare @DateSelected DateTime

SET @DateSelected = '13/1/2009'

INSERT INTO Mortality
Values
(
            403612595,
            Convert(NVarChar, @DateSelected, 103),
            11,
            Null
)
GO
SELECT
            *
FROM
            [Mortality]
GO
DELETE FROM Mortality WHERE [Bird ID] = 403612595


--------------------------------------------------------RESULTS-----------------------------------------------------------------

Msg 242, Level 16, State 3, Line 3
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

(1 row(s) affected)

(12 row(s) affected)

(1 row(s) affected)




Dim DateSelected As Date
 
    If Calendar2.SelectedDates.Count = 1 Then
        DateSelecttxt.Text = ""
        DateSelected = Calendar2.SelectedDate
    Else
        DateSelecttxt.Text = "Missing Value: Date Inserted / Removed"
        Exit Sub
    End If
 
        Dim sqlconn As New SqlConnection(ConfigurationManager.ConnectionStrings("HubbardRDSqlClient").ConnectionString)
        Dim cmd As New SqlCommand("MortalityInsert", sqlconn)
 
        cmd.CommandType = Data.CommandType.StoredProcedure
 
        cmd.Parameters.Add(New SqlParameter("@Line", Data.SqlDbType.VarChar, 5))
        cmd.Parameters("@Line").Value = LineList.SelectedItem.Text
 
        cmd.Parameters.Add(New SqlParameter("@WB", Data.SqlDbType.int))
        cmd.Parameters("@WB").Value = WingBandNumberIn.text
 
        cmd.Parameters.Add(New SqlParameter("@DateSelected", Data.SqlDbType.VarChar, 10))
        cmd.Parameters("@DateSelected").Value = DateSelected
 
        cmd.Parameters.Add(New SqlParameter("@Code", Data.SqlDbType.int))
        cmd.Parameters("@Code").Value = MortalityCodeList.SelectedItem.Text
 
        cmd.Parameters.Add(New SqlParameter("@Loc", Data.SqlDbType.int))
        cmd.Parameters("@Loc").Value = HouseList.SelectedValue
 
        cmd.Parameters.Add(New SqlParameter("@HatchYear", Data.SqlDbType.int))
        cmd.Parameters("@HatchYear").Value = HatchYearList.SelectedItem.Text
 
        sqlconn.Open()
        cmd.ExecuteNonQuery()
        sqlconn.Close()
 
 
 
-------------------------------------------------------------------------------------------------------------------
 
/****** Object:  StoredProcedure [dbo].[MortalityInsert]    Script Date: 02/13/2009 14:50:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[MortalityInsert]
 
@Line NVarChar(5),
@WB int,
@DateSelected DateTime,
@Code int,
@Loc int,
@HatchYear int
 
AS
 
INSERT INTO Mortality
(
		[Bird ID],
		[Date],
		[Mortality Code]
)
SELECT	
		wf.[Bird ID], 
		Convert(NVarChar, @DateSelected, 103), 
		@Code 
FROM 
		WingbandFlock As wf 
INNER JOIN 
		[House Assignment Details] As had 
ON 
		had.[Bird ID] = wf.[Bird ID] 
WHERE 
		wf.[Line Number] = @Line 
AND		wf.[Wing Band Number] = @WB 
AND		had.[Location ID] = @Loc 
AND		DatePart("yy", wf.[Hatch Date]) = @HatchYear
GROUP BY
		wf.[Bird ID]
--------------------
UPDATE [Cage Assignment] Set [Date Removed] = Convert(NVarChar, @DateSelected, 103) WHERE [Bird ID] = (SELECT	wf.[Bird ID] FROM WingbandFlock As wf INNER JOIN [House Assignment Details] As had ON had.[Bird ID] = wf.[Bird ID] WHERE wf.[Line Number] = @Line AND wf.[Wing Band Number] = @WB AND had.[Location ID] = @Loc AND DatePart("yy", wf.[Hatch Date]) = @HatchYear GROUP BY wf.[Bird ID]) AND [Date Removed] Is Null
--------------------
UPDATE [House Assignment] Set [Date Removed] = Convert(NVarChar, @DateSelected, 103) WHERE [Bird ID] = (SELECT	wf.[Bird ID] FROM WingbandFlock As wf INNER JOIN [House Assignment Details] As had ON had.[Bird ID] = wf.[Bird ID] WHERE wf.[Line Number] = @Line AND wf.[Wing Band Number] = @WB AND had.[Location ID] = @Loc AND DatePart("yy", wf.[Hatch Date]) = @HatchYear GROUP BY wf.[Bird ID]) AND [Date Removed] Is Null

Open in new window

untitled222.bmp
0
Navicerts
Asked:
Navicerts
  • 8
  • 5
  • 2
  • +1
3 Solutions
 
nkhelashviliCommented:
change  DateSelected = Calendar2.SelectedDate  to

DateSelected = Calendar2.SelectedDate.ToString("yyyyMMdd");
0
 
nkhelashviliCommented:
oops DateSelected   is datetime?
0
 
Patrick MatthewsCommented:
Hello Navicerts,

I would recommend that you pass the date literal to SQL Server as yyyy-mm-dd, thus side-stepping the
whole US vs the rest of the world thing for whether you meant mm/dd/yy or dd/mm/yy.

yyyy-mm-dd is never ambiguous, and as such is the best practice.

No idea how to get ASP.net to pass the date literal in such a fashion...

Regards,

Patrick
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
nkhelashviliCommented:
Change  cmd.Parameters.Add(New SqlParameter("@DateSelected", Data.SqlDbType.VarChar, 10))

to

cmd.Parameters.Add(New SqlParameter("@DateSelected", Data.SqlDbType.DateTime))
0
 
Patrick MatthewsCommented:
I see nkhelashvili has beat me to it :)
0
 
nkhelashviliCommented:
problem is that @DateSelected   is datetime in stored procedure, and you are sending as VARCHAR from .NET.

Because of this you must change it as I told in my previous post
0
 
NavicertsAuthor Commented:
@nkhelashvili

I tried changing the variable getting passed to datetime but got a different error (see pic).  Maybe I should go in the other direction and change everything to NVarChar?

@matthewspatrick

Makes sense, I know how to do this on the database end but I'm not sure how to format the date selected from the asp.net calendar to yymmdd.  Playing around with it now...
untitled33.bmp
0
 
NavicertsAuthor Commented:
Changing evreything to NVarChar didn't seem to work for me (both the asp.net and the sql sp), I get the same error as the above post.
0
 
NavicertsAuthor Commented:
I see, even if I change everything to NVarChar I still am stuck with "Convert(NVarChar, @DateSelected, 103)" which requires a date format.
0
 
NavicertsAuthor Commented:
OK, still working on the problem but the logic has me stumped.

I changed everything to NVarChar which includes the lines below in my comment.  When I run it I get the error message "La conversion de la chaĆ®ne "20090213" en type 'Date' n'est pas valide. " AKA it is still trying to change it to a date and I don't know why considering I can run the small T-SQL in the code window with no problems?!

DateSelected = Calendar2.SelectedDate.ToString("yyyyMMdd")

cmd.Parameters.Add(New SqlParameter("@DateSelected", Data.SqlDbType.VarChar, 10))
cmd.Parameters("@DateSelected").Value = DateSelected


@DateSelected NVarChar(10),

@Line NVarChar(5),
@WB int,
@DateSelected NVarChar(10),
@Code int,
@Loc int,
@HatchYear int

AS

INSERT INTO Mortality
(
            [Bird ID],
            [Date],
            [Mortality Code]
)
SELECT      
            wf.[Bird ID],
            @DateSelected,
            @Code
FROM
            WingbandFlock As wf
INNER JOIN
            [House Assignment Details] As had
ON
            had.[Bird ID] = wf.[Bird ID]
WHERE
            wf.[Line Number] = @Line
AND            wf.[Wing Band Number] = @WB
AND            had.[Location ID] = @Loc
AND            DatePart("yy", wf.[Hatch Date]) = @HatchYear


Declare @DateSelected NVarChar(10)
 
SET @DateSelected = '20090213'
 
INSERT INTO Mortality
Values
(
            403612595,
            @DateSelected,
            11,
            Null
)
GO
SELECT
            *
FROM
            [Mortality]
GO
DELETE FROM Mortality WHERE [Bird ID] = 403612595

Open in new window

0
 
NavicertsAuthor Commented:
It's working now.  I had to remove the Convert(NVarChar, @DateSelected, 103) from the SP and instead just use @DateSelected.

I am not sure WHY this works as I would expect it to interpret the date incorrectly on the SQL Server side of things considering I am passing a DDMMYYYY date into a MMDDYYY system?  Even if I pass in the date 12/2/2009 SQL Server is interpreting it as DD/MM/YYYY (it could go either way).

In any case if someone can explain this I would appreciate it. I'll close the question regardless before I go home for the day.

thanks to all.
0
 
nkhelashviliCommented:
You must change only in your .net code and only single line, don`t touch(!)  to stored procedure old text!
Change  
cmd.Parameters.Add(New SqlParameter("@DateSelected", Data.SqlDbType.VarChar, 10))

to

cmd.Parameters.Add(New SqlParameter("@DateSelected", Data.SqlDbType.DateTime))
and it will work.
yyyyMMdd  is the format which sql server understands, so feel free using it. And don`t change  yyyyMMdd format with yyyymmdd format, as  mm is minutes and not month!
0
 
NavicertsAuthor Commented:
Yes, thats what I ended up doing but I am still confused on something.  To get the date variable I just use "DateSelected = Calendar2.SelectedDate"

Then for the SP variable I use "cmd.Parameters.Add(New SqlParameter("@DateSelected", Data.SqlDbType.DateTime))", just as you suggested.

In my SP I use "@DateSelected DateTime" to declare the variable and....

SELECT      
            wf.[Bird ID],
            @DateSelected,
            @Code
FROM

The reason it did not work the first time I did as you suggested is because I had the following in the SP and I needed to remove the Convert statement.

SELECT      
            wf.[Bird ID],
            Convert(NVarChar, @DateSelected, 101),
            @Code
FROM
0
 
NavicertsAuthor Commented:
As far as I know I am passing the date in as "13/02/2009" and then using that same thing in my INSERT statement.

Somehow SQL Server knows that I want a US date format?  Or maybe it is converting the date to yyyyMMdd behind the scenes?
0
 
MaxOvrdrv2Commented:
you have a very simple problem, with a very simple solution... although i understand the head banging because i<ve been through this before and kinda freaked...

there is a very useful function in T.SQL to set the date format of ANY parameter, AND SQL Query Field it returns so that they always match:

SET DATEFORMAT { format | @format_var }

If you follow this link, it will give you examples on how to use it in your Stored Procedure to format the parameters properly, but mainly, it's a simple call before executing your main code:
http://technet.microsoft.com/en-us/library/ms189491.aspx

here's a small example:
To Set date format to day/month/year.
SET DATEFORMAT dmy;
GO
DECLARE @datevar datetime2 = '31/12/2008 09:01:01.1234567';
SELECT @datevar;
GO
the Result will be: 2008-12-31 09:01:01.123

and voila! with that, you should be able to format your passed parameters to the proper format to match your fields... and the best thing is, you don't even have to convert or bother with anything, the SET DATEFORMAT handles both incoming, and outgoing (pull from DB) calls... example, i could go:
SET DATEFORMAT dmy;
GO
DECLARE @datevar1 datetime1 = '01-Jan-09'
DECLARE @datevar2 datetime2 = '31/12/2008 09:01:01.1234567';
SELECT @datevar1, @datevar2 ;

and the result would be: "2009-01-01 12:00:00.00" , "2008-12-31 09:01:01.123"

as you can see, it's now super easy to compare my dates.

Cheers!

0
 
NavicertsAuthor Commented:
For anyone using this as a reference I had to make the change nkhelashvili suggested and it was also needed to change "Convert(NVarChar, @DateSelected, 103)" to "@DateSelected" in my SP.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 8
  • 5
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now