Sql Server
search record from table at given date
you pass date as varchar type and mm/dd/yyyy format
select * from emp where (createddate>’11/15/2008′ and
createddate<DATEADD(day,1, ‘11/15/2008′))
remove any new line charactor from value of column
update emp set name=replace(name,char(10),”) where
charindex(char(10),name)>0
checking space or any charactor in value in any column
select name from emp where charindex(‘ ‘,name)>0
Read Full Post | Make a Comment ( None so far )using datediff function in sql
select datediff(year,‘Nov 30 2001′,getdate()) as diffyear
select datediff(month,‘Nov 30 2001′,getdate())as diffmonth
select datediff(day,‘Nov 30 2001′,getdate()) as diffdaydiffyear
diffyear
———–
7 (1 row(s) affected)
diffmonth
———–
78(1 row(s) affected)
diffday
———-
2357(1 row(s) affected)
using datename function for name of month,day
select DATENAME(w, GETDATE()) AS ‘ Day Name’
day name
———
Wednesday
SELECT DATENAME(m, GETDATE()) AS ‘ month Name’
month name
———
may
Using Dateadd function adding day,month,year in date
select
dateadd(datepart,number,date)
eg-
select
DATEADD(month, 3, getdate())
Datepart
|Abbreviations
year| yy,yyyy
quarter| qq,q
month| mm,m
dayofyear| dy,y
day| dd,d
week| wk,ww
weekday| dw, w
hour| hh
minute| mi,n
second| ss, s
using datepart function get different output
selectdatepart(year,getdate())
selectdatepart(month,getdate())
select datepart(day,getdate())
select datepart(hour,getdate())
select datepart(minute,getdate())
select datepart(second,getdate())
select datepart(millisecond,getdate())
select datepart(weekday,getdate())
select datepart(dayofyear,getdate())
select datepart(quarter,getdate())
using convert function filter time or date only from datetime column
SELECT
convert(char(11), getdate(),109)
May 6 2008
SELECT
convert(char(8), getdate(),108)
12:51:32
and so on u can change according input different char length and style value
converting Date into string in different style
hi
i submit for converting date into string in different style by help of sql server help?
select
CONVERT(varchar,CONVERT(datetime,getdate()),106) as date
output
06 May 2008
other style u want help like this
0 or 100 | Default |mon dd yyyy hh:miAM (or PM)
101 | U.S.| mm/dd/yyyy
102 | ANSI |yy.mm.dd
103|British/French|dd/mm/yy
104|German|dd.mm.yy
105|Italian|dd-mm-yy
106 (1)|-|dd mon yy
107 (1)|-|Mon dd, yy
108|- |hh:mm:ss
9 or 109 (1, 2) |Default + [...]
display custum text using case in sqlserver
SELECT FirstName, LastName,
CASE WHEN sex =’M’ THEN ‘MALE’
WHEN sex = ’F’ THEN ‘FEMALE’
ELSE ‘Undefined’
END As Sex
FROM Employees
« Previous Entries
