英文:
Date Conversion in SQL Server?
问题
我想要更改DateCompleted列，以显示实际日期。
例如，我想要将202302更改为显示2023-02-01，将202301更改为显示2023-01-01。我如何将当前的日期列转换为这样呢？
select
convert(varchar(7), DateCompleted, 120) + '-01' as DateCompleted,
GtrReference,
SourceWarehouse,
TargetWarehouse,
DateCreated,
EntryType,
ControlAccount,
InitialValue,
Operator,
FirstDesc,
SecondDesc
from
(
select
concat(d.TransfCompYear, case when len(d.TransfCompPeriod) = 1 then '0' end, d.TransfCompPeriod) as DateCompleted,
a.Complete,
d.Line,
d.TransfCompPeriod as DateMonth,
d.TransfCompYear as DateYear,
a.GtrReference as GtrReference,
a.SourceWarehouse as SourceWarehouse,
max(a.TargetWarehouse) over (partition by a.GtrReference) as TargetWarehouse,
a.DateCreated as DateCreated,
COALESCE(d.ExpectedDueDate, d.ExpectedDueDate) as ExpectedDueDate,
a.EntryType as EntryType,
a.ControlAccount as ControlAccount,
max(a.InitialValue) over (partition by a.GtrReference) as InitialValue,
a.Operator as Operator,
b.Description as FirstDesc,
c.Description as SecondDesc
FROM
[GtrMaster] a
LEFT JOIN [InvWhControl] b
ON (a.SourceWarehouse = b.Warehouse)
LEFT JOIN [InvWhControl] c
ON (a.TargetWarehouse = c.Warehouse)
LEFT JOIN [GtrDetail] d
ON (a.GtrReference = d.GtrReference)
) as i
WHERE (i.EntryType = 'W' OR i.EntryType = 'S')
AND i.Complete = 'Y' AND i.GtrReference >= ''
and i.Line = '1'
and i.DateCompleted >= convert(varchar(6), getdate() - 90, 112)
ORDER BY i.DateCompleted desc
上面的SQL查询将DateCompleted列的值更改为所需的格式，即YYYY-MM-01，以显示实际日期。
英文:
I have a query that generates this table:
| DateCompleted | GtrReference | SourceWarehouse | TargetWarehouse | DateCreated | EntryType | ControlAccount | InitialValue | Operator | FirstDesc | SecondDesc |
|---|---|---|---|---|---|---|---|---|---|---|
| 202302 | 01062023 | W | 01 | 2023-01-06 00:00:00.000 | W | 1610 | 6080.00 | CWHITE | IN TRANSIT WAREHOUSE | MAIN WAREHOUSE |
| 202302 | 01172023 | W | 01 | 2023-01-17 00:00:00.000 | W | 1610 | 6210.00 | CWHITE | IN TRANSIT WAREHOUSE | MAIN WAREHOUSE |
| 202302 | 02022023 | W | 01 | 2023-02-02 00:00:00.000 | W | 1610 | 3616.00 | CWHITE | IN TRANSIT WAREHOUSE | MAIN WAREHOUSE |
The Query:
select
DateCompleted,
GtrReference,
SourceWarehouse,
TargetWarehouse,
DateCreated,
EntryType,
ControlAccount,
InitialValue,
Operator,
FirstDesc,
SecondDesc
from
(
select
concat(d.TransfCompYear, case when len(d.TransfCompPeriod) = 1 then '0' end, d.TransfCompPeriod) as DateCompleted,
a.Complete,
d.Line,
d.TransfCompPeriod as DateMonth,
d.TransfCompYear as DateYear,
a.GtrReference as GtrReference,
a.SourceWarehouse as SourceWarehouse,
max(a.TargetWarehouse) over (partition by a.GtrReference) as TargetWarehouse,
a.DateCreated as DateCreated,
COALESCE(d.ExpectedDueDate, d.ExpectedDueDate) as ExpectedDueDate,
a.EntryType as EntryType,
a.ControlAccount as ControlAccount,
max(a.InitialValue) over (partition by a.GtrReference) as InitialValue,
a.Operator as Operator,
b.Description as FirstDesc,
c.Description as SecondDesc
FROM
[GtrMaster] a
LEFT JOIN [InvWhControl] b
ON (a.SourceWarehouse = b.Warehouse)
LEFT JOIN [InvWhControl] c
ON (a.TargetWarehouse = c.Warehouse)
LEFT JOIN [GtrDetail] d
ON (a.GtrReference = d.GtrReference)
) as i
WHERE ( i.EntryType = 'W' OR i.EntryType = 'S' )
AND i.Complete = 'Y' AND i.GtrReference >= ''
and i.Line = '1'
and i.DateCompleted >= convert(varchar(6),getdate()-90,112)
ORDER BY i.DateCompleted desc
What i'm trying to do is change the DateCompleted column to show an actual date.
Ex, I want to change 202302 to show 2023-02-01, and 202301 to show 2023-01-01. How can I convert my current date column to that?
答案1
得分: 1
使用Convert：
Convert(DateTime, [DateCompleted] + '01') As TrueDateCompleted
返回的日期值可以按您的喜好格式化。
英文:
Use Convert:
Convert(DateTime, [DateCompleted] + '01') As TrueDateCompleted
The returned date values you can format as you like.
答案2
得分: 0
一个日历表在这里会非常有用。
从一个日历表中，你可以对每个日期的年份和月份列使用CONCAT()（或使用YearMonthNum列）。然后将该列与你的DateCompleted列连接，通过某些区分子句（例如WHERE DayNum = 1）仅返回每个月的第一个日期。
英文:
A calendar table would be very useful here.
From a calendar table you could CONCAT() the year and month columns for each date (or use a YearMonthNum column). Then join this column to your DateCompleted column, returning only the first date of each month via some distinction clause e.g., WHERE DayNum = 1
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论