none
SET DATEFORMAT within a stored procedure

    Question

  • Hi,

    I need to use SET DATEFORMAT statement, but I want to do it within a stored procedure. I should run a dozen of SPs sequentially (from MS Access), so these will be different seesions. Now I have to use the SET statement every time before executing each SP.

    If I specify SET DATEFORMAT dmy within a SP itself:

    BEGIN
     SET NOCOUNT ON;
     SET DATEFORMAT dmy;
    --here is the SP itself
    END
    
    

    it doesn't work. Because I can see the correct result for mdy format instead of specified one.

    Thanks in advance!


    Andrey V Artemyev | Saint-Petersburg, Russia
    Russian blog artemyev.biztoolbox.ru
    English blog biztoolblogs.tumblr.com
    Monday, August 15, 2011 6:29 AM

Answers

  • Instead of setting the dateformat I suggest to use a format the SQL Server understand in the most cases correct, like yyymmdd or yyyy-mm-dd or use an implicit conversion with the indication of the date styles

    Monday, August 15, 2011 6:49 AM
  • There is nothing in your procedure that depends on the dateformat setting. You pass the value as datetime, and that's that. The DATEFORMAT setting applies when character values are converted to datetime, but that happens when you invoke the procedure. Had you passed a character value and then converted it in the procedure, it would have been different. On the other hand, that is absolutely nothing you should do.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Monday, August 15, 2011 9:28 AM

All replies

  • Instead of setting the dateformat I suggest to use a format the SQL Server understand in the most cases correct, like yyymmdd or yyyy-mm-dd or use an implicit conversion with the indication of the date styles

    Monday, August 15, 2011 6:49 AM
  • In the procedure below SET DATEFORMAT appears to have effect:

    create procedure xyz as
    set dateformat dmy
    select convert(datetime, '9/10/1911')
    set dateformat dmy
    set dateformat mdy
    select convert(datetime, '9/10/1911')
    go
    exec xyz
    go
    drop procedure xyz

    But I don't know how your code looks like.

    But as Christa says, this is not a good solution. Better is to use a dateformat that SQL Server always understands. Even better is to call the procedure through RPC and pass parameters through the parameter object, in which case datetime values are passed as binary.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Monday, August 15, 2011 7:10 AM
  • Christa,

    thanks for the tip. It's even more comfortable and I'm going to use this approach. I just get confused with all these date/time features in different systems. I'll definetely mark it as an answer after getting an understanding of why SET statements doesn't work in my case.

    Erland,

    it would be good to understand why this doesn't work for me. Here is an example of my SP:

    CREATE PROCEDURE ST_s_supcc 
    	@d datetime, 
    	@branch varchar(3) = '999'
    AS
    BEGIN
    	SET NOCOUNT ON;
    	SET DATEFORMAT dmy;
    	SELECT tEmployees_dic.EmployeeFIO, CONVERT(varchar, [PTime], 104) AS DateOnly, 
    	Count(tCC.ID) AS TotalSupcc
    	FROM tSessions INNER JOIN tRoot ON tSessions.SID = tRoot.SID
    	INNER JOIN tCC ON tCC.PID = tRoot.PID
    	INNER JOIN tEmployees_dic ON tEmployees_dic.EmployeeID = tSessions.EmployeeID
    	WHERE tRoot.PTime Between @d And dateadd(month,1,@d) AND tSessions.BranchID=@branch 
    	AND tCC.OperID='05' AND tSessions.PositionID=1
    	GROUP BY tEmployees_dic.EmployeeFIO, CONVERT(varchar, [PTime], 104)
    END
    GO
    

    However, if I execute

    st_s_supcc

    '01.05.2011', '555'

    assuming it is May, 1st. I get a result with DateOnly field 04.02.2011 which is February, 4th according to my CONVERT function in the SP.


    Andrey V Artemyev | Saint-Petersburg, Russia
    Russian blog artemyev.biztoolbox.ru
    English blog biztoolblogs.tumblr.com
    Monday, August 15, 2011 7:55 AM
  • There is nothing in your procedure that depends on the dateformat setting. You pass the value as datetime, and that's that. The DATEFORMAT setting applies when character values are converted to datetime, but that happens when you invoke the procedure. Had you passed a character value and then converted it in the procedure, it would have been different. On the other hand, that is absolutely nothing you should do.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Monday, August 15, 2011 9:28 AM
  • Erland,

    yes, got it! Thanks a lot for explanation. To use SET statement I should modify my SP to accept text patameter and then convert it to datetime, where the DATEFORMAT exactly affects.

    CREATE PROCEDURE ST_s_supcc 
    	@d varchar(10), 
    	@branch varchar(3) = '999'
    AS
    BEGIN
    	SET NOCOUNT ON;
    	SET DATEFORMAT dmy;
            DECLARE @dt AS datetime;
            SET @dt = CAST(@d AS datetime);
    	SELECT tEmployees_dic.EmployeeFIO, CONVERT(varchar, [PTime], 104) AS DateOnly, 
    	Count(tCC.ID) AS TotalSupcc
    	FROM tSessions INNER JOIN tRoot ON tSessions.SID = tRoot.SID
    	INNER JOIN tCC ON tCC.PID = tRoot.PID
    	INNER JOIN tEmployees_dic ON tEmployees_dic.EmployeeID = tSessions.EmployeeID
    	WHERE tRoot.PTime Between @dt And dateadd(month,1,@dt) AND tSessions.BranchID=@branch 
    	AND tCC.OperID='05' AND tSessions.PositionID=1
    	GROUP BY tEmployees_dic.EmployeeFIO, CONVERT(varchar, [PTime], 104)
    END
    GO
    
    

    But I won't use it, there are extra hither-thither conversions which finally make no sense versus using yyyy-mm-dd format, which is really better.
    Thanks all again!


    Andrey V Artemyev | Saint-Petersburg, Russia
    Russian blog artemyev.biztoolbox.ru
    English blog biztoolblogs.tumblr.com
    Monday, August 15, 2011 10:01 AM