(Cast((CreateTime + ' 00:00:00.000' ) as datetime) between '2010-04-13 07:59:59.999' and '2010-04-14 08:00:00.000') CreateTime 是int 类型 这样吗?可是会提示错误。Arithmetic overflow error converting expression to data type datetime.
declare @a varchar(20) SET @a ='20010203' SELECT cast(@a AS DATETIME) SELECT @a+' '+convert(varchar(20),cast(@a AS DATETIME),114)--result /*
declare @a INT SET @a ='20010203' SELECT cast(ltrim(@a) AS DATETIME) SELECT ltrim(@a)+' '+convert(varchar(20),cast(ltrim(@a) AS DATETIME),114)--result /*
(Cast(ltrim(CreateTime) + ' 00:00:00.000') as datetime) between '2010-04-13 07:59:59.999' and '2010-04-14 08:00:00.000')
/*------------------------------------------------------------------ -- Author : htl258(Tony) -- Date : 2010-04-15 14:15:02 -- Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) Jul 9 2008 14:43:34 Copyright (c) 1988-2008 Microsoft Corporation Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)------------------------------------------------------------------*/ --> 生成测试数据表:tbIF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb] GO CREATE TABLE [tb]([CreateTime] INT) INSERT [tb] SELECT '20090821' UNION ALL SELECT '20090901' UNION ALL SELECT '20090914' GO --SELECT * FROM [tb]-->SQL查询如下:select CAST(ltrim([CreateTime]) as datetime) from tb /* ----------------------- 2009-08-21 00:00:00.000 2009-09-01 00:00:00.000 2009-09-14 00:00:00.000(3 行受影响) */如果只是一个字段,直接转为字符型再转日期就可以了。
INT 类型的 日期格式 转成 日期类型 先转成字符再转日期,前面没说清楚来。 Select Cast(Cast(CrateDate As Varchar) As DateTime) as 创建时间 from [Table]
20090821
20090901
20090914
想转成日期类型,进行判断。
(Cast(CreateTime as datetime) between '2010-04-13 07:59:59.999' and '2010-04-14 08:00:00.000')
这样吗?可是会提示错误。Arithmetic overflow error converting expression to data type datetime.
SET @a ='20010203'
SELECT cast(@a AS DATETIME)
SELECT @a+' '+convert(varchar(20),cast(@a AS DATETIME),114)--result
/*
------------------------------
2001-02-03 00:00:00.000(所影响的行数为 1 行)
------------------------------
20010203 00:00:00:000*/
你要哪个
SET @a ='20010203'
SELECT cast(ltrim(@a) AS DATETIME)
SELECT ltrim(@a)+' '+convert(varchar(20),cast(ltrim(@a) AS DATETIME),114)--result
/*
------------------------------
2001-02-03 00:00:00.000(所影响的行数为 1 行)
------------------------------
20010203 00:00:00:000*/
/*------------------------------------------------------------------
-- Author : htl258(Tony)
-- Date : 2010-04-15 14:15:02
-- Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
Jul 9 2008 14:43:34
Copyright (c) 1988-2008 Microsoft Corporation
Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)------------------------------------------------------------------*/
--> 生成测试数据表:tbIF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb]([CreateTime] INT)
INSERT [tb]
SELECT '20090821' UNION ALL
SELECT '20090901' UNION ALL
SELECT '20090914'
GO
--SELECT * FROM [tb]-->SQL查询如下:select CAST(ltrim([CreateTime]) as datetime) from tb
/*
-----------------------
2009-08-21 00:00:00.000
2009-09-01 00:00:00.000
2009-09-14 00:00:00.000(3 行受影响)
*/如果只是一个字段,直接转为字符型再转日期就可以了。
先转成字符再转日期,前面没说清楚来。
Select Cast(Cast(CrateDate As Varchar) As DateTime) as 创建时间 from [Table]