Modify xml path type to a simple solution

21

Given that the column is called "strValue" presumably its a text representation of a date (or DateTime maybe)

For DateTime anything OTHER than 'yyyymmdd hh:mm:ss' (or optionally the ISO 'yyyy-mm-ddThh:mm:ss', and for date also 'yyyy-mm-dd' is open to all the parsing rules which are influenced by, for example, the Language of the currently connected user ...

I would try my earlier query to see what you get:

select Configure_strValue
       , DATEADD(Day, DATEDIFF(Day, 0, Configure_strValue), 0) AS [StartOfDay]
       , DATEADD(Day, DATEDIFF(Day, 0, Configure_strValue)+1, 0) AS [StartOfNextDay]
       , CONVERT(Date, Configure_strValue) AS [ConvertToDate] -- << ADDED THIS
FROM tblConfigure 
WHERE Configure_strName = 'StartTimeOfDay'

if the string-format is suitable for DATETIME then you might need to try

       , CONVERT(Date, CONVERT(DateTime, Configure_strValue)) AS [ConvertToDate2]
22

Hi. the Results are:
Configure_strValue StartOfDay StartOfNextDay ConvertToDate
06:00AM 1900-01-01 00:00:00.000 1900-01-02 00:00:00.000 1900-01-01

23

cannot be converted to DATE. It will need some other string manipulation to convert that to a suitable DATE or DATETIME. Its not ideal as a TIME format ... "06:00" would be better, but it will still need string-manipulation converting it to a DateTime.

Presumably you want to append that Config Time to your @dtmDate = '20170215'. Both these work fine for me, the first is "standard" and will always be parsed unambiguously, I don't know if the second one will for all locale settings

SELECT CONVERT(Datetime, '20170215 06:00')
SELECT CONVERT(Datetime, '20170215 06:00AM')
24

Hi.
But isn't this just as the beginning as we use cast again?
anyhow the conversion must be done in the time - hour, so something like this (that does not work but...)
select dateadd(hour, CONVERT(datetime, CONVERT(DateTime, '06:00AM')),0)

25

What are you actually wanting to filter on?

I get that your CONFIG setting is 6AM, that's OK.

So is it, for example, something like: Start with Yesterday at 6AM and finish at Today at 5:59AM?

Or something else?

26

Yes it gets the business day that is from 6 to 6 .
So it convert a day that do not fall into the 6 to 6 rule.
That's about it.

27

This perhaps?

if that looks OK then change it to use

SELECT CONVERT(Datetime, @dtmDate + ' ' + Configure_strValue)

or whatever date manipulation you want.

28

Hi.
Sorry maybe I can't explain it correctly.
The function will take a datetime - checks the 6 to 6 rule and return.
Actually we have 2 functions. On that will get rid of the hours and minutes (set to 00:00) and the other will leave them be.

So:

ALTER FUNCTION [dbo].[fnGetBizDateTime] (@dtmDate as datetime) RETURNS datetime as
	BEGIN
		DECLARE @dtmBizDate datetime,
		@intTransDateTime     	int,
	 	@intStartTimeOfDay	int
	
		SET @intTransDateTime = DATEPART(hh, @dtmDate)
		/* This statement will get rid of all the hours and minuits of the @TransDate */
		SET @dtmDate = CONVERT(DATETIME,CONVERT(VARCHAR(10),@dtmDate,20))
		
		SELECT @intStartTimeOfDay = SUBSTRING(Configure_strValue,1,2) 
		FROM tblConfigure 
		WHERE Configure_strName = 'StartTimeOfDay'
						
		IF @intTransDateTime >= @intStartTimeOfDay
		    BEGIN	
			/* Keep the passes date as same only add the @StartTimeOfDay to date */
			SET @dtmBizDate =(DATEADD(hh, @intStartTimeOfDay, @dtmDate))
		    END
		ELSE
		    BEGIN
		        SET @dtmBizDate  = (DATEADD(dd, (-1),@dtmDate))
			SET @dtmBizDate  =(DATEADD(hh, @intStartTimeOfDay, @dtmBizDate))
		    END			
		RETURN @dtmBizDate
	END

and

ALTER FUNCTION [dbo].[fnGetBizDateOnly] (@dtmDate as datetime) RETURNS datetime as
	BEGIN
		DECLARE @dtmBizDate datetime,
		@intTransDateTime     	int,
	 	@intStartTimeOfDay	int
	
		SET @intTransDateTime = DATEPART(hh, @dtmDate)
		/* This statement will get rid of all the hours and minuits of the @TransDate */
		SET @dtmDate = CONVERT(DATETIME,CONVERT(VARCHAR(10),@dtmDate,20))
		
		SELECT @intStartTimeOfDay = SUBSTRING(Configure_strValue,1,2) 
		FROM tblConfigure 
		WHERE Configure_strName = 'StartTimeOfDay'
						
		IF @intTransDateTime >= @intStartTimeOfDay 
			BEGIN
				SET @dtmBizDate = @dtmDate
			END
		ELSE
			BEGIN
				SET @dtmBizDate  = (DATEADD(dd, (-1),@dtmDate))
				
			END
		RETURN @dtmBizDate
	END

as you can see intStartTimeOfDay is an integer that will later be used.
Now, I'm sorry but I cannot comprehend the select you wrote. Where should it be added to?
I can't add it to the "FROM tblConfigure" as the value is int over there.

I have no problem with the function as it works as it supposed to but I would like to understand how would it be made "better" and what the benefit would be (as we are using "convert" on the last piece of code, so aren't we create a non sargable query?)

Thanks again.

29

No.... that's not actually the answer. That's a user request. The question of usage still remains. Why does the user think they need to see it this way and what are they going to use it for? If a 100 seats were involved, do you think it will really be useful to the user to see it that way? Maybe... maybe not. There might be a better way better way for the user to see things. For example, is the reason why the user wants to see things in sorted order so that they can determine the range of seats, see missing seats, make it easier to color in a seating chart or what?

30

Hi.
It's an 8 seats max the user can see.
They request is to see it ordered. They can't just see A1:A3:H:4:H8:D2:A4:D1:A2 . It looks ugly.
Yes supposedly it makes no matter for the Inner SQL selection but we can't present that to the end used.
The sorting is done on both the app an the SQL (different receipts in each one) so we try to simulate the app behavior.
Thanks.