代码语言
.
CSharp
.
JS
Java
Asp.Net
C
MSSQL
PHP
Css
PLSQL
Python
Shell
EBS
ASP
Perl
ObjC
VB.Net
VBS
MYSQL
GO
Delphi
AS
DB2
Domino
Rails
ActionScript
Scala
代码分类
文件
系统
字符串
数据库
网络相关
图形/GUI
多媒体
算法
游戏
Jquery
Extjs
Android
HTML5
菜单
网页交互
WinForm
控件
企业应用
安全与加密
脚本/批处理
开放平台
其它
【
MSSQL
】
根据日期得到星期的函数
作者:
Dezai.CN
/ 发布于
2012/2/8
/
509
<div> <p align="left" class="MsoNormal" style="margin: 0cm 0cm 0pt; text-align: left; mso-layout-grid-align: none;"><span lang="EN-US" style="color: green; font-family: "Courier New"; font-size: 10pt; mso-font-kerning: 0pt; mso-no-proof: yes;">--</span><span style="color: green; font-family: 宋体; font-size: 10pt; mso-font-kerning: 0pt; mso-no-proof: yes; mso-ascii-font-family: "Courier New"; mso-hansi-font-family: "Courier New"; mso-bidi-font-family: "Courier New";">最直接的方式就是用</span><span lang="EN-US" style="color: green; font-family: "Courier New"; font-size: 10pt; mso-font-kerning: 0pt; mso-no-proof: yes;">case when</span><span style="color: green; font-family: 宋体; font-size: 10pt; mso-font-kerning: 0pt; mso-no-proof: yes; mso-ascii-font-family: "Courier New"; mso-hansi-font-family: "Courier New"; mso-bidi-font-family: "Courier New";">判断,这里用表变量来处理的</span> <p align="left" class="MsoNormal" style="margin: 0cm 0cm 0pt; text-align: left; mso-layout-grid-align: none;"><span lang="EN-US" style="font-family: "Courier New"; font-size: 10pt; mso-font-kerning: 0pt; mso-no-proof: yes;">go</span> <p align="left" class="MsoNormal" style="margin: 0cm 0cm 0pt; text-align: left; mso-layout-grid-align: none;"><span lang="EN-US" style="color: green; font-family: "Courier New"; font-size: 10pt; mso-font-kerning: 0pt; mso-no-proof: yes;">--</span><span style="color: green; font-family: 宋体; font-size: 10pt; mso-font-kerning: 0pt; mso-no-proof: yes; mso-ascii-font-family: "Courier New"; mso-hansi-font-family: "Courier New"; mso-bidi-font-family: "Courier New";">创建函数</span> <p align="left" class="MsoNormal" style="margin: 0cm 0cm 0pt; text-align: left; mso-layout-grid-align: none;"><span lang="EN-US" style="color: blue; font-family: "Courier New"; font-size: 10pt; mso-font-kerning: 0pt; mso-no-proof: yes;">create</span><span lang="EN-US" style="font-family: "Courier New"; font-size: 10pt; mso-font-kerning: 0pt; mso-no-proof: yes;"> <span style="color: blue;">function</span> [dbo]<span style="color: gray;">.</span>[m_getweek]<span style="color: gray;">(</span>@date <span style="color: blue;">nvarchar</span><span style="color: gray;">(</span>2000<span style="color: gray;">))</span><span style="mso-spacerun: yes;"> </span></span> <p align="left" class="MsoNormal" style="margin: 0cm 0cm 0pt; text-align: left; mso-layout-grid-align: none;"><span lang="EN-US" style="color: blue; font-family: "Courier New"; font-size: 10pt; mso-font-kerning: 0pt; mso-no-proof: yes;">returns</span><span lang="EN-US" style="font-family: "Courier New"; font-size: 10pt; mso-font-kerning: 0pt; mso-no-proof: yes;"> <span style="color: blue;">varchar</span><span style="color: gray;">(</span>2000<span style="color: gray;">)</span></span> <p align="left" class="MsoNormal" style="margin: 0cm 0cm 0pt; text-align: left; mso-layout-grid-align: none;"><span lang="EN-US" style="color: blue; font-family: "Courier New"; font-size: 10pt; mso-font-kerning: 0pt; mso-no-proof: yes;">AS</span> <p align="left" class="MsoNormal" style="margin: 0cm 0cm 0pt; text-align: left; mso-layout-grid-align: none;"><span lang="EN-US" style="color: blue; font-family: "Courier New"; font-size: 10pt; mso-font-kerning: 0pt; mso-no-proof: yes;">begin</span><span lang="EN-US" style="font-family: "Courier New"; font-size: 10pt; mso-font-kerning: 0pt; mso-no-proof: yes;"> </span> <p align="left" class="MsoNormal" style="margin: 0cm 0cm 0pt; text-align: left; mso-layout-grid-align: none;"><span lang="EN-US" style="font-family: "Courier New"; font-size: 10pt; mso-font-kerning: 0pt; mso-no-proof: yes;"><span style="mso-tab-count: 1;"> </span><span style="color: blue;">declare</span> @weekday <span style="color: blue;">nvarchar</span><span style="color: gray;">(</span>300<span style="color: gray;">)</span></span> <p align="left" class="MsoNormal" style="margin: 0cm 0cm 0pt; text-align: left; mso-layout-grid-align: none;"><span lang="EN-US" style="font-family: "Courier New"; font-size: 10pt; mso-font-kerning: 0pt; mso-no-proof: yes;"><span style="mso-tab-count: 1;"> </span><span style="color: blue;">declare</span> @table <span style="color: blue;">table</span> <span style="color: gray;">(</span>id <span style="color: blue;">int</span> <span style="color: gray;">,</span>weekday <span style="color: blue;">nvarchar</span><span style="color: gray;">(</span>200<span style="color: gray;">))</span></span> <p align="left" class="MsoNormal" style="margin: 0cm 0cm 0pt; text-align: left; mso-layout-grid-align: none;"><span lang="EN-US" style="font-family: "Courier New"; font-size: 10pt; mso-font-kerning: 0pt; mso-no-proof: yes;"><span style="mso-tab-count: 1;"> </span><span style="color: blue;">insert</span> <span style="color: blue;">into</span> @table</span> <p align="left" class="MsoNormal" style="margin: 0cm 0cm 0pt; text-align: left; mso-layout-grid-align: none;"><span lang="EN-US" style="font-family: "Courier New"; font-size: 10pt; mso-font-kerning: 0pt; mso-no-proof: yes;"><span style="mso-tab-count: 1;"> </span><span style="color: blue;">select</span> 0<span style="color: gray;">,</span><span style="color: red;">'</span></span><span style="color: red; font-family: 宋体; font-size: 10pt; mso-font-kerning: 0pt; mso-no-proof: yes; mso-ascii-font-family: "Courier New"; mso-hansi-font-family: "Courier New"; mso-bidi-font-family: "Courier New";">星期天</span><span lang="EN-US" style="color: red; font-family: "Courier New"; font-size: 10pt; mso-font-kerning: 0pt; mso-no-proof: yes;">'</span><span lang="EN-US" style="font-family: "Courier New"; font-size: 10pt; mso-font-kerning: 0pt; mso-no-proof: yes;"> <span style="color: blue;">union</span> <span style="color: blue;">select</span> 1<span style="color: gray;">,</span><span style="color: red;">'</span></span><span style="color: red; font-family: 宋体; font-size: 10pt; mso-font-kerning: 0pt; mso-no-proof: yes; mso-ascii-font-family: "Courier New"; mso-hansi-font-family: "Courier New"; mso-bidi-font-family: "Courier New";">星期一</span><span lang="EN-US" style="color: red; font-family: "Courier New"; font-size: 10pt; mso-font-kerning: 0pt; mso-no-proof: yes;">'</span><span lang="EN-US" style="font-family: "Courier New"; font-size: 10pt; mso-font-kerning: 0pt; mso-no-proof: yes;"> <span style="color: blue;">union</span> <span style="color: blue;">select</span> 2<span style="color: gray;">,</span><span style="color: red;">'</span></span><span style="color: red; font-family: 宋体; font-size: 10pt; mso-font-kerning: 0pt; mso-no-proof: yes; mso-ascii-font-family: "Courier New"; mso-hansi-font-family: "Courier New"; mso-bidi-font-family: "Courier New";">星期二</span><span lang="EN-US" style="color: red; font-family: "Courier New"; font-size: 10pt; mso-font-kerning: 0pt; mso-no-proof: yes;">'</span><span lang="EN-US" style="font-family: "Courier New"; font-size: 10pt; mso-font-kerning: 0pt; mso-no-proof: yes;"> <span style="color: blue;">union</span> </span> <p align="left" class="MsoNormal" style="margin: 0cm 0cm 0pt; text-align: left; mso-layout-grid-align: none;"><span lang="EN-US" style="font-family: "Courier New"; font-size: 10pt; mso-font-kerning: 0pt; mso-no-proof: yes;"><span style="mso-tab-count: 1;"> </span><span style="color: blue;">select</span> 3<span style="color: gray;">,</span><span style="color: red;">'</span></span><span style="color: red; font-family: 宋体; font-size: 10pt; mso-font-kerning: 0pt; mso-no-proof: yes; mso-ascii-font-family: "Courier New"; mso-hansi-font-family: "Courier New"; mso-bidi-font-family: "Courier New";">星期三</span><span lang="EN-US" style="color: red; font-family: "Courier New"; font-size: 10pt; mso-font-kerning: 0pt; mso-no-proof: yes;">'</span><span lang="EN-US" style="font-family: "Courier New"; font-size: 10pt; mso-font-kerning: 0pt; mso-no-proof: yes;"> <span style="color: blue;">union</span> <span style="color: blue;">select</span> 4<span style="color: gray;">,</span><span style="color: red;">'</span></span><span style="color: red; font-family: 宋体; font-size: 10pt; mso-font-kerning: 0pt; mso-no-proof: yes; mso-ascii-font-family: "Courier New"; mso-hansi-font-family: "Courier New"; mso-bidi-font-family: "Courier New";">星期四</span><span lang="EN-US" style="color: red; font-family: "Courier New"; font-size: 10pt; mso-font-kerning: 0pt; mso-no-proof: yes;">'</span><span lang="EN-US" style="font-family: "Courier New"; font-size: 10pt; mso-font-kerning: 0pt; mso-no-proof: yes;"> <span style="color: blue;">union</span> <span style="color: blue;">select</span> 5<span style="color: gray;">,</span><span style="color: red;">'</span></span><span style="color: red; font-family: 宋体; font-size: 10pt; mso-font-kerning: 0pt; mso-no-proof: yes; mso-ascii-font-family: "Courier New"; mso-hansi-font-family: "Courier New"; mso-bidi-font-family: "Courier New";">星期五</span><span lang="EN-US" style="color: red; font-family: "Courier New"; font-size: 10pt; mso-font-kerning: 0pt; mso-no-proof: yes;">'</span><span lang="EN-US" style="font-family: "Courier New"; font-size: 10pt; mso-font-kerning: 0pt; mso-no-proof: yes;"> <span style="color: blue;">union</span> <span style="color: blue;">select</span> 6<span style="color: gray;">,</span><span style="color: red;">'</span></span><span style="color: red; font-family: 宋体; font-size: 10pt; mso-font-kerning: 0pt; mso-no-proof: yes; mso-ascii-font-family: "Courier New"; mso-hansi-font-family: "Courier New"; mso-bidi-font-family: "Courier New";">星期六</span><span lang="EN-US" style="color: red; font-family: "Courier New"; font-size: 10pt; mso-font-kerning: 0pt; mso-no-proof: yes;">'</span> <p align="left" class="MsoNormal" style="margin: 0cm 0cm 0pt; text-align: left; mso-layout-grid-align: none;"><span lang="EN-US" style="font-family: "Courier New"; font-size: 10pt; mso-font-kerning: 0pt; mso-no-proof: yes;"><span style="mso-tab-count: 1;"> </span><span style="color: blue;">select</span> @weekday<span style="color: gray;">=</span>weekday <span style="color: blue;">from</span> @table <span style="color: blue;">where</span> id<span style="color: gray;">=(</span><span style="color: fuchsia;">datepart</span><span style="color: gray;">(</span>dw<span style="color: gray;">,</span>@date<span style="color: gray;">)-</span>1<span style="color: gray;">)</span></span> <p align="left" class="MsoNormal" style="margin: 0cm 0cm 0pt; text-align: left; mso-layout-grid-align: none;"><span lang="EN-US" style="font-family: "Courier New"; font-size: 10pt; mso-font-kerning: 0pt; mso-no-proof: yes;"><span style="mso-tab-count: 1;"> </span><span style="color: blue;">return</span> @weekday</span> <p align="left" class="MsoNormal" style="margin: 0cm 0cm 0pt; text-align: left; mso-layout-grid-align: none;"><span lang="EN-US" style="color: blue; font-family: "Courier New"; font-size: 10pt; mso-font-kerning: 0pt; mso-no-proof: yes;">end</span> <p align="left" class="MsoNormal" style="margin: 0cm 0cm 0pt; text-align: left; mso-layout-grid-align: none;"><span lang="EN-US" style="color: blue; font-family: "Courier New"; font-size: 10pt; mso-font-kerning: 0pt; mso-no-proof: yes;"> </span> <p align="left" class="MsoNormal" style="margin: 0cm 0cm 0pt; text-align: left; mso-layout-grid-align: none;"><span lang="EN-US" style="color: green; font-family: "Courier New"; font-size: 10pt; mso-font-kerning: 0pt; mso-no-proof: yes;">--</span><span style="color: green; font-family: 宋体; font-size: 10pt; mso-font-kerning: 0pt; mso-no-proof: yes; mso-ascii-font-family: "Courier New"; mso-hansi-font-family: "Courier New"; mso-bidi-font-family: "Courier New";">测试示例</span> <p align="left" class="MsoNormal" style="margin: 0cm 0cm 0pt; text-align: left; mso-layout-grid-align: none;"><span lang="EN-US" style="color: blue; font-family: "Courier New"; font-size: 10pt; mso-font-kerning: 0pt; mso-no-proof: yes;">select</span><span lang="EN-US" style="font-family: "Courier New"; font-size: 10pt; mso-font-kerning: 0pt; mso-no-proof: yes;"> [dbo]<span style="color: gray;">.</span>[m_getweek]<span style="color: gray;">(</span><span style="color: fuchsia;">getdate</span><span style="color: gray;">())</span> <span style="color: blue;">as</span> </span><span style="font-family: 宋体; font-size: 10pt; mso-font-kerning: 0pt; mso-no-proof: yes; mso-ascii-font-family: "Courier New"; mso-hansi-font-family: "Courier New"; mso-bidi-font-family: "Courier New";">星期</span> <p align="left" class="MsoNormal" style="margin: 0cm 0cm 0pt; text-align: left; mso-layout-grid-align: none;"><span lang="EN-US" style="font-family: "Courier New"; font-size: 10pt; mso-font-kerning: 0pt; mso-no-proof: yes;"> </span> <p align="left" class="MsoNormal" style="margin: 0cm 0cm 0pt; text-align: left; mso-layout-grid-align: none;"><span lang="EN-US" style="color: green; font-family: "Courier New"; font-size: 10pt; mso-font-kerning: 0pt; mso-no-proof: yes;">--</span><span style="color: green; font-family: 宋体; font-size: 10pt; mso-font-kerning: 0pt; mso-no-proof: yes; mso-ascii-font-family: "Courier New"; mso-hansi-font-family: "Courier New"; mso-bidi-font-family: "Courier New";">今天的星期</span> <p align="left" class="MsoNormal" style="margin: 0cm 0cm 0pt; text-align: left; mso-layout-grid-align: none;"><span lang="EN-US" style="color: green; font-family: "Courier New"; font-size: 10pt; mso-font-kerning: 0pt; mso-no-proof: yes;">/*</span> <p align="left" class="MsoNormal" style="margin: 0cm 0cm 0pt; text-align: left; mso-layout-grid-align: none;"><span style="color: green; font-family: 宋体; font-size: 10pt; mso-font-kerning: 0pt; mso-no-proof: yes; mso-ascii-font-family: "Courier New"; mso-hansi-font-family: "Courier New"; mso-bidi-font-family: "Courier New";">星期</span> <p align="left" class="MsoNormal" style="margin: 0cm 0cm 0pt; text-align: left; mso-layout-grid-align: none;"><span lang="EN-US" style="color: green; font-family: "Courier New"; font-size: 10pt; mso-font-kerning: 0pt; mso-no-proof: yes;">--------</span> <p align="left" class="MsoNormal" style="margin: 0cm 0cm 0pt; text-align: left; mso-layout-grid-align: none;"><span style="color: green; font-family: 宋体; font-size: 10pt; mso-font-kerning: 0pt; mso-no-proof: yes; mso-ascii-font-family: "Courier New"; mso-hansi-font-family: "Courier New"; mso-bidi-font-family: "Courier New";">星期六</span> <p align="left" class="MsoNormal" style="margin: 0cm 0cm 0pt; text-align: left; mso-layout-grid-align: none;"><span lang="EN-US" style="color: green; font-family: "Courier New"; font-size: 10pt; mso-font-kerning: 0pt; mso-no-proof: yes;">*/</span> </div>
试试其它关键字
日期得到星期
同语言下
.
SQL查询 多列合并成一行用逗号隔开
.
查看存储过程修改时间,最近执行时间
.
设置手动批量删除数据库相关进程
.
获取某个表中特定字段的所有字符串形式
.
SQL 如何去除重复的字符串
.
怎么去掉一个字段中的重复数据
.
String 去除空格 回车 换行 水平制表符
.
SQL查询和替换含有回车,空格,TAB
.
SQL SERVER 查询每日新增用户数量、次留数量
.
判断两个字符串是否存在相同的内容
可能有用的
.
C#实现的html内容截取
.
List 切割成几份 工具类
.
SQL查询 多列合并成一行用逗号隔开
.
一行一行读取txt的内容
.
C#动态修改文件夹名称(FSO实现,不移动文件)
.
c# 移动文件或文件夹
.
c#图片添加水印
.
Java PDF转换成图片并输出给前台展示
.
网站后台修改图片尺寸代码
.
处理大图片在缩略图时的展示
Dezai.CN
贡献的其它代码
(
4037
)
.
多线程Socket服务器模块
.
生成随机密码
.
清除浮动样式
.
弹出窗口居中
.
抓取url的函数
.
使用base HTTP验证
.
div模拟iframe嵌入效果
.
通过header转向的方法
.
Session操作类
.
执行sqlite输入插入操作后获得自动编号的ID
Copyright © 2004 - 2024 dezai.cn. All Rights Reserved
站长博客
粤ICP备13059550号-3