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]
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
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)
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?)
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?
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.