By varying the datatype and length, we can get the desired component. Moreover, the style argument in the Convert function is provided exclusively for use with date and time data. Some sample code illustrating the same is as follows:
use pubs
go
---------------- Selecting only the date part from a datetime column --------------
/* Creating a Test Table */
Create Table MyDateTest99
(
DateColumn datetime
)
go
/* Inserting the test value into the table */
insert into MyDateTest99 values (getdate())
go
/* Selecting the result */
select convert(varchar,DateColumn,101) from MyDateTest99
go
/* Performing Cleanup */
drop table MyDateTest99
go
use pubs
go
---------------- Selecting only the date part from a datetime column --------------
/* Creating a Test Table */
Create Table MyDateTest99
(
DateColumn datetime
)
go
/* Inserting the test value into the table */
insert into MyDateTest99 values (getdate())
go
/* Selecting the result */
select convert(varchar,DateColumn,108) from MyDateTest99
go
/* Performing Cleanup */
drop table MyDateTest99
go
The list of styles that can be used are:
Style ID
Style Type
0 or 100mon dd yyyy hh:miAM (or PM)
101 mm/dd/yy
102 yy.mm.dd
103 dd/mm/yy
104 dd.mm.yy
105 dd-mm-yy
106 dd mon yy
107 Mon dd, yy
108 hh:mm:ss
9 or 109mon dd yyyy hh:mi:ss:mmmAM (or PM)
110 mm-dd-yy
111 yy/mm/dd
112 yymmdd
13 or 113dd mon yyyy hh:mm:ss:mmm(24h)
114 hh:mi:ss:mmm(24h)
20 or 120yyyy-mm-dd hh:mi:ss(24h)
21 or 121yyyy-mm-dd hh:mi:ss.mmm(24h)
126 yyyy-mm-dd Thh:mm:ss.mmm(no spaces)
130 dd mon yyyy hh:mi:ss:mmmAM
131 dd/mm/yy hh:mi:ss:mmmAM
These styles are the format of input to be used when converting character data into datetime and format of output while converting datetime data into characters:
use pubs
go
---------------- Example for the demonstration of use of style while input of data--------------
/* Creating a Test Table */
Create Table MyDateTest99
(
DateColumn datetime
)
go
/* Inserting the test values into the table */
-- Inserting in US format
insert into MyDateTest99select convert(datetime,'05/08/2004',101)
-- Inserting in UK format
insert into MyDateTest99select convert(datetime,'08/05/2004',103)
-- Inserting in ISO Format
insert into MyDateTest99select convert(datetime,'20040508',112)
go
/* Selecting the result */
select DateColumn from MyDateTest99
go
/* Performing Cleanup */
drop table MyDateTest99
go
use pubs
go
---------------- Example for the demonstration of use of style while output of data--------------
/* Creating a Test Table */
Create Table MyDateTest99
(
DateColumn datetime
)
go
/* Inserting the test values into the table */
insert into MyDateTest99select convert(datetime,'05/08/2004',101)
go
/* Selecting the result */
-- In US Format
select convert(varchar,DateColumn,101) from MyDateTest99
-- In UK Format
select convert(varchar,DateColumn,103) from MyDateTest99
-- In ISO Format
select convert(varchar,DateColumn,112) from MyDateTest99
go
/* Performing Cleanup */
drop table MyDateTest99
go
Some other functions that can be used for various purposes are DATEADD, DATEDIFF, DATENAME, DATEPART, DAY, GETDATE, MONTH, and YEAR. Here's some further detail on these functions as well as a code sample showing their use:
Dateadd: Returns a new datetime value based on adding an interval to the specified date.
Syntax: DATEADD ( datepart, number, date )
Datediff: Returns the number of date and time boundaries crossed between two specified dates.
Syntax: DATEDIFF ( datepart, startdate, enddate )
Datename: Returns a character string representing the specified datepart of the specified date.
Syntax: DATENAME ( datepart, date )
Datepart: Returns an integer representing the specified datepart of the specified date.
Syntax: DATEPART ( datepart, date )
Day: Returns an integer representing the day datepart of the specified date.
Syntax: DAY ( date )
Getdate: Returns the current system date and time in the Microsoft® SQL Server™ standard internal format for datetime values.
Syntax: GETDATE ( )
Month: Returns an integer that represents the month part of a specified date.
Syntax: MONTH ( date )
Year: Returns an integer that represents the year part of a specified date.
Syntax: YEAR ( date )
declare @datevar datetime
select @datevar = getdate()
/*Example for getdate() : getting current datetime*/
select getdate() [Current Datetime]
/*Example for dateadd : getting date 7 days from current datetime*/
select dateadd(dd, 7, @datevar) [Date 7 days from now]
/*Example for datediff : getting no of days passed since 01-01-2004*/
select datediff(dd,'20040101',@datevar) [No of days since 01-01-2004]
/*Example for datename : getting month name*/
select datename(mm, @datevar) [Month Name]
/*Example for datepart : getting week from date*/
select datepart(wk, @datevar ) [Week No]
/*Example for day : getting day part of date*/
select day (@datevar) [Day]
/*Example for month : getting month part of date*/
select month(@datevar) [Month]
/*Example for year : getting year part of date*/
select year(@datevar) [Year]
Now I will provide you with some code samples which you can use for various tasks. I will try to include as many examples I can think of, but this list is not exhaustive:
A very common question asked in forums is regarding the change from a character column to a datetime column. The error encountered by developers is :
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
This is common because the varchar column does not provide any validations against the data and as a result, some invalid entries creep in. So, while converting to datetime, SQL Server is not able to change the character data to datetime and throws up an error. The easiest way to identify the rows that are causing problems and contain invalid datetime data is by using the isdate() function:
/* Example to show how to find invalid records */
use pubs
go
/* Creating a Test Table */
Create Table MyDateTest99
(
DateColumn varchar(8)
)
go
/* Inserting the test value into the table */
insert into MyDateTest99 select '19780129'
insert into MyDateTest99 select '19670229'
insert into MyDateTest99 select '19910112'
insert into MyDateTest99 select '19791016'
go
/* Selecting the result */
select
DateColumn
from
MyDateTest99
where
isdate(DateColumn) = 0
go
/* Performing Cleanup */
drop table MyDateTest99
Delphi - .....................................
sql server dogum gunu bulma
use pubs
go
---------------- Inserting only the time part into a datetime column --------------
/* Creating a Test Table */
Create Table MyDateTest99
(
DateColumn datetime
)
go
/* Inserting the test value into the table */
insert into MyDateTest99 values ('10:00 AM')
go
/* Selecting the result */
select DateColumn from MyDateTest99
go
/* Performing Cleanup */
drop table MyDateTest99
go
---------------- Inserting only the date part into a datetime column --------------
use pubs
go
/* Creating a Test Table */
Create Table MyDateTest99
(
DateColumn datetime
)
go
/* Inserting the test value into the table */
insert into MyDateTest99 values ('January 1, 2000')
go
/* Selecting the result */
select DateColumn from MyDateTest99
go
/* Performing Cleanup */
drop table MyDateTest99
go
So, the most common question that is asked is:
Q: How do I get SQL Server to return only the Date component or only the Time component from the datetime data type?
A: By using the Convert function. The syntax for using the convert function is:
By varying the datatype and length, we can get the desired component. Moreover, the style argument in the Convert function is provided exclusively for use with date and time data. Some sample code illustrating the same is as follows:
use pubs
go
---------------- Selecting only the date part from a datetime column --------------
/* Creating a Test Table */
Create Table MyDateTest99
(
DateColumn datetime
)
go
/* Inserting the test value into the table */
insert into MyDateTest99 values (getdate())
go
/* Selecting the result */
select convert(varchar,DateColumn,101) from MyDateTest99
go
/* Performing Cleanup */
drop table MyDateTest99
go
use pubs
go
---------------- Selecting only the date part from a datetime column --------------
/* Creating a Test Table */
Create Table MyDateTest99
(
DateColumn datetime
)
go
/* Inserting the test value into the table */
insert into MyDateTest99 values (getdate())
go
/* Selecting the result */
select convert(varchar,DateColumn,108) from MyDateTest99
go
/* Performing Cleanup */
drop table MyDateTest99
go
The list of styles that can be used are:
Style ID
Style Type
0 or 100mon dd yyyy hh:miAM (or PM)
101 mm/dd/yy
102 yy.mm.dd
103 dd/mm/yy
104 dd.mm.yy
105 dd-mm-yy
106 dd mon yy
107 Mon dd, yy
108 hh:mm:ss
9 or 109mon dd yyyy hh:mi:ss:mmmAM (or PM)
110 mm-dd-yy
111 yy/mm/dd
112 yymmdd
13 or 113dd mon yyyy hh:mm:ss:mmm(24h)
114 hh:mi:ss:mmm(24h)
20 or 120yyyy-mm-dd hh:mi:ss(24h)
21 or 121yyyy-mm-dd hh:mi:ss.mmm(24h)
126 yyyy-mm-dd Thh:mm:ss.mmm(no spaces)
130 dd mon yyyy hh:mi:ss:mmmAM
131 dd/mm/yy hh:mi:ss:mmmAM
These styles are the format of input to be used when converting character data into datetime and format of output while converting datetime data into characters:
use pubs
go
---------------- Example for the demonstration of use of style while input of data--------------
/* Creating a Test Table */
Create Table MyDateTest99
(
DateColumn datetime
)
go
/* Inserting the test values into the table */
-- Inserting in US format
insert into MyDateTest99select convert(datetime,'05/08/2004',101)
-- Inserting in UK format
insert into MyDateTest99select convert(datetime,'08/05/2004',103)
-- Inserting in ISO Format
insert into MyDateTest99select convert(datetime,'20040508',112)
go
/* Selecting the result */
select DateColumn from MyDateTest99
go
/* Performing Cleanup */
drop table MyDateTest99
go
use pubs
go
---------------- Example for the demonstration of use of style while output of data--------------
/* Creating a Test Table */
Create Table MyDateTest99
(
DateColumn datetime
)
go
/* Inserting the test values into the table */
insert into MyDateTest99select convert(datetime,'05/08/2004',101)
go
/* Selecting the result */
-- In US Format
select convert(varchar,DateColumn,101) from MyDateTest99
-- In UK Format
select convert(varchar,DateColumn,103) from MyDateTest99
-- In ISO Format
select convert(varchar,DateColumn,112) from MyDateTest99
go
/* Performing Cleanup */
drop table MyDateTest99
go
Some other functions that can be used for various purposes are DATEADD, DATEDIFF, DATENAME, DATEPART, DAY, GETDATE, MONTH, and YEAR. Here's some further detail on these functions as well as a code sample showing their use:
Dateadd: Returns a new datetime value based on adding an interval to the specified date.
Syntax: DATEADD ( datepart, number, date )
Datediff: Returns the number of date and time boundaries crossed between two specified dates.
Syntax: DATEDIFF ( datepart, startdate, enddate )
Datename: Returns a character string representing the specified datepart of the specified date.
Syntax: DATENAME ( datepart, date )
Datepart: Returns an integer representing the specified datepart of the specified date.
Syntax: DATEPART ( datepart, date )
Day: Returns an integer representing the day datepart of the specified date.
Syntax: DAY ( date )
Getdate: Returns the current system date and time in the Microsoft® SQL Server™ standard internal format for datetime values.
Syntax: GETDATE ( )
Month: Returns an integer that represents the month part of a specified date.
Syntax: MONTH ( date )
Year: Returns an integer that represents the year part of a specified date.
Syntax: YEAR ( date )
declare @datevar datetime
select @datevar = getdate()
/*Example for getdate() : getting current datetime*/
select getdate() [Current Datetime]
/*Example for dateadd : getting date 7 days from current datetime*/
select dateadd(dd, 7, @datevar) [Date 7 days from now]
/*Example for datediff : getting no of days passed since 01-01-2004*/
select datediff(dd,'20040101',@datevar) [No of days since 01-01-2004]
/*Example for datename : getting month name*/
select datename(mm, @datevar) [Month Name]
/*Example for datepart : getting week from date*/
select datepart(wk, @datevar ) [Week No]
/*Example for day : getting day part of date*/
select day (@datevar) [Day]
/*Example for month : getting month part of date*/
select month(@datevar) [Month]
/*Example for year : getting year part of date*/
select year(@datevar) [Year]
Now I will provide you with some code samples which you can use for various tasks. I will try to include as many examples I can think of, but this list is not exhaustive:
A very common question asked in forums is regarding the change from a character column to a datetime column. The error encountered by developers is :
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
This is common because the varchar column does not provide any validations against the data and as a result, some invalid entries creep in. So, while converting to datetime, SQL Server is not able to change the character data to datetime and throws up an error. The easiest way to identify the rows that are causing problems and contain invalid datetime data is by using the isdate() function:
