Click here to Skip to main content
15,912,400 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
hi i have a dateime in xml eg. 2016-04-25T00:00:00+05:30
but when i am inserting it into table using sql it gives me date one day before ie. 2016-04-24

so how can i manage that in sql?

What I have tried:

i have tried sysdatetimeoffset and converision of datetime but its not working for me
Posted
Updated 27-Apr-16 0:14am
v2
Comments
Maciej Los 27-Apr-16 6:07am    
Exactly what is not working? You should provide at least a minimum information about your attempt to solve the problem.
Richard MacCutchan 27-Apr-16 6:10am    
Most likely because the time zone offset indicates a value that would make the UTC value of that datetime equivalent to 2016-04-24T18:30:00. Strip the time fields off the string before converting it.

1 solution

I created a simple scalar function in SQL that takes in the date as a string and restricts it to 23 characters. Then a CONVERT to datetime is used if the string is not null or empty. Then everywhere I have a date coming in I wrap this function around it.

Example:
SQL
SELECT [dbo].GetFormattedDate('2013-07-01T14:27:00.434')
,[dbo].GetFormattedDate('')
,[dbo].GetFormattedDate(NULL)
,[dbo].GetFormattedDate('2013-07-01T14:27:00.434Z')
,[dbo].GetFormattedDate('2013-07-01T14:27:00.434445Z')
,[dbo].GetFormattedDate('2013-07-01')

Function Code:
SQL
CREATE FUNCTION [dbo].[GetFormattedDate] 
(  
    @p_DateString varchar(23)  
)
RETURNS datetime 
AS
BEGIN  

/* 
Notice that the in parameter truncates all datetime values to 23 characters
Which would truncate the date time to the thousandths place of the milliseconds
*/
DECLARE @returnDatetime as datetime;

IF ((@p_DateString IS NULL) OR (@p_DateString = '')) 
    BEGIN
        /* Return null is string is empty or null already */
        SET @returnDatetime = NULL;
    END
ELSE
    BEGIN
        /* Otherwise conver the string to a datetime */
        SET @returnDatetime = CONVERT(datetime, @p_DateString);
    END

RETURN @returnDatetime;

END
 
Share this answer
 
Comments
Aviinash 27-Apr-16 6:29am    
i need the date what i get in xml by excluding the time part and it is converting date to previous date using that

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900