May 11, 2022 at 12:33 am
I work on sql server 2017 i need to validate user input of temp table
if formate of created date not YYYY-MM-DD then reject by update status field of temp table to be Notvalid
and if formate of created date is YYYY-MM-DD then update status by Valid
CREATE TABLE #TempPC
(
[ID] INT IDENTITY ,
CreatedDate varchar(12),
status varchar(200)
)
insert into #TempPC
(
CreatedDate
)
select '2022-05-09'
union
select '12-04-2022'
Expected result
CreatedDate Status
2022-05-09 Valid
12-04-2022 NotValid
May 11, 2022 at 4:32 am
You cannot prove that a date like 2022-05-09 is actually in the YYYY-MM-DD format. For example, in SQL Server in the English Language, that will be YYYY-MM-DD but, if it comes from someone that has a server in the French language, it could be in the YYYY-DD-MM format. The thing is, since both numbers for MM and DD are less than 13, you simply don't know unless you've made a prior agreement with the provider of the data.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 11, 2022 at 5:27 pm
You cannot prove that a date like 2022-05-09 is actually in the YYYY-MM-DD format. For example, in SQL Server in the English Language, that will be YYYY-MM-DD but, if it comes from someone that has a server in the French language, it could be in the YYYY-DD-MM format. The thing is, since both numbers for MM and DD are less than 13, you simply don't know unless you've made a prior agreement with the provider of the data.
Even if you have a prior agreement - and the provider is sending the data in YYYY-MM-DD format you could still have problems depending on the data type and language. If you are using the DATE data type then SQL Server will evaluate the string as YYYY-MM-DD, but if you are using the DATETIME data type and the language is French (for example) then it will be evaluated as YYYY-DD-MM.
The simplest way to ensure a valid date is passed into a segment of code is to define the column with the correct data type. Allowing a string to be passed in that needs to be converted/evaluated is just asking for data integrity issues and failures. In the example given - both examples are valid dates and would be converted. However, if the language setting is French and the data type is DATE the dates could be incorrect if the expectation is that 2022-05-09 is September 5th, 2022 and not May 9th, 2022 - and 02-04-2022 would be evaluated as April 4th, 2022 (correct for that language). If the data type is DATETIME - then the dates would be evaluated as September 5th, 2022 and April 4th, 2022 for a French language system - May 9th, 2022 and February 4th, 2022 for US English.
If this is code that will be used directly by another individual - and you want to ensure they are passing it correctly, then you should use YYYYMMDD (no dashes), set the input to the correct data type (DATE, DATETIME, ...). Document the code to inform the user to enter it correctly.
If this is intended to validate the source data - then that source data either needs to define it correctly or you need that contract with the provider to make sure it is sent in a specifically defined format.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 11, 2022 at 6:29 pm
I fully agree with the comments made by others, but if the OP would like to try anyway, this will work with the test data provided:
select
CreatedDate,
case when try_convert(date,CreatedDate,23) is not null then 'Valid' else 'NotValid' end as "Status"
from #TempPC
May 12, 2022 at 3:39 pm
Even if you have a prior agreement - and the provider is sending the data in YYYY-MM-DD format you could still have problems depending on the data type and language. If you are using the DATE data type then SQL Server will evaluate the string as YYYY-MM-DD, but if you are using the DATETIME data type and the language is French (for example) then it will be evaluated as YYYY-DD-MM.
Yep... that's what I'm getting at. "It Depends".
The "simplest" way is to stop sending dates and times in a character based format. Ah, but humans need to read stuff that was supposedly written for ingestion by computers and that messes everything up. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
May 12, 2022 at 3:47 pm
I fully agree with the comments made by others, but if the OP would like to try anyway, this will work with the test data provided:
select
CreatedDate,
case when try_convert(date,CreatedDate,23) is not null then 'Valid' else 'NotValid' end as "Status"
from #TempPC
I totally understand your good intentions... let's hope it doesn't have a negative effect someday in the future. The other issue I have with this is why not just store the valid date if it's valid and Null if it's not? Marking a character based column with a status is a bit having both an "IsActive" and an "ActivatedOn" column. It's just extra work and confusion in the table.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 13, 2022 at 7:24 pm
Jeffrey Williams wrote:Even if you have a prior agreement - and the provider is sending the data in YYYY-MM-DD format you could still have problems depending on the data type and language. If you are using the DATE data type then SQL Server will evaluate the string as YYYY-MM-DD, but if you are using the DATETIME data type and the language is French (for example) then it will be evaluated as YYYY-DD-MM.
Yep... that's what I'm getting at. "It Depends".
The "simplest" way is to stop sending dates and times in a character based format. Ah, but humans need to read stuff that was supposedly written for ingestion by computers and that messes everything up. 😀
How about this: 0x0B43EF
Or this: 2459713
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 14, 2022 at 7:37 pm
Those would work although those particular dates are a wee bit into the future assuming those are the little-endian count of days since the first of January 1900. One of the big disappointments I have is how they screwed us by NOT making the newer temporal datatypes so that they could easily work with non-temporal datatypes and ignored a lot of the ANSI/ISO requirements like EndDateTime-StartDateTime = Duration and StartDateTime + Duration = EndDateTime.
That also brings up a bit of a rant on my part...
They tried to help after they made that mistake by creating a DATEDIFF_BIG but, in the same stroke, they didn't make DATEADD_BIG. These are the same people that thought creating EOMonth without creating and FOMonth was a good idea and still haven't blessed the on-prem version of STRING_SPLIT() with an ordinal position being returned.
They also haven't created a temporal duration function and so you end up with code like they published as an example in the DATEDIFF_BIG documentation and it doesn't even use DATEDIFF_BIG.
https://docs.microsoft.com/en-us/sql/t-sql/functions/datediff-big-transact-sql
DECLARE @date1 DATETIME2, @date2 DATETIME2, @result VARCHAR(100)
DECLARE @years BIGINT, @months BIGINT, @days BIGINT, @hours BIGINT, @minutes BIGINT, @seconds BIGINT, @milliseconds BIGINT
SET @date1 = '0001-01-01 00:00:00.00000000'
SET @date2 = '2018-12-12 07:08:01.12345678'
SELECT @years = DATEDIFF(yy, @date1, @date2)
IF DATEADD(yy, -@years, @date2) < @date1
SELECT @years = @years-1
SET @date2 = DATEADD(yy, -@years, @date2)
SELECT @months = DATEDIFF(mm, @date1, @date2)
IF DATEADD(mm, -@months, @date2) < @date1
SELECT @months=@months-1
SET @date2= DATEADD(mm, -@months, @date2)
SELECT @days=DATEDIFF(dd, @date1, @date2)
IF DATEADD(dd, -@days, @date2) < @date1
SELECT @days=@days-1
SET @date2= DATEADD(dd, -@days, @date2)
SELECT @hours=DATEDIFF(hh, @date1, @date2)
IF DATEADD(hh, -@hours, @date2) < @date1
SELECT @hours=@hours-1
SET @date2= DATEADD(hh, -@hours, @date2)
SELECT @minutes=DATEDIFF(mi, @date1, @date2)
IF DATEADD(mi, -@minutes, @date2) < @date1
SELECT @minutes=@minutes-1
SET @date2= DATEADD(mi, -@minutes, @date2)
SELECT @seconds=DATEDIFF(s, @date1, @date2)
IF DATEADD(s, -@seconds, @date2) < @date1
SELECT @seconds=@seconds-1
SET @date2= DATEADD(s, -@seconds, @date2)
SELECT @milliseconds=DATEDIFF(ms, @date1, @date2)
SELECT @result= ISNULL(CAST(NULLIF(@years,0) AS VARCHAR(10)) + ' years,','')
+ ISNULL(' ' + CAST(NULLIF(@months,0) AS VARCHAR(10)) + ' months,','')
+ ISNULL(' ' + CAST(NULLIF(@days,0) AS VARCHAR(10)) + ' days,','')
+ ISNULL(' ' + CAST(NULLIF(@hours,0) AS VARCHAR(10)) + ' hours,','')
+ ISNULL(' ' + CAST(@minutes AS VARCHAR(10)) + ' minutes and','')
+ ISNULL(' ' + CAST(@seconds AS VARCHAR(10))
+ CASE WHEN @milliseconds > 0 THEN '.' + CAST(@milliseconds AS VARCHAR(10))
ELSE '' END
+ ' seconds','')
SELECT @result
Yeah... I know... I'm just ranting. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
May 14, 2022 at 8:15 pm
Those would work although those particular dates are a wee bit into the future assuming those are the little-endian count of days since the first of January 1900. One of the big disappointments I have is how they screwed us by NOT making the newer temporal datatypes so that they could easily work with non-temporal datatypes and ignored a lot of the ANSI/ISO requirements like EndDateTime-StartDateTime = Duration and StartDateTime + Duration = EndDateTime.
The hex value is 2022-05-13 based on the zero date being 0001-01-01. The numeric date is also 2022-05-13 based on the Julian Day Number where zero date is January 1, 4713BC.
I am well aware of your rant - and agree to some of it. My particular issue is that the standards committee force down on everybody this idea that an external date should be the representative of a date and only allowed for using the Gregorian calendar. While at the same time the Julian Day Number was already being used and could have easily been chosen to represent a date.
Forget about external representation - a date is really just an incremental counter of the number of times the planet has rotated. If the standards were defined to represent a date in that manner, then every system built could store it any way it wanted as long as they could convert to/from that incremental counter. In fact, every system eventually ended up doing that anyways - but since there was no standard they all chose different starting points.
Even with that - having different starting points just means an offset is needed to convert to/from the standard incremental date which would be so much easier to code.
Instead, what we have now are date strings that can be interpreted differently based on language and a 'standard' that tells us to use YYYY-MM-DD (or YYYYMMDD) to represent a date - that is only valid for the Gregorian calendar.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 14, 2022 at 9:25 pm
Heh... I was wondering what you used for a base date in your example. 😀
Totally agreed on the rest. My current issue is that the increments are clicking by way too fast for me, no matter the base date. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply