代码之家 › 专栏 › 技术社区 › Doonie Darkoo

如何从日期名称中获取日期?

sql-server

-3

Doonie Darkoo · 技术社区 · 6 年前

我怎样才能知道具体日期?比如我有星期四或者月号?

例如,如果我给12,我想得到这个月的第12天。或者如果我给“Sun”或“Sat”,有可能得到这些天的日期吗?

4 回复 | 直到 6 年前

Andrey Nikolov user3098458 6 年前

DATEFROMPARTS 函数可以从天、月和年构造日期。

DATEPARTS 相反-给出日期的日期、月、年、小时等。或者您可以使用以下函数 YEAR , MONTH 和 DAY

您可以解构 GETDATE

select DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 12)

DATEPART(dw, GETDATE()) (这会给你一周中的哪一天)。

t-clausen.dk 6 年前

查找当前月份的工作日的步骤

DECLARE @daynumber INT = 12

SELECT datename(weekday, dateadd(d, @daynumber - 1, getdate()))

DECLARE @dayname char(3) = 'sat'

;WITH CTE as
(
  SELECt TOP 
    (datediff(D, eomonth(getdate(), -1),eomonth(getdate())))
    dateadd(d,row_number()over(ORDER BY 1/0),
      eomonth(getdate(),-1))date
  FROM
    (values(1),(2),(3),(4),(5),(6))x(x),
    (values(1),(2),(3),(4),(5),(6))y(x)
)
SELECT day(date) monthday, date
FROM CTE
WHERE left(datename(weekday, date),3) = @dayname

Wei Lin 6 年前

select sysdatetime(); --2018-12-13 16:29:56.0560574

---If I give 12 for instance I want to get the date of 12th day of this month.
declare @numDate int = 12;
select dateadd(m, datediff(m,0,getdate()),@numDate - 1 ); --2018-12-12 00:00:00.000

--Or if I give 'Sun' or 'Sat' is it possible to get the dates of these days ?
declare @text nvarchar(20) = 'Sunday';
declare @dateStart date =  dateadd(month, datediff(month, 0, sysdatetime()), 0),
    @days int =( select (DAY(dateadd(dd,-1,DATEADD(m,1,cast(2018 as varchar(4)) + '-' + cast(12 as varchar(2)) +'-01')))));
declare @dateEnd date = DATEADD(day,@days-1,@dateStart);
;WITH CTE (Dates,EndDate) AS
(
    SELECT @dateStart AS Dates,@dateEnd AS EndDate
    UNION ALL
    SELECT DATEADD(day,1,Dates),EndDate
    FROM CTE 
    WHERE DATEADD(day,1,Dates) <= EndDate
)
SELECT CTE.Dates, DATENAME(DW, CTE.Dates)
FROM CTE
where DATENAME(DW, CTE.Dates)  = @text;

结果:

Dates,Day
2018/12/2,Sunday
2018/12/9,Sunday
2018/12/16,Sunday
2018/12/23,Sunday
2018/12/30,Sunday

Sean Pearce 6 年前

-- Here is how to get week day name to week day number
DECLARE @T TABLE (Dow INT, NameOfDay VARCHAR(15), ShortName CHAR(3));

WITH Days AS
(
    SELECT TOP 7
        ROW_NUMBER() OVER(PARTITION BY object_id ORDER BY object_id) AS RowNo
    FROM
        sys.all_columns
)
INSERT INTO @T
SELECT
    RowNo,
    DATENAME(WEEKDAY, RowNo - 1),
    LEFT(DATENAME(WEEKDAY, RowNo - 1), 3)
FROM
    Days

SELECT
    *
FROM
    @T;

-- Here is how to get start of period
SELECT
    DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0) AS StartOfDay,
    DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()), 0) AS StartOfWeek,
    DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) AS StartOfMonth,
    DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0) AS StartOfYear;

-- An example
WITH
StartPeriods AS
(
    SELECT DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()), 0) AS StartOfWeek
),
SelectedDay AS
(
    SELECT
        Dow - 1 AS Dow,
        (SELECT StartOfWeek FROM StartPeriods) AS StartOfWeek
    FROM
        @T
    WHERE
        ShortName = 'Wed'
)
SELECT
    DATEADD(DAY, Dow, StartOfWeek)
FROM
    SelectedDay;