Query data from specific date SQL Server

Posted by cikul | Posted in SQL, Tips | Posted on 01-03-2012-05-2008

2

for example, you have sort of data from table order :

Order id Order_date Value
1 2005-12-15 13:04:42.667 500
2 2006-03-16 19:08:30.423 350
3 2007-07-28 15:14:30.350 400
4 2006-03-16 20:11:25.413 150
5 2000-09-10 16:09:58.817 200

and you want to get data for date 2006-03-16,

you can use this query :

SELECT * from order WHERE CONVERT(VARCHAR(10),order_date,23)='2006-03-16'

you will get this result :

Order id Order date Value
2 2006-03-16 19:08:30.423 350
4 2006-03-16 20:11:25.413 150

CONVERT used for converting datetime datatype to another datatype (in this case we convert to varchar)

CONVERT Syntax :

CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

Arguments :

expression
Is any valid expression.

data_type
Is the target data type. This includes xml, bigint, and sql_variant. Alias data types cannot be used. For more information about available data types, see Data Types (Transact-SQL).

length
Is an optional integer that specifies the length of the target data type. The default value is 30.

style
Is an integer expression that specifies how the CONVERT function is to translate expression. If style is NULL, NULL is returned. The range is determined by data_type. For more information, see the Remarks section.

Here date and time styles for CONVERT function :

 

Without century (yy) (1) With century (yyyy) Standard Input/Output (3)
0 or 100 (1, 2) Default mon dd yyyy hh:miAM (or PM)
1 101 U.S. mm/dd/yyyy
2 102 ANSI yy.mm.dd
3 103 British/French dd/mm/yyyy
4 104 German dd.mm.yy
5 105 Italian dd-mm-yy
6 106 (1) dd mon yy
7 107 (1) Mon dd, yy
8 108 hh:mi:ss
9 or 109 (1, 2) Default + milliseconds mon dd yyyy hh:mi:ss:mmmAM (or PM)
10 110 USA mm-dd-yy
11 111 JAPAN yy/mm/dd
12 112 ISO yymmddyyyymmdd
13 or 113 (1, 2) Europe default + milliseconds dd mon yyyy hh:mi:ss:mmm(24h)
14 114 hh:mi:ss:mmm(24h)
20 or 120 (2) ODBC canonical yyyy-mm-dd hh:mi:ss(24h)
21 or 121 (2) ODBC canonical (with milliseconds) yyyy-mm-dd hh:mi:ss.mmm(24h)
126 (4) ISO8601 yyyy-mm-ddThh:mi:ss.mmm (no spaces)
127(6, 7) ISO8601 with time zone Z. yyyy-mm-ddThh:mi:ss.mmmZ(no spaces)
130 (1, 2) Hijri (5) dd mon yyyy hh:mi:ss:mmmAM
131 (2) Hijri (5) dd/mm/yy hh:mi:ss:mmmAM
23 (2) yyyy-mm-dd

Comments posted (2)

Bingung aku pak maksude…
Campact database iku gawe opo?

keren nih…
kaya di oracle convert format jadi to_char atau to_date yah klo ga salah? :malus

Write a comment

*