Tag: conversion functions

How to_timestamp() Works in PostgreSQL

Posted on by Ian

According to the PostgreSQL documentation, there are two functions called to_timestamp():

  • One converts the Unix epoch to a timestamp. The Unix epoch is the number of seconds since 1970-01-01 00:00:00+00.
  • The other converts a string to a timestamp.

More specifically, each function converts the value to a timestamp with time zone value.

Although the Postgres documentation presents them as two separate to_timestamp() functions, I present them as if they’re one function that accepts either one argument, or two.

Continue reading →

How to_char() Works in PostgreSQL

Posted on by Ian

In Postgres, to_char() is a data type formatting function that converts its first argument to a string.

The format of the string is determined by the second argument.

The to_char() function can be used to do the following conversions:

  • time stamp to string
  • interval to string
  • integer to string
  • real/double precision to string
  • numeric to string
Continue reading →

Converting Between Date & Time Data Types in SQL Server (T-SQL Examples)

Posted on by Ian

When you convert between date and time data types in SQL Server, you need to be mindful of how the new data type will handle the value you’re trying to assign to it.

In some cases you might lose part of the value, in other cases you might gain a bunch of zeros (increasing storage size in the process). You may also end up with a value that’s been rounded up.

The following articles contain examples of conversions between the different date and time data types, with a particular focus on the issues I just mentioned.

Continue reading →

Convert ‘datetimeoffset’ to ‘time’ in SQL Server (T-SQL Examples)

Posted on by Ian

If you have a datetimeoffset value, but you don’t need the date and time zone offset part, converting it to time will save you a lot of storage space (while removing unnecessary details from the value). This article contains examples of converting a datetimeoffset value to a time value in SQL Server.

Continue reading →

Convert ‘datetimeoffset’ to ‘date’ in SQL Server (T-SQL Examples)

Posted on by Ian

If you have a datetimeoffset value, but you don’t need the time and time zone offset part, converting it to date will save you a lot of storage space (while removing unnecessary details from the value). This article contains examples of converting a datetimeoffset value to a date value in SQL Server.

Continue reading →

Convert ‘datetimeoffset’ to ‘smalldatetime’ in SQL Server (T-SQL Examples)

Posted on by Ian

This article contains examples of converting a datetimeoffset value to smalldatetime in SQL Server.

When you convert a datetimeoffset value to smalldatetime, the date and hours are copied. The minutes are rounded up (depending on the seconds value) and the seconds are set to 0.

Continue reading →

Convert ‘datetimeoffset’ to ‘datetime2’ in SQL Server (T-SQL Examples)

Posted on by Ian

This article contains examples of converting a datetimeoffset value to datetime2 in SQL Server.

When you convert a datetimeoffset value to datetime2, the date and time are copied to the datetime2 value, and the time zone is truncated. The fractional seconds are also truncated to fit if the destination precision is lower.

Continue reading →

Convert ‘datetimeoffset’ to ‘datetime’ in SQL Server (T-SQL Examples)

Posted on by Ian

This article contains examples of converting a datetimeoffset value to datetime in SQL Server.

When you convert a datetimeoffset value to datetime, the date and time values are copied, and the time zone offset is truncated. When the fractional precision of the datetimeoffset value is greater than three digits, the value is truncated.

Continue reading →