假設資料庫為出勤記錄表如下，並使用sql進行查詢:
CREATE TABLE [dbo].[AttendanceCollect](
[CardCode] [nvarchar](200) NULL,
[EmployeeName] [nvarchar](200) NULL,
[EmployeeCode] [nvarchar](200) NULL,
[AttendanceDate] [datetime] NULL,
[AttendanceTime] [nvarchar](200) NULL
)一般日期查詢
在使用sql進行一般日期查詢時，您可以參考以下範例。
✅ 查詢特定日期（例如 2025-06-23）的打卡紀錄：
SELECT *
FROM [dbo].[AttendanceCollect]
WHERE CAST([AttendanceDate] AS DATE) = '2025-06-23';✅ 查詢特定日期範圍（例如 2025-06-01 到 2025-06-23）的紀錄：
SELECT *
FROM [dbo].[AttendanceCollect]
WHERE [AttendanceDate] >= '2025-06-01'
AND [AttendanceDate] < '2025-06-24';注意這種「小於隔天」的寫法可以避免 datetime 時間部分的干擾。
✅ 查詢今天的打卡紀錄：
SELECT *
FROM [dbo].[AttendanceCollect]
WHERE CAST([AttendanceDate] AS DATE) = CAST(GETDATE() AS DATE);✅ 查詢某員工（如 EmployeeCode = ‘E001’）在某日的打卡記錄：
SELECT *
FROM [dbo].[AttendanceCollect]
WHERE CAST([AttendanceDate] AS DATE) = '2025-06-23'
AND [EmployeeCode] = 'E001';依 日期與時間組合 做排序或進一步處理，也可將 AttendanceDate + AttendanceTime 合併：
SELECT *,
CONVERT(datetime,
FORMAT([AttendanceDate], 'yyyy-MM-dd') + ' ' + [AttendanceTime], 120
) AS FullDateTime
FROM [dbo].[AttendanceCollect]如果 AttendanceTime 是如 '08:30:00' 格式，這樣可以合併成真正的完整時間戳。
✅ 查詢當月的打卡紀錄
SELECT *
FROM [dbo].[AttendanceCollect]
WHERE AttendanceDate >= DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1)
AND AttendanceDate < DATEADD(MONTH, 1, DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1));說明：
DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1)：取得當月第一天（例如：2025-08-01）DATEADD(MONTH, 1, ...)：取得下個月第一天（例如：2025-09-01）- 用
< 下個月第一天的寫法可以避免datetime精度影響
✅ 補充：只查當月最早與最晚打卡（報表格式）
SELECT
EmployeeCode,
EmployeeName,
CAST(AttendanceDate AS DATE) AS AttendanceDay,
MIN(AttendanceTime) AS FirstCheckIn,
MAX(AttendanceTime) AS LastCheckOut
FROM dbo.AttendanceCollect
WHERE AttendanceDate >= DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1)
AND AttendanceDate < DATEADD(MONTH, 1, DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1))
GROUP BY EmployeeCode, EmployeeName, CAST(AttendanceDate AS DATE)
ORDER BY EmployeeCode, AttendanceDay;✅ 查詢上一個月的打卡紀錄
要查詢「上一個月的打卡紀錄」，可用 SQL Server 內建函數 GETDATE() 和 DATEADD() 搭配 DATEFROMPARTS()，來取得上個月的第一天與最後一天（或隔月1日）。
SELECT *
FROM [dbo].[AttendanceCollect]
WHERE AttendanceDate >= DATEFROMPARTS(YEAR(DATEADD(MONTH, -1, GETDATE())), MONTH(DATEADD(MONTH, -1, GETDATE())), 1)
AND AttendanceDate < DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1);🔍 範例（假設今天是 2025-08-07）：
DATEADD(MONTH, -1, GETDATE())→ 2025-07-07DATEFROMPARTS(YEAR(...), MONTH(...), 1)→ 2025-07-01DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1)→ 2025-08-01
所以查詢區間會是：
👉 2025-07-01 <= AttendanceDate < 2025-08-01
✅ 補充：上一個月的「最早／最晚打卡報表」
SELECT
EmployeeCode,
EmployeeName,
CAST(AttendanceDate AS DATE) AS AttendanceDay,
MIN(AttendanceTime) AS FirstCheckIn,
MAX(AttendanceTime) AS LastCheckOut
FROM dbo.AttendanceCollect
WHERE AttendanceDate >= DATEFROMPARTS(YEAR(DATEADD(MONTH, -1, GETDATE())), MONTH(DATEADD(MONTH, -1, GETDATE())), 1)
AND AttendanceDate < DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1)
GROUP BY EmployeeCode, EmployeeName, CAST(AttendanceDate AS DATE)
ORDER BY EmployeeCode, AttendanceDay;出勤報表查詢
報表一：某日每位員工的最早與最晚打卡時間
SELECT
EmployeeCode,
EmployeeName,
CAST(AttendanceDate AS DATE) AS AttendanceDay,
MIN(AttendanceTime) AS FirstCheckIn,
MAX(AttendanceTime) AS LastCheckOut
FROM dbo.AttendanceCollect
WHERE CAST(AttendanceDate AS DATE) = '2025-06-23'
GROUP BY EmployeeCode, EmployeeName, CAST(AttendanceDate AS DATE)
ORDER BY EmployeeCode;報表二：某一段時間內，員工們的每日打卡最早/最晚時間
SELECT
EmployeeCode,
EmployeeName,
CAST(AttendanceDate AS DATE) AS AttendanceDay,
MIN(AttendanceTime) AS FirstCheckIn,
MAX(AttendanceTime) AS LastCheckOut
FROM dbo.AttendanceCollect
WHERE AttendanceDate >= '2025-06-01'
AND AttendanceDate < '2025-07-01'
GROUP BY EmployeeCode, EmployeeName, CAST(AttendanceDate AS DATE)
ORDER BY EmployeeCode, AttendanceDay;報表三：統計一段時間內各員工出勤天數
SELECT
EmployeeCode,
EmployeeName,
COUNT(DISTINCT CAST(AttendanceDate AS DATE)) AS AttendanceDays
FROM dbo.AttendanceCollect
WHERE AttendanceDate >= '2025-06-01'
AND AttendanceDate < '2025-07-01'
GROUP BY EmployeeCode, EmployeeName
ORDER BY AttendanceDays DESC;報表四：某員工某日的完整打卡記錄（時間排序）
SELECT
AttendanceTime
FROM dbo.AttendanceCollect
WHERE EmployeeCode = 'E001'
AND CAST(AttendanceDate AS DATE) = '2025-06-23'
ORDER BY AttendanceTime;報表五 ：員工出勤月曆打卡紀錄格式（早上 / 晚上欄位）
SELECT CardCode
,EmployeeCode
,EmployeeName
,CONVERT(DATE, DATE) AS [Date]
,MIN(TIME) AS FirstCheckIn
,MAX(TIME) AS LastCheckOut
FROM AttendanceCollect
WHERE DATE >= '2025-06-01'
AND DATE < '2025-07-01'
AND EmployeeCode IS NOT NULL
AND CardCode IS NOT NULL
GROUP BY CardCode
,EmployeeCode
,EmployeeName
,CONVERT(DATE, DATE)
ORDER BY CardCode
,EmployeeCode
,[Date];
