How to convert a date in tsql

Written by cathlene s. baptista
  • Share
  • Tweet
  • Share
  • Pin
  • Email
How to convert a date in tsql
Use the convert T-SQL function to convert dates. (Sunday to Saturday monthly calendar, 2008 Year image by Stasys Eidiejus from Fotolia.com)

T-SQL, or Transact-SQL, adds transaction control, row processing, variables and exception handling to SQL for Microsoft and Sybase databases. Database programmers can use T-SQL to create local variables and procedures and to add flow control language elements to database transactions, taking advantage of the flexibility and control offered by procedural languages. T-SQL provides support functions for string, mathematical and date functions, making it a good option for developers that want more programmatic solutions for their database processes.

Skill level:
Easy

Other People Are Reading

Instructions

  1. 1

    Connect to the default SQL Server instance using the SQLCMD utility by opening a DOS prompt and typing SQLCMD.

  2. 2

    Declare a local variable, named "@mydate" of type "datetime" and hit the "Enter" key. The "@mydate" variable will hold the date to be converted. Add a return after the variable declaration. For example:

    1>declare @mydate datetime

  3. 3

    Retrieve the current date using the "getdate() Transact-SQL" function. Assign the current date value to the "@mydate" variable and add a return.

    1>declare @mydate datetime

    2>set @mydate=getdate()

  4. 4

    Print "@mydate" to the screen using the print statement and type a hard return. Printing "@mydate" will display the variable's value before it is converted.

    1>declare @mydate datetime

    2>set @mydate=getdate()

    3>print @mydate

  5. 5

    Use the convert T-SQL statement to convert the value of "@mydate" to a "varchar(10)" data type. The "convert()" command takes three values: The data type to which the date should be converted, the variable to be converted and a style code. In this example, the style code "101" converts the "@mydate" variable to the data type" varchar(10)" in the format mm/dd/yy. Follow the convert statement with a hard return.

    1>declare @mydate datetime

    2>set @mydate=getdate()

    3>print @mydate

    4>select convert(varchar(10),@mydate,101)

  6. 6

    Print "@mydate" to the screen using the print statement and type a hard return.

    1>declare @mydate datetime

    2>set @mydate=getdate()

    3>print @mydate

    4>select convert(varchar(10),@mydate,101)

    5>print @mydate

  7. 7

    Type the "GO" command and hit "Enter." The datetime value of "@mydate" will print to the screen in its original format (pre-conversion) and the "varchar(10)" value of "@mydate" will print to the screen in mm/dd/yy format (post-conversion).

Tips and warnings

  • The T-SQL "CONVERT" statement works in the same way as the T-SQL "CAST" statement, but provides a style argument when converting dates to strings.
  • T-SQL offers a variety of functions for manipulating dates and their formats. There are many style codes available for use with "CONVERT."
  • The "sql_variant_property" can be used to determine the base type of a converted datetime value.
  • Be sure to consider data type capacity when converting dates.

Don't Miss

Filter:
  • All types
  • Articles
  • Slideshows
  • Videos
Sort:
  • Most relevant
  • Most popular
  • Most recent

No articles available

No slideshows available

No videos available

By using the eHow.co.uk site, you consent to the use of cookies. For more information, please see our Cookie policy.