如何使用pl sql仅在sql表中插入日期 [英] how to insert only date in sql table using pl sql

问题描述

如何使用pl sql在sql表中仅插入日期

---------------------------



我在Sql中有一个表，



表名 - > CalendarDailySchedule

列名 - > CalendarDates



我想从今天开始365天生成，这个工作正常。

但它插入日期和时间....我需要在2015年2月18日的日期中使用SqlServer 2005数据库，这是我的代码。



AM使用SqlServer 2005数据库。请帮忙。

How to insert only date in sql table using pl sql
---------------------------

I have a table in Sql,

Table Name -> CalendarDailySchedule
Column Name -> CalendarDates

I want to generate 365 days from today, This is working fine.
But Its inserting with date and time....just i need date as 18/02/2015 in column

AM using SqlServer 2005 database, This is my code. Please help.

DECLARE @CalDate datetime
DECLARE @Counter INT
SET @Counter =0
SET @CalDate = Getdate()
WHILE (@Counter < 365)
BEGIN
Insert into CalendarDailySchedule(CalendarDates) VALUES (@CalDate)
SET @Counter = @Counter + 1
SET @CalDate = @CalDate + 1

END
GO

谢谢...

Thanks...

推荐答案

你需要使用正确的数据类型 [ ^ ]。对于sql server 2008及更高版本，它是 date 数据类型。



公用表表达式版本：

You need to use proper data type[^]. For sql server 2008 and higher it's a date data type.

Common Table Expression version:

;WITH MyDates AS
(
    SELECT CONVERT(DATE, '2015-01-01') AS MyDate
    UNION ALL
    SELECT DATEADD(dd, 1, MyDAte) AS MyDate
    FROM MyDates 
    WHERE MyDate<convert(date,>)
SELECT MyDate
FROM MyDates
OPTION (MAXRECURSION 0)

CalendarDates列的数据类型应仅为日期，请在下面查看



工作原理

Datatype of CalendarDates column should be Date only, Check below

it works

Create table #CalendarDailySchedule(CalendarDates Date)

DECLARE @CalDate datetime
DECLARE @Counter INT
SET @Counter =0
SET @CalDate = Getdate()
WHILE (@Counter < 365)
BEGIN
Insert into #CalendarDailySchedule(CalendarDates) VALUES (@CalDate)
SET @Counter = @Counter + 1
SET @CalDate = @CalDate + 1
 
END
GO	

select * from #CalendarDailySchedule

这篇关于如何使用pl sql仅在sql表中插入日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！