Tuesday, June 5, 2007

SQL CAST and CONVERT

It converts an expression from one data type to another.
CAST and CONVERT have similar functionality.

SQL CAST and CONVERT Syntax
Using CAST:
CAST ( expression AS data_type )


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

Example of SQL Cast and Convert

SQL Cast and Convert - String
SELECT SUBSTRING('CAST and CONVERT', 1, 3)
Return Value = CAS (it get from index 1 to 3)

SELECT CAST('CAST and CONVERT' AS char(3))
Return Value = CAS (it get 3 char only)


SQL Cast and Convert - Date Time
-Converting date time to character data(vachar)
-The default values (style 0 or 100, 9 or 109, 13 or 113, 20 or 120, and 21 or 121) always return the Without century year(yy).
-Add 100 to a style value to get a four-place year that includes the century year(yyyy).
-Below is example for converting 1 format of date time to different format of date time, so that it can be use in various condition.

Value of current Date Time GETDATE()
SELECT (GETDATE()) = 2007-06-06 23:41:10.153


SELECT CONVERT(varchar,GETDATE(),0)
Return Value = Jun 6 2007 11:07PM
SELECT CONVERT(varchar,GETDATE(),100)
Return Value = Jun 6 2007 11:07PM


SELECT CONVERT(varchar,GETDATE(),1)
Return Value = 06/06/07
SELECT CONVERT(varchar,GETDATE(),101)
Return Value = 06/06/2007


SELECT CONVERT(varchar,GETDATE(),2)
Return Value = 07.06.06
SELECT CONVERT(varchar,GETDATE(),102)
Return Value = 2007.06.06


SELECT CONVERT(varchar,GETDATE(),3)
Return Value = 06/06/07
SELECT CONVERT(varchar,GETDATE(),103)
Return Value = 06/06/2007


SELECT CONVERT(varchar,GETDATE(),4)
Return Value = 06.06.07
SELECT CONVERT(varchar,GETDATE(),104)
Return Value = 06.06.2007


SELECT CONVERT(varchar,GETDATE(),5)
Return Value = 06-06-07
SELECT CONVERT(varchar,GETDATE(),105)
Return Value = 06-06-2007


SELECT CONVERT(varchar,GETDATE(),6)
Return Value = 06 Jun 07
SELECT CONVERT(varchar,GETDATE(),106)
Return Value = 06 Jun 2007


SELECT CONVERT(varchar,GETDATE(),7)
Return Value = Jun 06, 07
SELECT CONVERT(varchar,GETDATE(),107)
Return Value = Jun 06, 2007


SELECT CONVERT(varchar,GETDATE(),8)
Return Value = 23:38:49
SELECT CONVERT(varchar,GETDATE(),108)
Return Value = 23:38:49


SELECT CONVERT(varchar,GETDATE(),9)
Return Value = Jun 6 2007 11:39:17:060PM
SELECT CONVERT(varchar,GETDATE(),109)
Return Value = Jun 6 2007 11:39:17:060PM


SELECT CONVERT(varchar,GETDATE(),10)
Return Value = 06-06-07
SELECT CONVERT(varchar,GETDATE(),110)
Return Value = 06-06-2007


SELECT CONVERT(varchar,GETDATE(),11)
Return Value = 07/06/06
SELECT CONVERT(varchar,GETDATE(),111)
Return Value = 2007/06/06


SELECT CONVERT(varchar,GETDATE(),12)
Return Value = 070606
SELECT CONVERT(varchar,GETDATE(),112)
Return Value = 20070606


SELECT CONVERT(varchar,GETDATE(),13)
Return Value = 06 Jun 2007 23:40:14:577
SELECT CONVERT(varchar,GETDATE(),113)
Return Value = 06 Jun 2007 23:40:14:577


SELECT CONVERT(varchar,GETDATE(),14)
Return Value = 23:40:29:717
SELECT CONVERT(varchar,GETDATE(),114)
Return Value = 23:40:29:717


SELECT CONVERT(varchar,GETDATE(),20)
Return Value = 2007-06-06 23:40:51
SELECT CONVERT(varchar,GETDATE(),120)
Return Value = 2007-06-06 23:40:51


SELECT CONVERT(varchar,GETDATE(),21)
Return Value = 2007-06-06 23:41:10.153
SELECT CONVERT(varchar,GETDATE(),121)
Return Value = 2007-06-06 23:41:10.153


SELECT CONVERT(varchar,GETDATE(),126)
Return Value = 2007-06-06T23:41:10.153


SELECT CONVERT(varchar,GETDATE(),131)
Return Value = 21/05/1428 11:41:10:153PM

12 comments:

Ashley said...

WoW nice blog buddy!Actually WoW is my favorite appreciation keyword. I also like to play. Wow Gold. I was looking for info regarding that and saw your website. Its nice and very interesting.

Mind said...

wow gold kaufen

Good post

Dustin said...

Hi, i have a Problem with this sql strings between 2005 and 2008

I just have an string like this cast(CONVERT(varchar(8), logic.dDatelastmodify, 112) AS datetime) = CAST(GETDATE() AS date)

It works fine in 2008 but not in 2005

thanks Dustin

formano kunsthandwerk

draganica said...

run into your articles by accident...
realy nice!
and helpfull!

stephen said...

Using June 6 is a bad date to choose, as it isn't very instructive for differentiating between US (MM/DD/YYYY) and English (DD/MM/YYYY) formatted dates, as both resolve to 6/6.

103 = DD/MM/YYYY
101 = MM/DD/YYYY

So June 21, 2011

103 = 21/6/2011
101 = 6/21/2011

malar said...

I actually enjoyed reading through this posting.Many thanks.











Data Conversion Services India

Makrant Iyengar said...

Amazing work, Keep it up!!!!!!!!!!!!!!!!

Makrant Iyengar said...

Amazing work !!

Makrant Iyengar said...

Amazing work, Keep it up!!!!!!!!!!!!!!!!

Cindy Dy said...

I like the way on how you put up your blogs. Wonderful and awesome. Hope to read more post from you in the future. Goodluck. Happy blogging!

Bubble
www.gofastek.com

Silvia Jacinto said...

As a person you should know what are your priorities and goals so that it is easier for you to determine and decide when is the time to give up or go on.
Thanks for sharing such a wonderful article, I hope you could inspire more people. Visit my site for an offer you wouldn't want to refuse.

n8fan.net

www.n8fan.net

sarah lee said...

I really enjoyed reading your article. I found this as an informative and interesting post, so i think it is very useful and knowledgeable. I would like to thank you for the effort you have made in writing this article.


edupdf.org