jtgogl.blogg.se

If you omit the date while converting to the datetime or smalldatetime data types, it will return as the date: SELECT CAST('2pm' AS datetime) AS Result So in this case you’d need to convert to datetime or smalldatetime. Here’s another example where we do actually specify a time: SELECT CAST('2pm' AS time) AS Result īut if we add the date, we get an error (unlike an earlier example when we converted to datetime): SELECT CAST('2pm ' AS time) AS Result

In that case, we specified a date but not a time, so it came back as 00:00:00. In this example we convert to the time data type: SELECT CAST('' AS time) AS Result Here we convert to the smalldatetime data type: SELECT CAST('' AS smalldatetime) AS Result Result: +-+Įxample 5 – Convert to smalldatetime Data Type In this example we convert to the datetimeoffset data type: SELECT CAST('' AS datetimeoffset) AS Result Here we convert to the datetime2 data type: SELECT CAST('' AS datetime2) AS Result Įxample 4 – Convert to datetimeoffset Data Type You can also specify the time along with the date: SELECT CAST('2pm ' AS datetime) AS Result Įxample 3 – Convert to datetime2 Data Type In this example we convert to the datetime data type: SELECT CAST('' AS datetime) AS Result Result: Conversion failed when converting date and/or time from character string.Įxample 2 – Convert to datetime Data Type Just like with CONVERT() the string needs to be in a style that’s able to be converted to the specified data type, otherwise you’ll get an error: SELECT CAST('Homer' AS date) AS Result In this example, we convert the string into a date data type (we specify this by using date as the second argument). length An optional integer that specifies the length of the target data type. This includes xml, bigint, and sql_variant. These arguments are defined as follows: expression Any valid expression. The syntax goes like this: CAST ( expression AS data_type ) This article contains examples using the CAST() function.

So if you need to convert a string to a date/time value, you can use the CONVERT() function or the CAST() function.Īnd if you get an error while using those two functions, the PARSE() function might be just what you need. This function works almost the same as the CONVERT() function, except that the syntax is slightly different (and CAST() doesn’t accept the style argument). In SQL Server, you can use the CAST() function to convert an expression of one data type to another.