Skip to main content

Return to Answer

Updated example to include fractional milliseconds > 0.5 as it is crtitical to understand where and how rounding affects the solution; minor formatting improvements
Source Link
edit approved May 11, 2021 at 18:33
krads
edit approved May 11, 2021 at 18:33
krads
  • 1.4k
  • 10
  • 14

Use CAST with following parameters:

DateDate

select Cast('2017-10-11 14:38:50.440'540' as date)

Output: 2017-10-11

DatetimeDatetime

select Cast('2017-10-11 14:38:50.440'540' as datetime)

Output: 2017-10-11 14:38:50.440540

SmallDatetimeSmallDatetime

select Cast('2017-10-11 14:38:50.440'540' as smalldatetime)

Output: 2017-10-11 14:39:00

DatetimeOffsetNote this method rounds to whole minutes (so you lose the seconds as well as the milliseconds)

DatetimeOffset

select Cast('2017-10-11 14:38:50.440'540' as datetimeoffset)

Output: 2017-10-11 14:38:50.44000005400000 +00:00

Datetime2Datetime2

select Cast('2017-10-11 14:38:50.440'540' as datetime2)

Output: 2017-10-11 14:38:50.44000005400000

Use CAST with following parameters:

Date

select Cast('2017-10-11 14:38:50.440' as date)

Output: 2017-10-11

Datetime

select Cast('2017-10-11 14:38:50.440' as datetime)

Output: 2017-10-11 14:38:50.440

SmallDatetime

select Cast('2017-10-11 14:38:50.440' as smalldatetime)

Output: 2017-10-11 14:39:00

DatetimeOffset

select Cast('2017-10-11 14:38:50.440' as datetimeoffset)

Output: 2017-10-11 14:38:50.4400000 +00:00

Datetime2

select Cast('2017-10-11 14:38:50.440' as datetime2)

Output: 2017-10-11 14:38:50.4400000

Use CAST with following parameters:

Date

select Cast('2017-10-11 14:38:50.540' as date)

Output: 2017-10-11

Datetime

select Cast('2017-10-11 14:38:50.540' as datetime)

Output: 2017-10-11 14:38:50.540

SmallDatetime

select Cast('2017-10-11 14:38:50.540' as smalldatetime)

Output: 2017-10-11 14:39:00

Note this method rounds to whole minutes (so you lose the seconds as well as the milliseconds)

DatetimeOffset

select Cast('2017-10-11 14:38:50.540' as datetimeoffset)

Output: 2017-10-11 14:38:50.5400000 +00:00

Datetime2

select Cast('2017-10-11 14:38:50.540' as datetime2)

Output: 2017-10-11 14:38:50.5400000

Minor corrections
Source Link
edit approved Oct 28, 2017 at 5:46
Stepan Novikov
edit approved Oct 28, 2017 at 5:46
Stepan Novikov
  • 1.4k
  • 12
  • 23

Use CASTCAST with following parameterparameters:

DateDate

select Cast('2017-10-11 14:38:50.440' as date)

Output: 2017-10-11

DatetimeDatetime

select Cast('2017-10-11 14:38:50.440' as datetime)

Output: 2017-10-11 14:38:50.440

SmallDatetimeSmallDatetime

select Cast('2017-10-11 14:38:50.440' as smalldatetime)

Output: 2017-10-11 14:39:00

DatetimeOffsetDatetimeOffset

select Cast('2017-10-11 14:38:50.440' as datetimeoffset)

Output: 2017-10-11 14:38:50.4400000 +00:00

Datetime2Datetime2

select Cast('2017-10-11 14:38:50.440' as datetime2)

Output: 2017-10-11 14:38:50.4400000

Use CAST with following parameter

Date

select Cast('2017-10-11 14:38:50.440' as date)

Output: 2017-10-11

Datetime

select Cast('2017-10-11 14:38:50.440' as datetime)

Output: 2017-10-11 14:38:50.440

SmallDatetime

select Cast('2017-10-11 14:38:50.440' as smalldatetime)

Output: 2017-10-11 14:39:00

DatetimeOffset

select Cast('2017-10-11 14:38:50.440' as datetimeoffset)

Output: 2017-10-11 14:38:50.4400000 +00:00

Datetime2

select Cast('2017-10-11 14:38:50.440' as datetime2)

Output: 2017-10-11 14:38:50.4400000

Use CAST with following parameters:

Date

select Cast('2017-10-11 14:38:50.440' as date)

Output: 2017-10-11

Datetime

select Cast('2017-10-11 14:38:50.440' as datetime)

Output: 2017-10-11 14:38:50.440

SmallDatetime

select Cast('2017-10-11 14:38:50.440' as smalldatetime)

Output: 2017-10-11 14:39:00

DatetimeOffset

select Cast('2017-10-11 14:38:50.440' as datetimeoffset)

Output: 2017-10-11 14:38:50.4400000 +00:00

Datetime2

select Cast('2017-10-11 14:38:50.440' as datetime2)

Output: 2017-10-11 14:38:50.4400000

Source Link
Akshay Mishra
Akshay Mishra
  • 1.8k
  • 2
  • 16
  • 14

Use CAST with following parameter

Date

select Cast('2017-10-11 14:38:50.440' as date)

Output: 2017-10-11

Datetime

select Cast('2017-10-11 14:38:50.440' as datetime)

Output: 2017-10-11 14:38:50.440

SmallDatetime

select Cast('2017-10-11 14:38:50.440' as smalldatetime)

Output: 2017-10-11 14:39:00

DatetimeOffset

select Cast('2017-10-11 14:38:50.440' as datetimeoffset)

Output: 2017-10-11 14:38:50.4400000 +00:00

Datetime2

select Cast('2017-10-11 14:38:50.440' as datetime2)

Output: 2017-10-11 14:38:50.4400000