CODEWITHSUNDEEP
sqlsql-serverdatedatetimeutc
MaylorTaylor
MaylorTaylor

Reputation: 5041

Converting Datetime to SQL UTC

I have a work task to update some StartDateUtc and EndDateUtc fields in the SQL database.

The Customer service person gave me the unique item Id to update (yay!) and the date it should be. Problem is, he gave me the dates in the following format:

I need to somehow convert these dates to the SQL format (2007-05-04 01:34:11.933) easily.

Is there some built-in SQL or .NET method i don't know about that can do this easily? Or better yet, a website that allows me to put in a date in a datepicker and it spits out the SQL format (that would be ideal)

Upvotes: 0

Views: 152

Answers (1)

Code Different
Code Different

Reputation: 93141

There's no easy function in SQL or .NET CLR to do what you want. Here's one way to do in pure SQL:

CREATE FUNCTION [dbo].[ConvertDate]
(
    @str    nvarchar(30)
)
RETURNS @Result TABLE 
(
    StartDate   datetime
,   EndDate     datetime
)
AS
BEGIN
    DECLARE @ThirdWhitespace    int          = CHARINDEX(' ', @str, CHARINDEX(' ', @str, CHARINDEX(' ', @str) + 1) + 1)
    DECLARE @FourthWhitespace   int          = CHARINDEX(' ', @str, @ThirdWhitespace + 1)
    DECLARE @FifthWhitespace    int          = CHARINDEX(' ', @str, @FourthWhitespace + 1)
    DECLARE @FirstComma         int          = CHARINDEX(',', @str)

    DECLARE @DateString         nvarchar(30) = STUFF(LEFT(@str, @ThirdWhitespace), @FirstComma - 2, 2, '')
    DECLARE @StartTimeString    nvarchar(10) = SUBSTRING(@str, @ThirdWhitespace + 1, @FourthWhitespace - @ThirdWhitespace - 1)
    DECLARE @EndTimeString      nvarchar(10) = SUBSTRING(@str, @FifthWhitespace + 1, 100)

    INSERT INTO @Result
    SELECT  CAST(@DateString AS datetime) + CAST(@StartTimeString AS datetime)
        ,   CAST(@DateString AS datetime) + CAST(@EndTimeString   AS datetime)
    RETURN 
END

Usage:

SELECT      *
FROM        dbo.ConvertDate('May 3rd, 2015 8am - 1pm')

Note that this is very finicky. If you add an extra space anywhere in the string, it will break. If you have .NET CLR at your disposal, you can use regex to capture the components.

Upvotes: 1

Related Questions