Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 919
  • Last Modified:

MS SQL Server -- substring DateTime ?

Below works, updating the value
to 2012-03-15 09:17:24.910

How can I change so it always
keeps the end as "02:00:00.000" ?

update [ReportServer].[dbo].[Schedule]
--set [StartDate] = '2012-03-30' + substring([StartDate],11,13)
set [StartDate] = CURRENT_TIMESTAMP,
    [NextRunTime] = CURRENT_TIMESTAMP
where [Name] like 'EOM - 3:30a%'
0
finance_teacher
Asked:
finance_teacher
  • 3
2 Solutions
 
micropc1Commented:
Try...

UPDATE [ReportServer].[dbo].[Schedule]
SET StartDate = CAST(CAST(YEAR(GETDATE()) AS varchar) + '-' + CAST(MONTH(GETDATE()) AS varchar) + '-' + CAST(DAY(GETDATE()) AS varchar) + ' 02:00' AS DATETIME)
WHERE [Name] LIKE 'EOM - 3:30a%'

Open in new window

0
 
Scott PletcherSenior DBACommented:
UPDATE [ReportServer].[dbo].[Schedule]
SET
    [StartDate] = CONVERT(char(8), CURRENT_TIMESTAMP, 112) + ' 02:00:00.000',
    [NextRunTime] = CONVERT(char(8), CURRENT_TIMESTAMP, 112) + ' 02:00:00.000'
WHERE
    [Name] LIKE 'EOM - 3:30a%'
0
 
finance_teacherAuthor Commented:
Below works, but how can I get the "substring"
to work since I have 50+ updates and want to
use a substring of the original StartDate value without
getting an Argument data type datetime is invalid for
argument 1 of substring function ERROR ?
------------------------------------------------------------------------------
UPDATE [ReportServer].[dbo].[Schedule]
SET StartDate =
         CAST(
                 CAST('2012-03-19' AS varchar) +
                 ' 03:30' AS DATETIME
                          --substring([StartDate],11,13) AS DATETIME
             )
WHERE [Name] LIKE 'EOM - 3:30a%'
0
 
Scott PletcherSenior DBACommented:
Oh, OK, you don't always want 02:00:00.000, you want to retain the original time --
right?.

Btw, the format YYYYMMDD is *universal* in SQL Server and *always* works, but YYYY-MM-DD is not can fail under certain settings.


UPDATE [ReportServer].[dbo].[Schedule]
SET StartDate =
         CAST(
                 '20120319 ' + CONVERT(varchar(20), startDate, 114)
             )
WHERE [Name] LIKE 'EOM - 3:30a%'


SQL will automatically convert the varchar value to datetime if/when needed.
0
 
Scott PletcherSenior DBACommented:
If you just need to adjust the number of days, and keep exactly the same time, this would be faster:

SET StartDate = DATEADD(DAY, DATEDIFF(DAY, StartDate, '20120319'), StartDate)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now