How to validate if a DateTime includes time in SQL Server

问题: I have a stored procedure that takes a datetime parameter. I need to determine if the given datetime parameter includes the time. Now, DATEPART(hour, @datetimeValue) = 0...

问题:

I have a stored procedure that takes a datetime parameter. I need to determine if the given datetime parameter includes the time.

Now, DATEPART(hour, @datetimeValue) = 0 doesn't work in my case because the datetime can be provided as 3/14/2019 0:00 which refers to as 12 AM and valid.

Return true if input is :

'3/14/2019 0:00'
'3/14/2019 15:00'

Return false only if input has no time :

'3/14/2019'

Thanks everyone for their input. It looks there is no solution to this other than changing the store proc parameter to varchar.


回答1:

The requirement is not technically possible to handle in the Stored Procedure.

The DateTime parameter in your stored procedure will always contain a time aspect which is either explicitly passed to it or defaults to midnight of the passed in date. There is no way to know if the caller explicitly passed in a time aspect or not.

You have 2 options:

  1. Change the incoming data type of the parameter to varchar and have the stored procedure parse that into a DateTime and handle validation.
  2. Make the caller handle any validation having to do with time and drop this requirement from your code.

回答2:

By default, a DATETIME includes a time, set to midnight (e.g. '00:00:00'), even if you aren't using the time portion. If your datetime's are strings, then here is a way to see if a time is part of the string (though it's a little hacky):

SELECT CASE WHEN CHARINDEX(':', '3/14/2019 0:00') > 0 THEN 1 ELSE 0 END   -- Returns 1
SELECT CASE WHEN CHARINDEX(':', '3/14/2019 15:00') > 0 THEN 1 ELSE 0 END  -- Returns 1
SELECT CASE WHEN CHARINDEX(':', '3/14/2019') > 0 THEN 1 ELSE 0 END        -- Returns 0

Basically it's just searching the string and looking to see if a colon is in the string. It meets your criteria though.


回答3:

The only solution I can think of is to ALTER your SP and use two (2) parameters instead, one is DATE and the second is TIME datatype as:

CREATE PROCEDURE HasTime(
    @MyDate DATE = NULL,
    @MyTime TIME = NULL
)
AS
BEGIN
    IF @MyTime IS NULL
        SELECT 'There is no time'
            ELSE
                SELECT 'There is time';
END;

EXEC dbo.HasTime '2019-01-01', NULL; --Or EXEC dbo.HasTime '2019-01-01';
EXEC dbo.HasTime '2019-01-01', '00:00:00';

Live Demo


回答4:

You can do a check like this, but if the time is actually midnight, it will return not valid time:

-- returns 'NOTime'
SELECT CASE WHEN CAST('1/1/2019' AS TIME) = '00:00:00.000' THEN 'NOTime' ELSE 'TIME' END

-- returns 'TIME'
SELECT CASE WHEN CAST(GetDate() AS TIME) = '00:00:00.000' THEN 'NOTime' ELSE 'TIME' END

回答5:

I would cast as date:

select (case when convert(date, @datetimeValue) = @datetimeValue then 'NoTime'
             else 'HasTime'
        end)

回答6:

The only way is to pass a varchar type to teh procedure instead of datetime otherwise we can not make difference between a date without time and a date at midnight here you can check this restriction The follwoing script illustrates that restriction:

declare @d as datetime
declare @d1 as datetime

set @d='01/01/2019'
set @d1='01/01/2019 00:00:00:00'

if @d=@d1 print 'equal' else print 'not equal'

回答7:

This is crude, but it does what you need. If its throw away, eg part of some import it might be good enough for your needs.

I am sure this approach could be improved with more understanding on how to retreive details of the current executing SQL from within the PROC, I had a quick look at using query plans etc.

You could just fail back to this check if the time is midnight this makes it more efficient

ALTER PROCEDURE Hastime(@d AS DATETIME) 
AS 
  BEGIN

      -- Is there a easier way to get from DBCC INPUTBUFFER to a SQL variable? 
      CREATE TABLE #temp 
        ( 
           spid       INT, 
           eventtype  NVARCHAR(30), 
           parameters INT, 
           eventinfo  NVARCHAR(4000) 
        )     
      INSERT INTO #temp 
                  (eventtype, 
                   parameters, 
                   eventinfo) 

      EXEC ('DBCC INPUTBUFFER(' + @@spid +') WITH NO_INFOMSGS') 

      -- Yes, we could do this better
      IF EXISTS (SELECT * 
                 FROM   #temp 
                 WHERE  eventinfo LIKE '%:%') 
        SELECT 'Time' 
      ELSE 
        SELECT 'No Time' 
  END     
go     
EXEC dbo.Hastime 
  '2000/06/01' 

go     
EXEC dbo.Hastime 
  '2000/06/01 00:00:00' 
  • 发表于 2019-03-19 09:55
  • 阅读 ( 108 )
  • 分类:sof

条评论

请先 登录 后评论
不写代码的码农
小编

篇文章

作家榜 »

  1. 小编 文章
返回顶部
部分文章转自于网络,若有侵权请联系我们删除