从SQL获取年和月 [英] Get year and month from SQL

问题描述

我想知道如何让我的年份和月份进入我的数据库。例如，假设是2011年8月。我需要的输出是这样的： CAB 11 08 001 （跟踪号码中的CAB + YEAR + MONTH + CURRENT NO。）

I want to know how can I get the year and month into my database. For example, suppose it’s August, 2011. The output that I need is like this: CAB 11 08 001 (CAB + YEAR + MONTH + CURRENT NO. in tracking number. )

这是我的SQL：

ALTER PROCEDURE [dbo].[generateTrackNo] AS
Declare @tempYear VARCHAR(5),@tempMonth VARCHAR(5)
Set @tempYear = Year(GetDate())
Set @tempMonth = Month(GetDate())
SELECT 'CAB' + Right(Cast(Year(GetDate()) as varchar(10)),2)+ Right(Cast(Month(GetDate()) as varchar(10)),2) + Right('000000'+ Cast(CurrentNo as varchar(10)), 5) from tblTrackNo where GenYear = @tempYear
--UPDATE tblTrackNo SET CurrentNo = CurrentNo + 1 where GenYear = @tempYear

这个输出是 CAB1180035 ，但我需要 CAB1108035 。我需要把这个月的零（0）08放在这个月。

The output for this is CAB1180035, but I need CAB1108035. I need to put zero(0) 08 like this for the month.

在我的表中，我只有genYear和当前号码我需要为MONTH添加另一列？

In my table I have only genYear and Current No. Do I need to add another column for MONTH?

推荐答案

看起来您正在为YEAR，MONTH等分别列。

It looks like you're making separate columns for YEAR, MONTH, etc.

大多数（全部？）DBMSs我知道有一个 Date 类型。用它。他们把很多有用的东西放在它里面，比如接受不同格式的日期输入，并以几乎任何你想象的格式给出输出。

Most (all?) DBMSs I'm aware of have a Date type. Use it. They put a lot of useful stuff in it, like accepting date inputs in different formats and giving them as output in pretty much any format you can think of.

例如，如果Oracle中有 DT 类型为 Date 的列，则可以输出月份为

For example, if you have a DT column of type Date in Oracle, you can output month as

SELECT TO_CHAR(DT, "MM") FROM MYTABLE;

，该月份将始终显示为2位数（01，02，... 12）

and the month will always be displayed as 2 digits (01, 02, ... 12)

这篇关于从SQL获取年和月的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！