从 SQL 获取年份和月份
我想知道如何将年份和月份存入我的数据库中。例如，假设现在是 2011 年 8 月。我需要的输出如下：CAB 11 08 001（CAB + YEAR + MONTH + CURRENT NO. in track 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。
在我的表中，我只有 genYear 和当前编号。我需要为 MONTH 添加另一列吗？
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. )
This is my 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
The output for this is CAB1180035, but I need CAB1108035. I need to put zero(0) 08 like this for the month.
In my table I have only genYear and Current No. Do I need to add another column for MONTH?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
看起来您正在为“年”、“月”等创建单独的列。
据我所知，大多数（全部？）DBMS 都有
Date类型。使用它。他们在其中添加了很多有用的东西，例如接受不同格式的日期输入并以您能想到的几乎任何格式将它们作为输出。例如，如果 Oracle 中有一个
Date类型的DT列，则可以将月份输出为，并且月份将始终显示为 2 位数字（01、02、. .. 12)
It looks like you're making separate columns for YEAR, MONTH, etc.
Most (all?) DBMSs I'm aware of have a
Datetype. 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.For example, if you have a
DTcolumn of typeDatein Oracle, you can output month asand the month will always be displayed as 2 digits (01, 02, ... 12)
这可能有效..
That might work..
使用你的方法\逻辑..
Using your method \ logic..