Problems with date formats

Question

Post reply

Problems with date formats

AmberArmy

SSC Enthusiast

Points: 113
More actions
October 8, 2021 at 2:29 pm

#3938865

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

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

aadcock SSC Veteran Points: 222 More actions · Answer 1

Change the date format in the convert from 120 to 23.

Jeff Moden SSC Guru Points: 1004591 More actions · Answer 2

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

AmberArmy SSC Enthusiast Points: 113 More actions · Answer 3

AmberArmy

SSC Enthusiast

Points: 113

October 8, 2021 at 3:17 pm

#3938894

Many thanks for your help. I love forums!

Cheers

frederico_fonseca SSCoach Points: 16215 More actions · Answer 4

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

Jeffrey Williams SSC Guru Points: 90346 More actions · Answer 5

frederico_fonseca wrote:

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

Carmon28 Grasshopper Points: 11 More actions · Answer 6

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.

Target Card Balance Now

This reply was modified 3 years, 7 months ago by Carmon28.

Problems with date formats

Cookies on SQLServerCentral