datetime vs varchar

If I insert datatime values in a varchar datatype as opposed to datetime,
what am I losing out on ?
Would I be able to perform the same functions against a varchar datatype as
opposed to datetime such as datepart, datediff,etc. ?
Hi Hassan
Why don't you try it and see? Something like this should give you a start:
declare @.today varchar(30)
select @.today = getdate()
select @.today
select dateadd(mm, 1, @.today)
And then read Tibor's excellent article:
http://www.karaszi.com/sqlserver/info_datetime.asp
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Hassan" <hassan@.test.com> wrote in message
news:uDdP0$EbIHA.748@.TK2MSFTNGP04.phx.gbl...
> If I insert datatime values in a varchar datatype as opposed to datetime,
> what am I losing out on ?
> Would I be able to perform the same functions against a varchar datatype
> as opposed to datetime such as datepart, datediff,etc. ?
>
>
|||Kalen,
Does not seem to be any difference with that example.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:ODKGXIFbIHA.5980@.TK2MSFTNGP04.phx.gbl...
> Hi Hassan
> Why don't you try it and see? Something like this should give you a start:
> declare @.today varchar(30)
> select @.today = getdate()
> select @.today
> select dateadd(mm, 1, @.today)
> And then read Tibor's excellent article:
> http://www.karaszi.com/sqlserver/info_datetime.asp
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://blog.kalendelaney.com
>
> "Hassan" <hassan@.test.com> wrote in message
> news:uDdP0$EbIHA.748@.TK2MSFTNGP04.phx.gbl...
>
|||Hassan would have found all that out if he read the article I pointed him
to.
;-)
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uB0OLMIbIHA.1376@.TK2MSFTNGP02.phx.gbl...
> Downsides of using varchar include:
> There's nothing stopping you from inserting invalid date (like Feb 30), or
> time values.
> Performing various datetime calculation might mean bad performance, since
> you might end up with a convert on the column side in your predicate.
> You need to decide on a format. If you want to retrieve the value and
> present it in a different format from what it is stored with you have more
> work to do.
> ...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Hassan" <hassan@.test.com> wrote in message
> news:evp1jaFbIHA.4180@.TK2MSFTNGP06.phx.gbl...
>
|||On Feb 11, 6:46Xam, "Hassan" <has...@.test.com> wrote:
> If I insert datatime values in a varchar datatype as opposed to datetime,
> what am I losing out on ?
> Would I be able to perform the same functions against a varchar datatype as
> opposed to datetime such as datepart, datediff,etc. ?
Nothing but you would end up with too much convertions if you want to
manipulate varchars
(ex order by, usage of datediff, dateadd,etc)
Always use proper DATETIME datatype to store dates and let your front
end application do the formation
sql

datetime vs varchar

If I insert datatime values in a varchar datatype as opposed to datetime,
what am I losing out on ?
Would I be able to perform the same functions against a varchar datatype as
opposed to datetime such as datepart, datediff,etc. ?Hi Hassan
Why don't you try it and see? Something like this should give you a start:
declare @.today varchar(30)
select @.today = getdate()
select @.today
select dateadd(mm, 1, @.today)
And then read Tibor's excellent article:
http://www.karaszi.com/sqlserver/info_datetime.asp
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Hassan" <hassan@.test.com> wrote in message
news:uDdP0$EbIHA.748@.TK2MSFTNGP04.phx.gbl...
> If I insert datatime values in a varchar datatype as opposed to datetime,
> what am I losing out on ?
> Would I be able to perform the same functions against a varchar datatype
> as opposed to datetime such as datepart, datediff,etc. ?
>
>|||Kalen,
Does not seem to be any difference with that example.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:ODKGXIFbIHA.5980@.TK2MSFTNGP04.phx.gbl...
> Hi Hassan
> Why don't you try it and see? Something like this should give you a start:
> declare @.today varchar(30)
> select @.today = getdate()
> select @.today
> select dateadd(mm, 1, @.today)
> And then read Tibor's excellent article:
> http://www.karaszi.com/sqlserver/info_datetime.asp
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://blog.kalendelaney.com
>
> "Hassan" <hassan@.test.com> wrote in message
> news:uDdP0$EbIHA.748@.TK2MSFTNGP04.phx.gbl...
>> If I insert datatime values in a varchar datatype as opposed to datetime,
>> what am I losing out on ?
>> Would I be able to perform the same functions against a varchar datatype
>> as opposed to datetime such as datepart, datediff,etc. ?
>>
>|||Downsides of using varchar include:
There's nothing stopping you from inserting invalid date (like Feb 30), or time values.
Performing various datetime calculation might mean bad performance, since you might end up with a
convert on the column side in your predicate.
You need to decide on a format. If you want to retrieve the value and present it in a different
format from what it is stored with you have more work to do.
...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Hassan" <hassan@.test.com> wrote in message news:evp1jaFbIHA.4180@.TK2MSFTNGP06.phx.gbl...
> Kalen,
> Does not seem to be any difference with that example.
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:ODKGXIFbIHA.5980@.TK2MSFTNGP04.phx.gbl...
>> Hi Hassan
>> Why don't you try it and see? Something like this should give you a start:
>> declare @.today varchar(30)
>> select @.today = getdate()
>> select @.today
>> select dateadd(mm, 1, @.today)
>> And then read Tibor's excellent article:
>> http://www.karaszi.com/sqlserver/info_datetime.asp
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://blog.kalendelaney.com
>>
>> "Hassan" <hassan@.test.com> wrote in message news:uDdP0$EbIHA.748@.TK2MSFTNGP04.phx.gbl...
>> If I insert datatime values in a varchar datatype as opposed to datetime, what am I losing out
>> on ?
>> Would I be able to perform the same functions against a varchar datatype as opposed to datetime
>> such as datepart, datediff,etc. ?
>>
>>
>|||Hassan would have found all that out if he read the article I pointed him
to.
;-)
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uB0OLMIbIHA.1376@.TK2MSFTNGP02.phx.gbl...
> Downsides of using varchar include:
> There's nothing stopping you from inserting invalid date (like Feb 30), or
> time values.
> Performing various datetime calculation might mean bad performance, since
> you might end up with a convert on the column side in your predicate.
> You need to decide on a format. If you want to retrieve the value and
> present it in a different format from what it is stored with you have more
> work to do.
> ...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Hassan" <hassan@.test.com> wrote in message
> news:evp1jaFbIHA.4180@.TK2MSFTNGP06.phx.gbl...
>> Kalen,
>> Does not seem to be any difference with that example.
>> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>> news:ODKGXIFbIHA.5980@.TK2MSFTNGP04.phx.gbl...
>> Hi Hassan
>> Why don't you try it and see? Something like this should give you a
>> start:
>> declare @.today varchar(30)
>> select @.today = getdate()
>> select @.today
>> select dateadd(mm, 1, @.today)
>> And then read Tibor's excellent article:
>> http://www.karaszi.com/sqlserver/info_datetime.asp
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://blog.kalendelaney.com
>>
>> "Hassan" <hassan@.test.com> wrote in message
>> news:uDdP0$EbIHA.748@.TK2MSFTNGP04.phx.gbl...
>> If I insert datatime values in a varchar datatype as opposed to
>> datetime, what am I losing out on ?
>> Would I be able to perform the same functions against a varchar
>> datatype as opposed to datetime such as datepart, datediff,etc. ?
>>
>>
>>
>|||On Feb 11, 6:46=A0am, "Hassan" <has...@.test.com> wrote:
> If I insert datatime values in a varchar datatype as opposed to datetime,
> what am I losing out on ?
> Would I be able to perform the same functions against a varchar datatype a=s
> opposed to datetime such as datepart, datediff,etc. ?
Nothing but you would end up with too much convertions if you want to
manipulate varchars
(ex order by, usage of datediff, dateadd,etc)
Always use proper DATETIME datatype to store dates and let your front
end application do the formation|||> Hassan would have found all that out if he read the article I pointed him to.
Ah, that ol' article ;-)
Thanks Kalen :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:OkINH5MbIHA.1376@.TK2MSFTNGP02.phx.gbl...
> Hassan would have found all that out if he read the article I pointed him to.
> ;-)
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://blog.kalendelaney.com
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:uB0OLMIbIHA.1376@.TK2MSFTNGP02.phx.gbl...
>> Downsides of using varchar include:
>> There's nothing stopping you from inserting invalid date (like Feb 30), or time values.
>> Performing various datetime calculation might mean bad performance, since you might end up with a
>> convert on the column side in your predicate.
>> You need to decide on a format. If you want to retrieve the value and present it in a different
>> format from what it is stored with you have more work to do.
>> ...
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Hassan" <hassan@.test.com> wrote in message news:evp1jaFbIHA.4180@.TK2MSFTNGP06.phx.gbl...
>> Kalen,
>> Does not seem to be any difference with that example.
>> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>> news:ODKGXIFbIHA.5980@.TK2MSFTNGP04.phx.gbl...
>> Hi Hassan
>> Why don't you try it and see? Something like this should give you a start:
>> declare @.today varchar(30)
>> select @.today = getdate()
>> select @.today
>> select dateadd(mm, 1, @.today)
>> And then read Tibor's excellent article:
>> http://www.karaszi.com/sqlserver/info_datetime.asp
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://blog.kalendelaney.com
>>
>> "Hassan" <hassan@.test.com> wrote in message news:uDdP0$EbIHA.748@.TK2MSFTNGP04.phx.gbl...
>> If I insert datatime values in a varchar datatype as opposed to datetime, what am I losing out
>> on ?
>> Would I be able to perform the same functions against a varchar datatype as opposed to
>> datetime such as datepart, datediff,etc. ?
>>
>>
>>
>>
>

DATEPART doesn't match BETWEEN... grrr

Hi,
I'm trying to report a months data, but the results differ when I use
DATEPART as opposed to BETWEEN
i.e. (The full queries are at the end of this post)
this...
where datepart(month,sessionstart) = 1
and datepart(year,sessionstart) = 2005
differs from this...
where sessionstart between '01/01/2005' and '01/31/2005'
In BOL, it states for between... "BETWEEN returns TRUE if the value of
test_expression is greater than or equal to the value of begin_expression an
d
less than or equal to the value of end_expression.", and yet if I change the
between statement to..
where sessionstart between '01/01/2005' and '02/01/2005'
I then get the same results as DATEPART.
Am I going loopy? (It does feel like it). I guess it may be pointing to an
issue in BOL.
Many thanks in advance,
Andy
The full queries... (which measure application usage in citrix metaframe )
--Query #1
select count(*) as 'Session count', lu_appname.appname as 'Published
Application'
from sdb_session
join LU_APPNAME on sdb_session.FK_APPNAMEID = LU_APPNAME.PK_APPNAMEID
where datepart(month,sessionstart) = 1
and datepart(year,sessionstart) = 2005
group by lu_appname.appname
order by lu_appname.appname
--Query #2
select count(*) as 'Session count', lu_appname.appname as 'Published
Application'
from sdb_session
join LU_APPNAME on sdb_session.FK_APPNAMEID = LU_APPNAME.PK_APPNAMEID
where sessionstart between '01/01/2005' and '01/31/2005'
group by lu_appname.appname
order by lu_appname.appnameAndy,
It could be because the datetime data type also include time part and when
you specify a constant like '01/31/2005', sql server is using '00:00:00.000'
as the time. Basically what you are doing is:
...
where sessionstart >= '2005-01-01T00:00:00.000' and sessionstart <=
'2005-01-31T00:00:00.00'
try this way:
...
where sessionstart >= '01/01/2005' and sessionstart < '02/01/2005'
AMB
"Andy" wrote:

> Hi,
> I'm trying to report a months data, but the results differ when I use
> DATEPART as opposed to BETWEEN
> i.e. (The full queries are at the end of this post)
> this...
> where datepart(month,sessionstart) = 1
> and datepart(year,sessionstart) = 2005
> differs from this...
> where sessionstart between '01/01/2005' and '01/31/2005'
> In BOL, it states for between... "BETWEEN returns TRUE if the value of
> test_expression is greater than or equal to the value of begin_expression
and
> less than or equal to the value of end_expression.", and yet if I change t
he
> between statement to..
> where sessionstart between '01/01/2005' and '02/01/2005'
> I then get the same results as DATEPART.
> Am I going loopy? (It does feel like it). I guess it may be pointing to an
> issue in BOL.
> Many thanks in advance,
> Andy
>
>
> The full queries... (which measure application usage in citrix metaframe )
> --Query #1
> select count(*) as 'Session count', lu_appname.appname as 'Published
> Application'
> from sdb_session
> join LU_APPNAME on sdb_session.FK_APPNAMEID = LU_APPNAME.PK_APPNAMEID
> where datepart(month,sessionstart) = 1
> and datepart(year,sessionstart) = 2005
> group by lu_appname.appname
> order by lu_appname.appname
> --Query #2
> select count(*) as 'Session count', lu_appname.appname as 'Published
> Application'
> from sdb_session
> join LU_APPNAME on sdb_session.FK_APPNAMEID = LU_APPNAME.PK_APPNAMEID
> where sessionstart between '01/01/2005' and '01/31/2005'
> group by lu_appname.appname
> order by lu_appname.appname
>|||How are the results different? And which gives you the proper resulst
(looking at sessionstart in the select clause) BETWEEN is rarely a good
thing to use with dates. what you are saying in your query is anything
between midnight of the first and midnight (the start of the day) on the
31st. Use:
sessionstart >= '20050101' and sessionstart < '20050201'
This will give you the full month (note you should also no use the date
format you are using, because it can be confusing, and has different meaning
in some situations, use YYYYMMDD and you will never have an issue. The
datepart thing will work, but you will not likely get good index usage.
--
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Andy" <Andy@.discussions.microsoft.com> wrote in message
news:41013C4E-5432-4A35-94F2-1FD77FAC6B7D@.microsoft.com...
> Hi,
> I'm trying to report a months data, but the results differ when I use
> DATEPART as opposed to BETWEEN
> i.e. (The full queries are at the end of this post)
> this...
> where datepart(month,sessionstart) = 1
> and datepart(year,sessionstart) = 2005
> differs from this...
> where sessionstart between '01/01/2005' and '01/31/2005'
> In BOL, it states for between... "BETWEEN returns TRUE if the value of
> test_expression is greater than or equal to the value of begin_expression
> and
> less than or equal to the value of end_expression.", and yet if I change
> the
> between statement to..
> where sessionstart between '01/01/2005' and '02/01/2005'
> I then get the same results as DATEPART.
> Am I going loopy? (It does feel like it). I guess it may be pointing to an
> issue in BOL.
> Many thanks in advance,
> Andy
>
>
> The full queries... (which measure application usage in citrix metaframe )
> --Query #1
> select count(*) as 'Session count', lu_appname.appname as 'Published
> Application'
> from sdb_session
> join LU_APPNAME on sdb_session.FK_APPNAMEID = LU_APPNAME.PK_APPNAMEID
> where datepart(month,sessionstart) = 1
> and datepart(year,sessionstart) = 2005
> group by lu_appname.appname
> order by lu_appname.appname
> --Query #2
> select count(*) as 'Session count', lu_appname.appname as 'Published
> Application'
> from sdb_session
> join LU_APPNAME on sdb_session.FK_APPNAMEID = LU_APPNAME.PK_APPNAMEID
> where sessionstart between '01/01/2005' and '01/31/2005'
> group by lu_appname.appname
> order by lu_appname.appname
>|||Many thanks for that, date formatting here in the UK is always a pain due to
most products being US-Centric, so yyyy/mm/dd would seem to be a good idea.
It seems like using between is more trouble than its worth.
Thanks again to all who posted.
Andy
Louis Davidson" wrote:

> How are the results different? And which gives you the proper resulst
> (looking at sessionstart in the select clause) BETWEEN is rarely a good
> thing to use with dates. what you are saying in your query is anything
> between midnight of the first and midnight (the start of the day) on the
> 31st. Use:
> sessionstart >= '20050101' and sessionstart < '20050201'
> This will give you the full month (note you should also no use the date
> format you are using, because it can be confusing, and has different meani
ng
> in some situations, use YYYYMMDD and you will never have an issue. The
> datepart thing will work, but you will not likely get good index usage.
> --
> ----
--
> Louis Davidson - drsql@.hotmail.com
> SQL Server MVP
> Compass Technology Management - www.compass.net
> Pro SQL Server 2000 Database Design -
> http://www.apress.com/book/bookDisplay.html?bID=266
> Note: Please reply to the newsgroups only unless you are interested in
> consulting services. All other replies may be ignored :)
> "Andy" <Andy@.discussions.microsoft.com> wrote in message
> news:41013C4E-5432-4A35-94F2-1FD77FAC6B7D@.microsoft.com...
>
>|||oh, I forgot to mention...
You have inferred that Datepart is not a good choice of syntax because of
bad index usage.
Is there a reason for this?
Many thanks - I know this is probably going of on a tangent...|||Google for "search arguments".
Transact-SQL Query
SQL Server Performance Tuning Tips
http://www.sql-server-performance.com/transact_sql.asp
AMB
"Andy" wrote:

> oh, I forgot to mention...
> You have inferred that Datepart is not a good choice of syntax because of
> bad index usage.
> Is there a reason for this?
> Many thanks - I know this is probably going of on a tangent...
>|||the basic issue is that if you manipulate the value, then do comparisons to
it, SQL Server cannot then unmanipulate it and see if an index would help.
For your example:
where datepart(month,sessionstart) = 1
and datepart(year,sessionstart) = 2005
The index is on sessionstart, so it is ordered in a given way, so if the
optimizer knows what you are looking for is 20050101 <= sessionstart <
20050201, it is simple to do an index scan. The index is not ordered in
the order of the dateparts, so it cannot be used (except for a covering
query, if all you need is sessionstart, but then it would have to look at
all values of sessionstart)
Could it be built to do this? Probably, but only because you included the
year (the most significant factor in the index) but it would be more costly,
and there are common ways of writing these queries that work (as we have
been discussing)
And like Alejandro says,
"Google for 'search arguments'
Transact-SQL Query
SQL Server Performance Tuning Tips
http://www.sql-server-performance.com/transact_sql.asp"
for lots more reading on the subject.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Andy" <Andy@.discussions.microsoft.com> wrote in message
news:30F05FEC-7BCD-495F-984E-9A12BBBFF881@.microsoft.com...
> oh, I forgot to mention...
> You have inferred that Datepart is not a good choice of syntax because of
> bad index usage.
> Is there a reason for this?
> Many thanks - I know this is probably going of on a tangent...
>|||On Tue, 1 Feb 2005 10:27:05 -0800, Andy wrote:

>Many thanks for that, date formatting here in the UK is always a pain due t
o
>most products being US-Centric, so yyyy/mm/dd would seem to be a good idea.
Hi Andy,
Though it might seem to be a good idea, it really isn't.
SQL Server supports many international date notations. That can be great,
but it can also be a pain - especially when SQL Server starts interpreting
date constants using a format you didn't intend it to. YYYY/MM/DD might
seem obvious, but there are countries where YYYY/DD/MM is used, and SQL
Server is aware of that...
The only formats that are truly unambiguous are:
* yyyymmdd (no slashes, dots, hyphens or other interpunction)
--> for date only (time part will default to midnight)
* yyyy-mm-ddThh:mm:ss.ttt (note the hyphens, colons, dot and capital T)
--> for date and time (the milliseconds part [.ttt] is optional)

>It seems like using between is more trouble than its worth.
This is absolutely true for date ranges..
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)