counter
स्मार्टक्यूए का लोगो

SQL Server: convert common date column data to date with timezone

"sql server: convert common date column data to date with timezone" के लिए कोड उत्तर. हमें मिल 4 कोड उदाहरण पर स्मार्टक्यूए नीचे sql-server श्रेणी।

आपको जो समाधान चाहिए वह खोजें!

हम 50 से अधिक भाषाओं के सामान्य प्रोग्रामिंग मुद्दों के लिए समाधान प्रदान करते हैं, आशा है कि इससे मदद मिलेगी!

उदाहरण #1

द्वारा ewahner 2👍
public partial class UserDefinedFunctions
{
    [SqlFunction(Name = "FormatDate")]
    public static SqlString FormatDate(SqlDateTime date, SqlString formatString)
    {
        var thisDate = date.Value;
        return new SqlString (thisDate.ToString(formatString.Value));
    }
    [SqlFunction(Name = "FormatDateTimeOffset")]
    public static SqlString FormatDateTimeOffset(DateTimeOffset date, SqlString formatString)
    {
        return new SqlString(date.ToString(formatString.Value));
    }    
}
select dbo.FormatDate(getdate(), 'yyyy-MM-dd HH:mm:ss ''GMT'' zzz') as "DateTime"
select dbo.FormatDateTimeOffset(convert(datetimeoffset, getdate()), 'yyyy-MM-dd HH:mm:ss K') as "DateTimeOffset"    

उदाहरण #2

द्वारा wiretext 1👍
SWITCHOFFSET ( DATETIMEOFFSET, time_zone ) 
--SELECT SWITCHOFFSET (ColDatetimeoffset, '-03:00') 
SELECT SYSDATETIMEOFFSET() CurrentOffset
SELECT SWITCHOFFSET (SYSDATETIMEOFFSET(), '-03:00') 
SELECT CONVERT(VARCHAR, GETDATE(), 126)
SELECT CONVERT(VARCHAR, GETDATE(), 127)

उदाहरण #3

द्वारा Wes Palmer 1👍
SELECT CAST(<ColumnName> AS datetimeoffset)
FROM <TableName>
UPDATE <TableName>
SET <DateColumn> = CONVERT(DATETIMEOFFSET, <DateColumn>)
WHERE ...

उदाहरण #4

द्वारा Matt Johnson-Pint 2👍
  1. You could write a SQL CLR function. If you do, you have two sub-options:

    • If the server's local time zone where you're going to be doing the conversions is set for Brasilia, then you can simply use new DateTimeOffset(yourDateTime) - which will pick the appropriate offset based on the local time zone.

    • You could use the TimeZoneInfo class, but it will require Unsafe Mode be enabled. If you do, the conversion would look like this:

      var tz = TimeZoneInfo.FindSystemTimeZoneById("E. South America Standard Time");
      var dto = new DateTimeOffset(yourDateTime, tz.GetUtcOffset(yourDateTime));
      
  2. You can use my SQL Server Time Zone Support project, which incorporates all of the correct time zone conversions, and gives you some simple functions ready to go.

    DECLARE @tz varchar(25)
    SET @tz = 'America/Sao_Paulo';
    SELECT Tzdb.SwitchZone(Tzdb.LocalToUtc(YourDateTime, @tz, 1, 1), @tz);
    

    Notice that I first convert the local time to UTC, then switch it back to the original time zone. I'll probably update the project to include this as a single function, but for now the two will work.

प्रश्न और उत्तर स्टैक ओवरफ़्लो से एकत्र किए जाते हैं, cc by-sa 2.5, cc by-sa 3.0 और cc by-sa 4.0 के तहत लाइसेंस प्राप्त होते हैं।

समान प्रश्न