0

This seems to be most discussed, but I could not arrive at a solution yet. I have a date format and have to add a month to it (DATEADD should not be used as my date is string initially) and finally should convert it as date. 103 format converts but it does not give seconds. Below is the code I tried

    Declare @time_info nvarchar(max)
Declare @format_time_info nvarchar(max)
Declare @time_info_final nvarchar(max)
Declare @time_detail datetime
SET @time_info='30/1/2020 20:34:16 6 300'

Select @format_time_info =  (Select REPLACE(@time_info,('/'+SUBSTRING( @time_info, 
CHARINDEX('/', @time_info) + 1, 
LEN(@time_info) - CHARINDEX('/', @time_info) - CHARINDEX('/', REVERSE(@time_info)))+'/'),
(CONCAT('/',(convert(varchar,(convert(int,(SUBSTRING( @time_info, 
CHARINDEX('/', @time_info) + 1, 
LEN(@time_info) - CHARINDEX('/', @time_info) - CHARINDEX('/', REVERSE(@time_info)))+1))))),'/')))) 

Select @time_info_final =(Select left(@format_time_info, charindex(' ', @format_time_info, charindex(' ', @format_time_info)+1)-1))
 Select @time_detail =  CONVERT(datetime, @time_info_final,103)-- Cast(@time_info_final as datetime)-- Convert(datetime,Cast(@time_info_final as datetime2),210) --
Print @time_info_final
 Print @time_detail

This throws the common exception

In this query I used 103

Select time_info_final =(Select convert(datetime,left(@format_time_info, charindex(' ', @format_time_info, charindex(' ', @format_time_info)+1)-1),103))

This is the dateformat I get from source '30/3/2020 20:34:16 6 300' or sometimes '30/03/2020 20:34:16 6 300'. So I used substring to find '/' and added date. The thing that troubles me is the seconds is always 00 ... looks 103 format changes it. Using other formats throws error while converting

2
  • 1
    What is the 6 300 at the end of the HH:MM:SS in your original date string? – BradC Sep 15 '20 at 20:07
  • What do you mean by "the common exception"? I'm getting a conversion error running your code sample. You said it works with format 103, but it doesn't. This is confusing. – Andriy M Sep 15 '20 at 22:42
2

Well, February doesn't have 30 days, so you can't naively just add 1 to the month number and expect to end up with a valid date. (Not to mention leap years, Dec-to-Jan wraparounds that should advance the year, etc.)

I think you'll find it much easier to convert to a date first, then use DATEADD. Then, if you like, convert back to a string format:

DECLARE @origstring VARCHAR(40) = '30/1/2020 20:34:16'
DECLARE @origdate DATETIME, @newdate DATETIME

SET @origdate = CONVERT(DATETIME, @origstring, 103)
SET @newdate = DATEADD(MONTH, 1, @origdate)

SELECT FORMAT(@newdate, N'MM/d/yyyy HH:mm:ss')

Your original string '30/1/2020 20:34:16 6 300' doesn't appear to be a valid date, the problem is the 6 300 at the end. If that's in your source data, just trim that off when you convert:

SET @origdate = CONVERT(DATETIME, LEFT(@origstring, 18), 103)
2
  • Thanks @BradC this is the dateformat I get from source '30/3/2020 20:34:16 6 300' or sometimes '30/03/2020 20:34:16 6 300'. So I used substring to find '/' and added date. The thing that troubles me is the seconds is always 00, the 'newdate' you used not gives seconds.... looks 103 format changes it. Using other formats throws error while converting – Vasanth Sep 16 '20 at 1:24
  • @Vasanth Yeah, those last few characters (6 300) don't make sense for a date; you've got to chop them off (see the LEFT statement at the end of my post) before SQL can successfully convert it. The "103" format tells SQL to interpret the string as DAY/MONTH/YEAR, otherwise it might see 5/2/2020 and give you May 2, instead of Feb 5. When I test my code, the resulting @newdate does successfully include the seconds. – BradC Sep 16 '20 at 13:37

Not the answer you're looking for? Browse other questions tagged or ask your own question.