October 8, 2021 at 2:29 pm
I am creating a User Defined Function in which I pass in a character string representing a date and I want to add a number of days to it. The calling application controls the date format passed in - 2015-11-01 12:24:32 (I'm not actually interested in the time component)
I tried this
SELECT DATEADD(day, 7, convert(datetime,'2015-11-01 12:24:32',120))
Which does what I want and gives me
2015-11-08 12:24:32.000
However the function must return just the date component. When I wrap the result in a substring
SELECT Substring(DATEADD(day, 7, convert(datetime,'2015-11-01 12:24:32',120)),1,10)
I get an error - Argument data type datetime is invalid for argument 1 of substring function. Because , I assume it needs to be character data to substring
So I CAST it
SELECT Substring(CAST(DATEADD(day, 7, convert(datetime,'2015-11-01 12:24:32',120)) AS VARCHAR),1,11)
And it works but the format is no longer as I need it
Nov 8 2015
What am I missing????
The input format is fixed and I need to add 7 days and the UDF must return a string in the format 2021-11-08. I have tried a number of different third arguments in the convert function. Anyone point me in the right direction
Thanks
October 8, 2021 at 2:35 pm
Change the date format in the convert from 120 to 23.
October 8, 2021 at 3:03 pm
Here are a couple of ways to do it. Like aadcock says above, format 23 comes into play. Be warned though... in a lot of languages, that format (23) will produce the desired YYYY-MM-DD format BUT will produce the non-ISO format of YYYY-DD-MM in some other languages like French (for example) WHEN DATETIME IS USED (no problem with DATE datatype).
SET LANGUAGE English;
SELECT OK = DATEADD(dd,7,CONVERT(DATE,'2015-11-01 12:24:32')) --Return as a DATE datatype
,OK = CONVERT(CHAR(10),DATEADD(dd,7,CONVERT(DATE,'2015-11-01 12:24:32')),23) --Return as a CHAR(10) datatype, no language issues
,OK = CONVERT(CHAR(10),DATEADD(dd,7,CONVERT(DATETIME,'2015-11-01 12:24:32')),23) --Return as a CHAR(10) datatype, no language issues
;
SET LANGUAGE French;
SELECT OK = DATEADD(dd,7,CONVERT(DATE,'2015-11-01 12:24:32')) --Return as a DATE datatype
,OK = CONVERT(CHAR(10),DATEADD(dd,7,CONVERT(DATE,'2015-11-01 12:24:32')),23) --Return as a CHAR(10) datatype, no language issues
,Whoops = CONVERT(CHAR(10),DATEADD(dd,7,CONVERT(DATETIME,'2015-11-01 12:24:32')),23) --Return as a CHAR(10) datatype, has language issues
;
Results of above code:
--Jeff Moden
Change is inevitable... Change for the better is not.
October 8, 2021 at 3:17 pm
Many thanks for your help. I love forums!
Cheers
October 8, 2021 at 6:49 pm
just use straight convert with the correct destination type (e.g. no VARCHAR without specifying a size - bad practice!!) and with the correct format - no need for substring either.
SELECT convert(varchar(10), DATEADD(day, 7, convert(datetime,'2015-11-01 12:24:32',120)), 120)
format 120 - ODBC canonical - yyyy-mm-dd hh:mi:ss (24h)
from docs here https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-ver15
October 8, 2021 at 9:12 pm
just use straight convert with the correct destination type (e.g. no VARCHAR without specifying a size - bad practice!!) and with the correct format - no need for substring either.
SELECT convert(varchar(10), DATEADD(day, 7, convert(datetime,'2015-11-01 12:24:32',120)), 120)format 120 - ODBC canonical - yyyy-mm-dd hh:mi:ss (24h)
from docs here https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-ver15
Since the return will always be 10 characters - never less and never more - I would use char(10).
With that said, I would not do this - I would cast/convert to a date data type and return that. Let the client convert to a string if that is what is needed, but returning a string from a string after converting to a datetime just rubs me the wrong way.
If you have to - here is another option:
cast(dateadd(day, 7, cast('2015-11-01 12:24:32' as date)) as char(10))
This should not be impacted by language settings - since cast/convert to date recognizes ISO 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
October 13, 2021 at 7:36 am
Select the dates whose format your want to change, or empty cells where you want to insert dates.
Press Ctrl+1 to open the Format Cells dialog. ...
In the Format Cells window, switch to the Number tab, and select Date in the Category list.
Under Type, pick a desired date format.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply