Solved

MS SQL Server -- substring DateTime ?

Posted on 2012-03-15
5
852 Views
Last Modified: 2012-03-16
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
Comment
Question by:finance_teacher
  • 3
5 Comments
 
LVL 7

Assisted Solution

by:micropc1
micropc1 earned 100 total points
ID: 37725398
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
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 37725432
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
 

Author Comment

by:finance_teacher
ID: 37726131
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
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 37726569
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
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 400 total points
ID: 37726576
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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

861 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question