Date/Time Stamp: datatype

Showing posts with label datatype. Show all posts

Thursday, March 29, 2012

date range query not returning anticipated results

Newbie question. When I query a field (datetime datatype) the results appear as if it were looking at text. There is no time data stored in the field.
Query:
select startdate
from auditlog
where startdate between '6/18/2004' and '6/20/2004'
I get results like this, with dates outside the specified range:
6/18/2004
6/18/2004
6/2/2004
6/19/2003
6/19/2003
6/2/2004
6/2/2004
6/2/2004
Any suggestions?
Any difference if you do it this way?
select startdate
from auditlog
where startdate between '20040618' and '20040620'
Andrew J. Kelly SQL MVP
"Yayahim" <Yayahim@.discussions.microsoft.com> wrote in message
news:C9A1AD19-40FE-42CE-BAC1-C69353F5F458@.microsoft.com...
> Newbie question. When I query a field (datetime datatype) the results
appear as if it were looking at text. There is no time data stored in the
field.
> Query:
> select startdate
> from auditlog
> where startdate between '6/18/2004' and '6/20/2004'
> I get results like this, with dates outside the specified range:
> 6/18/2004
> 6/18/2004
> 6/2/2004
> 6/19/2003
> 6/19/2003
> 6/2/2004
> 6/2/2004
> 6/2/2004
> Any suggestions?
|||Any difference if you do it this way?
select startdate
from auditlog
where startdate between '20040618' and '20040620'
Andrew J. Kelly SQL MVP
"Yayahim" <Yayahim@.discussions.microsoft.com> wrote in message
news:C9A1AD19-40FE-42CE-BAC1-C69353F5F458@.microsoft.com...
> Newbie question. When I query a field (datetime datatype) the results
appear as if it were looking at text. There is no time data stored in the
field.
> Query:
> select startdate
> from auditlog
> where startdate between '6/18/2004' and '6/20/2004'
> I get results like this, with dates outside the specified range:
> 6/18/2004
> 6/18/2004
> 6/2/2004
> 6/19/2003
> 6/19/2003
> 6/2/2004
> 6/2/2004
> 6/2/2004
> Any suggestions?
|||Are you using SQL Server 7? I don't think SQL Server 2000 will do this,
but in any case, try
where startdate between cast('20040618' as datetime) and cast('20040620'
as datetime)
Steve Kass
Drew University
Yayahim wrote:

>Newbie question. When I query a field (datetime datatype) the results appear as if it were looking at text. There is no time data stored in the field.
>Query:
>select startdate
>from auditlog
>where startdate between '6/18/2004' and '6/20/2004'
>I get results like this, with dates outside the specified range:
>6/18/2004
>6/18/2004
>6/2/2004
>6/19/2003
>6/19/2003
>6/2/2004
>6/2/2004
>6/2/2004
>Any suggestions?
>
|||Are you using SQL Server 7? I don't think SQL Server 2000 will do this,
but in any case, try
where startdate between cast('20040618' as datetime) and cast('20040620'
as datetime)
Steve Kass
Drew University
Yayahim wrote:

>Newbie question. When I query a field (datetime datatype) the results appear as if it were looking at text. There is no time data stored in the field.
>Query:
>select startdate
>from auditlog
>where startdate between '6/18/2004' and '6/20/2004'
>I get results like this, with dates outside the specified range:
>6/18/2004
>6/18/2004
>6/2/2004
>6/19/2003
>6/19/2003
>6/2/2004
>6/2/2004
>6/2/2004
>Any suggestions?
>

Tuesday, March 27, 2012

date query when date field is char and mmyy format

Hi All,
I have a field which stored date as char datatype in mmyy format.
Acutally, I have inherited this and there is no way I can change the
design of the database.
Now, I needed to run a qeury between two months (from December 2005 to
January 2006).
My query was:
select * from table1 where claim_date between '1205' and 0106'
This did not give me any records whereas, I know there are records for
the month of December (claim_date : 1205)
I understand that 0106 is smaller than 1205 and that is the main reason
that I am not getting any records. Because if I change it to
select * from table1 where claim_date between '0106' and '1205', then
it gives me the results I want. I feel this is not right.
My question is how can I handle this problem in the query, so that I
can really use the date range as claim_date between '1205' and '0106'.
CREATE TABLE [dbo].[table1] (
[Claim_Number] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[Claim_Date] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[Prime_ABC] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
Insert table1
select 'D124', '1205', 'A'
union all
select 'D243', '0106', 'A'
Thanks a million in advance.
Best regards,
MamunWithout changing the table structure and storing the right data in the right
format, don't expect this to have sizzling performance.
SELECT * FROM
(
SELECT
*,
claim_date_calc = '20'+RIGHT(claim_date,2)+LEFT(claim_date
,2)+'01'
FROM
table1
WHERE
ISDATE('20'+RIGHT(claim_date,2)+LEFT(cla
im_date,2)+'01') = 1
)
x
WHERE CONVERT(SMALLDATETIME, claim_date_calc) BETWEEN '20051201' AND
'20060101';
"microsoft.public.dotnet.languages.vb" <mamun_ah@.hotmail.com> wrote in
message news:1139340637.499729.6330@.f14g2000cwb.googlegroups.com...
> Hi All,
> I have a field which stored date as char datatype in mmyy format.
> Acutally, I have inherited this and there is no way I can change the
> design of the database.
> Now, I needed to run a qeury between two months (from December 2005 to
> January 2006).
> My query was:
> select * from table1 where claim_date between '1205' and 0106'
> This did not give me any records whereas, I know there are records for
> the month of December (claim_date : 1205)
> I understand that 0106 is smaller than 1205 and that is the main reason
> that I am not getting any records. Because if I change it to
> select * from table1 where claim_date between '0106' and '1205', then
> it gives me the results I want. I feel this is not right.
> My question is how can I handle this problem in the query, so that I
> can really use the date range as claim_date between '1205' and '0106'.
> CREATE TABLE [dbo].[table1] (
> [Claim_Number] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [Claim_Date] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ,
> [Prime_ABC] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ) ON [PRIMARY]
> Insert table1
> select 'D124', '1205', 'A'
> union all
> select 'D243', '0106', 'A'
> Thanks a million in advance.
> Best regards,
> Mamun
>|||where substring(Claim_Date,3,2)+substring(Clai
m_Date,1,2)
between '0512' and '0601'|||Both solutions worked great. I was not looking at the performance but
to get the results.
Thanks a ton to both of you.
best regards,
mamun|||Another approach would be to convert the values for claim_date into honest
datetimes, and compare to other datetime values
SELECT Claim_Number, Claim_Date, Prime_ABC
FROM table1
WHERE
CONVERT(datetime, RIGHT(Claim_Date,2) + LEFT(Claim_Date,2) + '01', 12)
BETWEEN '20051201' AND '20060101'
With all respect to Alexander, using BETWEEN with character datatypes gives
me the heebee jeebees.
"microsoft.public.dotnet.languages.vb" wrote:

> Hi All,
> I have a field which stored date as char datatype in mmyy format.
> Acutally, I have inherited this and there is no way I can change the
> design of the database.
> Now, I needed to run a qeury between two months (from December 2005 to
> January 2006).
> My query was:
> select * from table1 where claim_date between '1205' and 0106'
> This did not give me any records whereas, I know there are records for
> the month of December (claim_date : 1205)
> I understand that 0106 is smaller than 1205 and that is the main reason
> that I am not getting any records. Because if I change it to
> select * from table1 where claim_date between '0106' and '1205', then
> it gives me the results I want. I feel this is not right.
> My question is how can I handle this problem in the query, so that I
> can really use the date range as claim_date between '1205' and '0106'.
> CREATE TABLE [dbo].[table1] (
> [Claim_Number] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [Claim_Date] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ,
> [Prime_ABC] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ) ON [PRIMARY]
> Insert table1
> select 'D124', '1205', 'A'
> union all
> select 'D243', '0106', 'A'
> Thanks a million in advance.
> Best regards,
> Mamun
>

Date Problem!

A column in a SQL Server7 DB table has the datatype smalldatetime. All
the records in this column are stored in 'm/d/yyyy' format like
6/15/2005 (15th June 2005), 8/21/2005 (21st August 2005) etc....
Assume that two of the records stores today's date i.e. 9/20/2005 (20th
September 2005). When I execute the following 2 queries in QA
SELECT * FROM tblRecords WHERE Date1='9/20/2005'
SELECT * FROM tblRecords WHERE Date1='20050920'
both the queries retrieve the two records as expected but when I issue
the following query
SELECT * FROM tblRecords WHERE Date1=GETDATE()
no records get retrieved! Why so?
Thanks,
ArpanBecause GETDATE() includes time information. Did you run SELECT GETDATE()
and see that it does not return exactly 20050920?
If you want all rows from today, the most efficient way is probably:
WHERE Date1 >= 0 + DATEDIFF(DAY, 0, GETDATE())
AND Date1 < 1 + DATEDIFF(DAY, 0, GETDATE())
or
DECLARE @.today SMALLDATETIME
SET @.today = 0 + DATEDIFF(DAY, 0, GETDATE())
SELECT
...
WHERE Date1 >= @.today
AND Date1 < (@.today + 1)
(In addition to using an index on date1, this will also allow you to ignore
any time values that might slip into the values in date1.)
"Arpan" <arpan_de@.hotmail.com> wrote in message
news:1127189510.583307.210400@.f14g2000cwb.googlegroups.com...
>A column in a SQL Server7 DB table has the datatype smalldatetime. All
> the records in this column are stored in 'm/d/yyyy' format like
> 6/15/2005 (15th June 2005), 8/21/2005 (21st August 2005) etc....
> Assume that two of the records stores today's date i.e. 9/20/2005 (20th
> September 2005). When I execute the following 2 queries in QA
> SELECT * FROM tblRecords WHERE Date1='9/20/2005'
> SELECT * FROM tblRecords WHERE Date1='20050920'
> both the queries retrieve the two records as expected but when I issue
> the following query
> SELECT * FROM tblRecords WHERE Date1=GETDATE()
> no records get retrieved! Why so?
> Thanks,
> Arpan
>|||Thanks, Aaron, for your help. Good suggestion :-)
Thanks once again,
Regards,
Arpan

Date problem in SQL Server

Good day!

I am having some problem with the date-time datatype. I want to store only date in my table in this format - DD-MM-YYYY - and I do not need time. This must be compatible with the format of Visual Basic.

How to achieve this?

SQL Server datetime datatype stores both DATE and TIME. [datetime] has a presicion to milliseconds, and [shortdatetime] has a precision to minutes.

If you do NOT provide the TIME component, it will default to midnight (or 12:00:00 AM). It is very common for applications to ignore the time component. If you do not need the TIME component, use a shortdatetime datatype.

Refer to Books Online about [datetime] and [shortdatetime] datatypes.

|||

what is the problem in storing date and time. is it because of Storage size or is it because u wnat to show only date to the user. If you want to show only date.... this formating can be done at the frontend. Don't change the datatype... you store the data as datetime only and do the formating at the frontend. And also check CONVERT/CAST function in BOL.

Madhu

|||

You cannot do this with SQL Server 2005 and previous release. In Katmai (next release), we define several new datetime data types and you will be able to do it.

Or you can use the techniques mentioned by the previous posts.

Thanks.

|||

Hi Madhu

My problem is actually in queiry. When I use the type date/time and I assign a default value to GETDATE() in sql server, it stores both date and time. But when I run sproc with where clause like - WHERE Date = @.Date - where the time is not given, I can't find the date!

How to solve it? I am using VB to run the sproc.

Regards

Kapalic

|||

in this case you may need to use CONVERT Function of SQL Server. Suppose u r passing date mm/dd/yyyy then

Where convert(varchar(10),Date,101)='04-10-2007'

Madhu

|||

That is because when you are using @.Date without a time portion, it defaults to midnight (12:00:00.000), and you don't have any rows with that exact time.

Try the following WHERE clause:

Code Snippet

WHERE ( [Date] >= @.Date

AND [Date] < ( dateadd( day, 1, @.Date )

)

This assumes that @.Date is in the form of 'mm/dd/yyyy', and that you want all values from midnight until the last possible moment before the next midnight (Or [ >= 12:00:00.000 ] AND [ < 11:59:59.997 ]

(You don't really want to follow Madhu's suggestion of using convert(varchar(10), Date, 101) BECAUSE it would invalidate the use of an index and could be very inefficient.)

|||

Hi Arnie,

Thnk you very much for your reply. The problem is, I want to use the date format as DD/MM/YYYY. This I forgot to mention. And I want to link the sproc with a DateTimePicker control in Visual Basic.

Please resque...

Regards

Kapalic

|||

Read about CONVERT Function in BOL ... what u need to do is ... convert the date in sql to which ever format u required. the below statement will do that

if the supplied date is 04/10/2007 dd/mm/yyyy run the below mentioned statement and see teh result

Where convert(varchar(10),Date,105)='04-10-2007'

Madhu

|||

Using a VB date format of 'DD/MM/YYYY', and using a DateTimePicker is NOT a problem as long as you are using a Date datatype in VB, and the procedure call is using a SQL Datetime datatype.

If your VB date variable it used when you make the SQL Stored procedure call, the WHERE clause example above will work just fine.

Sunday, March 11, 2012

date formatting

hi
below is my query AdditionalField3 is varchar datatype .and in this field my date is
storing like this 12/05/06..now i wanted to change format of this field AdditionalField3
like 2006-12-5

how to do this?
SELECT *
FROM Tbl_CMS_UploadDetails
WHERE AdditionalField3 = '2006-12-5'

You can use the following statement..

SELECT *
FROM Tbl_CMS_UploadDetails
WHERE Cast(AdditionalField3 as datetime) = Cast('2006-12-5' as datetime)

|||

i m getting below error

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

|||Ok.. What is your date format 12/05/06|||yes and that field is varchar|||

Ok..

If you use dd/mm/yy

SELECT *
FROM Tbl_CMS_UploadDetails
WHERE Convert(datetime, AdditionalField3, 103) = Cast('2006-12-5' as datetime)

if you use mm/dd/yy

SELECT *
FROM Tbl_CMS_UploadDetails
WHERE Convert(datetime, AdditionalField3, 101) = Cast('2006-12-5' as datetime)

|||

i tried both queries getting error

Server: Msg 241, Level 16, State 1, Line 1
Syntax error converting datetime from character string.

|||Is your AdditionalField3 has some invalid data?|||

yes this field also containing bank name ...we r using this field as common field so here for some cases we storing name and for other date thats why this field is varchar type ..for this purpose we r using format_id..

like this

for 83 we r storing date ..

SELECT *
FROM Tbl_CMS_UploadDetails
WHERE Convert(datetime, AdditionalField3, 101) = Cast('2006-12-5' as datetime) and format_id=83

|||

use the following query..

Code Snippet

--If you use dd/mm/yy

SET DATEFORMAT dmy

SELECT *

FROM Tbl_CMS_UploadDetails

WHERE Case When Isdate(AdditionalField3)=1 Then Convert(datetime, AdditionalField3, 103) Else NULL END = Cast('2006-12-5' as datetime)

--if you use mm/dd/yy

SET DATEFORMAT mdy

SELECT *

FROM Tbl_CMS_UploadDetails

WHERE Case When Isdate(AdditionalField3)=1 Then Convert(datetime, AdditionalField3, 101) Else NULL END = Cast('2006-12-5' as datetime)

|||

getting error

Server: Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'Then'.

|||Ooops Fixed , try now|||

again error

Server: Msg 241, Level 16, State 1, Line 3
Syntax error converting datetime from character string.

|||thanx a lot....gotted now..

Friday, February 24, 2012

Date Datatype Oddity - Ref "Like"

While trying to resolve a very endepth data issue against
healthcare claims data (8 years = rows), have discovered
an oddity.
When I run a "LIKE '% %'" (two spaces) against a datetime
or smalldatetime data type field, all dates with days less
than 10 return.
Could someone explain, this resultset, since datetime is
actually stored has an equivalant to two 4 bit integers
(one for date and one for time)?
This occurs against SQL 2000 (6.5 compatability DB, 7.0
compatability DB, 2000 DB) and also against SQL 2005 Beta
2 (2005 DB).
John Whitehead
MSSQLDBA (9years)Sorry (8 years = rows) should read (8 years = 31 million
rows)
JohnW
>--Original Message--
>While trying to resolve a very endepth data issue against
>healthcare claims data (8 years = rows), have discovered
>an oddity.
>When I run a "LIKE '% %'" (two spaces) against a
datetime
>or smalldatetime data type field, all dates with days
less
>than 10 return.
>Could someone explain, this resultset, since datetime is
>actually stored has an equivalant to two 4 bit integers
>(one for date and one for time)?
>This occurs against SQL 2000 (6.5 compatability DB, 7.0
>compatability DB, 2000 DB) and also against SQL 2005 Beta
>2 (2005 DB).
>
>John Whitehead
>MSSQLDBA (9years)
>.
>|||John,
I think this will explain it:
select convert(varchar, convert(datetime, '20040930'))
--Sep 30 2004 12:00AM
select convert(varchar, convert(datetime, '20041001'))
--Oct 1 2004 12:00AM
Notice that the 1 for Oct 1 is left-padded with an extra space.
"John Whitehead" <jwhitehead@.thearksolutions.com> wrote in message
news:24d801c4a7df$bc939270$a401280a@.phx.gbl...
> When I run a "LIKE '% %'" (two spaces) against a datetime
> or smalldatetime data type field, all dates with days less
> than 10 return.
> Could someone explain, this resultset, since datetime is
> actually stored has an equivalant to two 4 bit integers
> (one for date and one for time)?|||That's a very risky way to query datetime types. The default format varies
with your regional settings. Why are you querying like this? What are your
search criteria?
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"John Whitehead" <jwhitehead@.thearksolutions.com> wrote in message
news:24d801c4a7df$bc939270$a401280a@.phx.gbl...
While trying to resolve a very endepth data issue against
healthcare claims data (8 years = rows), have discovered
an oddity.
When I run a "LIKE '% %'" (two spaces) against a datetime
or smalldatetime data type field, all dates with days less
than 10 return.
Could someone explain, this resultset, since datetime is
actually stored has an equivalant to two 4 bit integers
(one for date and one for time)?
This occurs against SQL 2000 (6.5 compatability DB, 7.0
compatability DB, 2000 DB) and also against SQL 2005 Beta
2 (2005 DB).
John Whitehead
MSSQLDBA (9years)|||You should never be using LIKE against a [SMALL]DATETIME column. However,
it is easy to explain why you are getting this error. Run this:
PRINT GETDATE()
It doesn't print the actual numeric values that are stored, it converts it
to a string first. The appearance of this string will vary depending on a
few things (locale, date format, language, etc). But the same conversion
happens behind the scenes when you apply a string function such as LIKE.
BTW, I can't reproduce your "only days with less than 10 days" scenario. I
am betting that you are converting to an explicit format, or you haven't
disclosed your complete where clause. Can you provide a full repro script?
--
http://www.aspfaq.com/
(Reverse address to reply.)
"John Whitehead" <jwhitehead@.thearksolutions.com> wrote in message
news:24d801c4a7df$bc939270$a401280a@.phx.gbl...
> While trying to resolve a very endepth data issue against
> healthcare claims data (8 years = rows), have discovered
> an oddity.
> When I run a "LIKE '% %'" (two spaces) against a datetime
> or smalldatetime data type field, all dates with days less
> than 10 return.
> Could someone explain, this resultset, since datetime is
> actually stored has an equivalant to two 4 bit integers
> (one for date and one for time)?
> This occurs against SQL 2000 (6.5 compatability DB, 7.0
> compatability DB, 2000 DB) and also against SQL 2005 Beta
> 2 (2005 DB).
>
> John Whitehead
> MSSQLDBA (9years)|||Adam,
I would agree, however was not converting to a varchar
before the "like".
John
>--Original Message--
>John,
>I think this will explain it:
>select convert(varchar, convert(datetime, '20040930'))
>--Sep 30 2004 12:00AM
>
>select convert(varchar, convert(datetime, '20041001'))
>--Oct 1 2004 12:00AM
>
>Notice that the 1 for Oct 1 is left-padded with an extra
space.
>
>"John Whitehead" <jwhitehead@.thearksolutions.com> wrote
in message
>news:24d801c4a7df$bc939270$a401280a@.phx.gbl...
>> When I run a "LIKE '% %'" (two spaces) against a
datetime
>> or smalldatetime data type field, all dates with days
less
>> than 10 return.
>> Could someone explain, this resultset, since datetime is
>> actually stored has an equivalant to two 4 bit integers
>> (one for date and one for time)?
>
>.
>|||Tom,
You are absolutely right, and this is in no way a normal
resolution discovery path. We had been working on this
issue for a while, and this path was taken due to the fact
that we import this data from a "~" delimited, fixed width
file (that we have no control over) and was looking at the
possibility of an special character somehow making it into
the database. Have found some very endepth information
about how the sqlserver engine handles data and was just
wondering if this oddity was caused by that process.
This is not an issue - just an oddity
Thanks
John
>--Original Message--
>That's a very risky way to query datetime types. The
default format varies
>with your regional settings. Why are you querying like
this? What are your
>search criteria?
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com
>
>"John Whitehead" <jwhitehead@.thearksolutions.com> wrote
in message
>news:24d801c4a7df$bc939270$a401280a@.phx.gbl...
>While trying to resolve a very endepth data issue against
>healthcare claims data (8 years = rows), have discovered
>an oddity.
>When I run a "LIKE '% %'" (two spaces) against a datetime
>or smalldatetime data type field, all dates with days less
>than 10 return.
>Could someone explain, this resultset, since datetime is
>actually stored has an equivalant to two 4 bit integers
>(one for date and one for time)?
>This occurs against SQL 2000 (6.5 compatability DB, 7.0
>compatability DB, 2000 DB) and also against SQL 2005 Beta
>2 (2005 DB).
>
>John Whitehead
>MSSQLDBA (9years)
>.
>|||The conversion happens implictly because LIKE expects only string arguments.
--
David Portas
SQL Server MVP
--|||> I would agree, however was not converting to a varchar
> before the "like".
You don't have to do it manually. LIKE is only relevant to string
arguments, and since [SMALL]DATETIME can be implicitly converted to VARCHAR,
that is what happens for you.|||You may want to consider DTS to look for the bad data as it imports it.
Failing that, you could import the field from the flat file into a
char/varchar column and then search for anomalies via SQL against the
column. We'd have to know a bit more of what you are seeking.
Tom
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"John Whitehead" <jwhitehead@.thearksolutions.com> wrote in message
news:254901c4a7e6$730376a0$a401280a@.phx.gbl...
Tom,
You are absolutely right, and this is in no way a normal
resolution discovery path. We had been working on this
issue for a while, and this path was taken due to the fact
that we import this data from a "~" delimited, fixed width
file (that we have no control over) and was looking at the
possibility of an special character somehow making it into
the database. Have found some very endepth information
about how the sqlserver engine handles data and was just
wondering if this oddity was caused by that process.
This is not an issue - just an oddity
Thanks
John
>--Original Message--
>That's a very risky way to query datetime types. The
default format varies
>with your regional settings. Why are you querying like
this? What are your
>search criteria?
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com
>
>"John Whitehead" <jwhitehead@.thearksolutions.com> wrote
in message
>news:24d801c4a7df$bc939270$a401280a@.phx.gbl...
>While trying to resolve a very endepth data issue against
>healthcare claims data (8 years = rows), have discovered
>an oddity.
>When I run a "LIKE '% %'" (two spaces) against a datetime
>or smalldatetime data type field, all dates with days less
>than 10 return.
>Could someone explain, this resultset, since datetime is
>actually stored has an equivalant to two 4 bit integers
>(one for date and one for time)?
>This occurs against SQL 2000 (6.5 compatability DB, 7.0
>compatability DB, 2000 DB) and also against SQL 2005 Beta
>2 (2005 DB).
>
>John Whitehead
>MSSQLDBA (9years)
>.
>|||FINAL POST!!
This is the reason that I don't post to these boards!
THIS IS NOT AN ISSUE TO BE SOVLED - JUST AN ODDITY TO BE
EXPLAINED - "Confidentiality agreements prevent from
further explanations and process logic discussion"
Tom - I guarentee you that this is the first time in over
20 years of dealing with databases that I have run a LIKE
search against a date datatype. Some RDBMS's don't
allow "Like" against a date datatype.
Thanks Aaron, Logical, but is it a FACT, that the engine
would convert a numeric based data type to a character
base value in order to compare for the non-numeric "like"?
John
>--Original Message--
>You may want to consider DTS to look for the bad data as
it imports it.
>Failing that, you could import the field from the flat
file into a
>char/varchar column and then search for anomalies via SQL
against the
>column. We'd have to know a bit more of what you are
seeking.
> Tom
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com
>
>"John Whitehead" <jwhitehead@.thearksolutions.com> wrote
in message
>news:254901c4a7e6$730376a0$a401280a@.phx.gbl...
>Tom,
>You are absolutely right, and this is in no way a normal
>resolution discovery path. We had been working on this
>issue for a while, and this path was taken due to the fact
>that we import this data from a "~" delimited, fixed width
>file (that we have no control over) and was looking at the
>possibility of an special character somehow making it into
>the database. Have found some very endepth information
>about how the sqlserver engine handles data and was just
>wondering if this oddity was caused by that process.
>This is not an issue - just an oddity
>Thanks
>John
>>--Original Message--
>>That's a very risky way to query datetime types. The
>default format varies
>>with your regional settings. Why are you querying like
>this? What are your
>>search criteria?
>>--
>>Tom
>>----
-
>--
>>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>>SQL Server MVP
>>Columnist, SQL Server Professional
>>Toronto, ON Canada
>>www.pinnaclepublishing.com
>>
>>"John Whitehead" <jwhitehead@.thearksolutions.com> wrote
>in message
>>news:24d801c4a7df$bc939270$a401280a@.phx.gbl...
>>While trying to resolve a very endepth data issue against
>>healthcare claims data (8 years = rows), have discovered
>>an oddity.
>>When I run a "LIKE '% %'" (two spaces) against a
datetime
>>or smalldatetime data type field, all dates with days
less
>>than 10 return.
>>Could someone explain, this resultset, since datetime is
>>actually stored has an equivalant to two 4 bit integers
>>(one for date and one for time)?
>>This occurs against SQL 2000 (6.5 compatability DB, 7.0
>>compatability DB, 2000 DB) and also against SQL 2005 Beta
>>2 (2005 DB).
>>
>>John Whitehead
>>MSSQLDBA (9years)
>>.
>.
>|||"John Whitehead" <jwhitehead@.thearksolutions.com> wrote in message
news:25f401c4a7ee$c91e7cd0$a401280a@.phx.gbl...
> This is the reason that I don't post to these boards!
It's free support... Sometimes you get what you pay for, other times
you get quite a bit more :-)
> Thanks Aaron, Logical, but is it a FACT, that the engine
> would convert a numeric based data type to a character
> base value in order to compare for the non-numeric "like"?
Yes, that's the only way the LIKE operator can work.|||> This is the reason that I don't post to these boards!
> THIS IS NOT AN ISSUE TO BE SOVLED - JUST AN ODDITY TO BE
> EXPLAINED
You got that, and more. Don't look a gift horse in the mouth.
> Thanks Aaron, Logical, but is it a FACT, that the engine
> would convert a numeric based data type to a character
> base value in order to compare for the non-numeric "like"?
Nah, I made it all up. *shrug*|||> BTW, I can't reproduce your "only days with less than 10 days" scenario.
> I
> am betting that you are converting to an explicit format, or you haven't
> disclosed your complete where clause. Can you provide a full repro
> script?
Aaron :
I'm not related to the original poster, I was just lurking, but here it is :
Create Table TableX
(ColX SmallDateTime Not Null )
Insert Into TableX Values ('20041101')
Insert Into TableX Values ('20041102')
Insert Into TableX Values ('20041109')
Insert Into TableX Values ('20041110')
Insert Into TableX Values ('20041111')
Insert Into TableX Values ('20041001')
Insert Into TableX Values ('20041002')
Insert Into TableX Values ('20041009')
Insert Into TableX Values ('20041010')
Insert Into TableX Values ('20041011')
Select * From TableX Where ColX Like '% %'
-- or Select * From TableX Where Cast(ColX as VarChar(20)) Like '% %'
Output in SQL Analyzer :
2004-11-01 00:00:00
2004-11-02 00:00:00
2004-11-09 00:00:00
2004-10-01 00:00:00
2004-10-02 00:00:00
2004-10-09 00:00:00
I have yyyy-mm-dd in my regional settings.
Regards,|||Sorry, I missed the two spaces in the original post, I thought there was
only one.
Regardless, the rest of the thread shows why this is a bad idea in the first
place.
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Craig Kenisston" <craigkenisston@.hotmail.com> wrote in message
news:#znUhsAqEHA.1164@.TK2MSFTNGP10.phx.gbl...
> > BTW, I can't reproduce your "only days with less than 10 days" scenario.
> > I
> > am betting that you are converting to an explicit format, or you haven't
> > disclosed your complete where clause. Can you provide a full repro
> > script?
> Aaron :
> I'm not related to the original poster, I was just lurking, but here it is
:
> Create Table TableX
> (ColX SmallDateTime Not Null )
> Insert Into TableX Values ('20041101')
> Insert Into TableX Values ('20041102')
> Insert Into TableX Values ('20041109')
> Insert Into TableX Values ('20041110')
> Insert Into TableX Values ('20041111')
> Insert Into TableX Values ('20041001')
> Insert Into TableX Values ('20041002')
> Insert Into TableX Values ('20041009')
> Insert Into TableX Values ('20041010')
> Insert Into TableX Values ('20041011')
> Select * From TableX Where ColX Like '% %'
> -- or Select * From TableX Where Cast(ColX as VarChar(20)) Like '% %'
> Output in SQL Analyzer :
> 2004-11-01 00:00:00
> 2004-11-02 00:00:00
> 2004-11-09 00:00:00
> 2004-10-01 00:00:00
> 2004-10-02 00:00:00
> 2004-10-09 00:00:00
> I have yyyy-mm-dd in my regional settings.
> Regards,
>

Date datatype

I need to store older dates than Jan 1 1753. What date type should I use? I
need to store for example 26 August 1537.
With thanks
Hi
Probably you will need to store it as VARCHAR(n) column.
"EdwardH" <EdwardH@.discussions.microsoft.com> wrote in message
news:55BDB48D-B01A-4075-815D-C139E45E9CF8@.microsoft.com...
> I need to store older dates than Jan 1 1753. What date type should I use?
I
> need to store for example 26 August 1537.
> With thanks
|||Hi Edward,
You can go with Varchar data type.
Thanks
Hari
SQL Server MVP
"EdwardH" <EdwardH@.discussions.microsoft.com> wrote in message
news:55BDB48D-B01A-4075-815D-C139E45E9CF8@.microsoft.com...
>I need to store older dates than Jan 1 1753. What date type should I use? I
> need to store for example 26 August 1537.
> With thanks
|||That means that the column would have not "date" abilities.
"Uri Dimant" wrote:

> Hi
> Probably you will need to store it as VARCHAR(n) column.
> "EdwardH" <EdwardH@.discussions.microsoft.com> wrote in message
> news:55BDB48D-B01A-4075-815D-C139E45E9CF8@.microsoft.com...
> I
>
>
|||What "date abilities" do you want? For ancient dates it's hard to do
any of the things we take for granted with DATETIME (validation,
comparison, sorting, date arithmetic). I expect you know that our
Gregorian Calendar didn't exist in 1537 so what calendar logic would
you expect to apply to such a date? You have to design them yourself
I'm afraid because SQL Server has no way of knowing what calendar was
in use in your particular part of the world on any given date.
David Portas
SQL Server MVP
|||Hi
Look at :
http://codebetter.com/blogs/raymond.../01/34519.aspx
http://groups.google.ch/group/micros... 7fa92fdeea5a
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"EdwardH" wrote:

> I need to store older dates than Jan 1 1753. What date type should I use? I
> need to store for example 26 August 1537.
> With thanks

Date datatype

I need to store older dates than Jan 1 1753. What date type should I use? I
need to store for example 26 August 1537.
With thanksHi
Probably you will need to store it as VARCHAR(n) column.
"EdwardH" <EdwardH@.discussions.microsoft.com> wrote in message
news:55BDB48D-B01A-4075-815D-C139E45E9CF8@.microsoft.com...
> I need to store older dates than Jan 1 1753. What date type should I use?
I
> need to store for example 26 August 1537.
> With thanks|||Hi Edward,
You can go with Varchar data type.
Thanks
Hari
SQL Server MVP
"EdwardH" <EdwardH@.discussions.microsoft.com> wrote in message
news:55BDB48D-B01A-4075-815D-C139E45E9CF8@.microsoft.com...
>I need to store older dates than Jan 1 1753. What date type should I use? I
> need to store for example 26 August 1537.
> With thanks|||That means that the column would have not "date" abilities.
"Uri Dimant" wrote:
> Hi
> Probably you will need to store it as VARCHAR(n) column.
> "EdwardH" <EdwardH@.discussions.microsoft.com> wrote in message
> news:55BDB48D-B01A-4075-815D-C139E45E9CF8@.microsoft.com...
> > I need to store older dates than Jan 1 1753. What date type should I use?
> I
> > need to store for example 26 August 1537.
> > With thanks
>
>|||What "date abilities" do you want? For ancient dates it's hard to do
any of the things we take for granted with DATETIME (validation,
comparison, sorting, date arithmetic). I expect you know that our
Gregorian Calendar didn't exist in 1537 so what calendar logic would
you expect to apply to such a date? You have to design them yourself
I'm afraid because SQL Server has no way of knowing what calendar was
in use in your particular part of the world on any given date.
--
David Portas
SQL Server MVP
--|||Hi
Look at :
http://codebetter.com/blogs/raymond.lewallen/archive/2004/12/01/34519.aspx
http://groups.google.ch/group/microsoft.public.sqlserver.server/browse_thread/thread/9b6613e416ae827c/55b87fa92fdeea5a?q=sql+date+1753&rnum=2&hl=en#55b87fa92fdeea5a
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"EdwardH" wrote:
> I need to store older dates than Jan 1 1753. What date type should I use? I
> need to store for example 26 August 1537.
> With thanks

Date Datatype

I am using Visual Studio .NET 2003 with SQL Server 2000. I am trying to
insert the date and time into a SQL database by using hour(now). I am having
a hard time trying to figure out which datatype to use in SQL to store this
value. I have tried using datetime, char, nchar, text and nothing seems to
work. Anyone have any ideas? Thanks!
Regards, :)
Christopher BowenDo you want to store the time in terms of hours only?
Madhivanan|||Christopher,
Is the datatype of the column a DateTime field and make sure the variable
storing this value (in .NET i assume) is a System.DateTime - or use
System.DateTime.Now. Using the SqlCommand Object add a parameter of type
datetime and assign this .net variable eg:
public void InsertDateTime()
{
DB Access setup....
...
using (SqlCommand command = new SqlCommand(etc...));
{
command.Connection = someConnectionPreviouslySetup;
command.CommandText = "INSERT INTO TableToInsertInto
(DateTimeField) VALUES (@.DateTimeField)";
command.CommentType = CommandType.Text;
//add the parameter to the parameters collection
command.Parameters.Add("@.DateTimeField", SqlDbType.DateTime);
//set the parameter to the required value
command.Parameters["@.DateTimeField"].Value =
System.DateTime.Now;
command.ExecuteNonQuery();
}
}
I am doing a mini sample in c# (this will NOT compile).
Regards,
Adrian.
<madhivanan2001@.gmail.com> wrote in message
news:1109141955.543915.123420@.g14g2000cwa.googlegroups.com...
> Do you want to store the time in terms of hours only?
> Madhivanan
>|||Hi
Format your value as 'YYYYMMDD' and try to insert
"Christopher Bowen" <c_bowen@.earthlink.net> wrote in message
news:%23OdiaKXGFHA.1740@.TK2MSFTNGP09.phx.gbl...
> I am using Visual Studio .NET 2003 with SQL Server 2000. I am trying to
> insert the date and time into a SQL database by using hour(now). I am
having
> a hard time trying to figure out which datatype to use in SQL to store
this
> value. I have tried using datetime, char, nchar, text and nothing seems to
> work. Anyone have any ideas? Thanks!
> Regards, :)
> Christopher Bowen
>|||I will give your examples a try and I will let you know how it turns out.
Thanks for the help!
"Christopher Bowen" <c_bowen@.earthlink.net> wrote in message
news:#OdiaKXGFHA.1740@.TK2MSFTNGP09.phx.gbl...
> I am using Visual Studio .NET 2003 with SQL Server 2000. I am trying to
> insert the date and time into a SQL database by using hour(now). I am
having
> a hard time trying to figure out which datatype to use in SQL to store
this
> value. I have tried using datetime, char, nchar, text and nothing seems to
> work. Anyone have any ideas? Thanks!
> Regards, :)
> Christopher Bowen
>|||It worked! I went into my table and changed the datatype to "datetime" and
used the code system.datetime.now. Thanks to all of you for all of your
help!
Kind Regards, :)
Christopher Bowen
"Christopher Bowen" <c_bowen@.earthlink.net> wrote in message
news:%23OdiaKXGFHA.1740@.TK2MSFTNGP09.phx.gbl...
>I am using Visual Studio .NET 2003 with SQL Server 2000. I am trying to
>insert the date and time into a SQL database by using hour(now). I am
>having a hard time trying to figure out which datatype to use in SQL to
>store this value. I have tried using datetime, char, nchar, text and
>nothing seems to work. Anyone have any ideas? Thanks!
> Regards, :)
> Christopher Bowen
>

Date datatype

I need to store older dates than Jan 1 1753. What date type should I use? I
need to store for example 26 August 1537.
With thanksHi
Probably you will need to store it as VARCHAR(n) column.
"EdwardH" <EdwardH@.discussions.microsoft.com> wrote in message
news:55BDB48D-B01A-4075-815D-C139E45E9CF8@.microsoft.com...
> I need to store older dates than Jan 1 1753. What date type should I use?
I
> need to store for example 26 August 1537.
> With thanks|||Hi Edward,
You can go with Varchar data type.
Thanks
Hari
SQL Server MVP
"EdwardH" <EdwardH@.discussions.microsoft.com> wrote in message
news:55BDB48D-B01A-4075-815D-C139E45E9CF8@.microsoft.com...
>I need to store older dates than Jan 1 1753. What date type should I use? I
> need to store for example 26 August 1537.
> With thanks|||That means that the column would have not "date" abilities.
"Uri Dimant" wrote:

> Hi
> Probably you will need to store it as VARCHAR(n) column.
> "EdwardH" <EdwardH@.discussions.microsoft.com> wrote in message
> news:55BDB48D-B01A-4075-815D-C139E45E9CF8@.microsoft.com...
> I
>
>|||What "date abilities" do you want? For ancient dates it's hard to do
any of the things we take for granted with DATETIME (validation,
comparison, sorting, date arithmetic). I expect you know that our
Gregorian Calendar didn't exist in 1537 so what calendar logic would
you expect to apply to such a date? You have to design them yourself
I'm afraid because SQL Server has no way of knowing what calendar was
in use in your particular part of the world on any given date.
David Portas
SQL Server MVP
--|||Hi
Look at :
http://codebetter.com/blogs/raymond...2/01/34519.aspx
http://groups.google.ch/group/micro...fdeea5a

--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"EdwardH" wrote:

> I need to store older dates than Jan 1 1753. What date type should I use?
I
> need to store for example 26 August 1537.
> With thanks

Sunday, February 19, 2012

Date created defualt value

in my database i have a "datecreated" feild, its datatype is "datetime", is it possible to set the defualt value as the time now? if so what do I put in the defualt value property? thanks a million si!

Under design view for the Table, you can set he default value of "Getdate()" (without the quotes). You dont need to do anything from yout T-SQL or application. The value is automatically inserted by SQL Server.

|||

thanks a million! thats going to save sometime Big Smile si!

Date Conversion throws truncation error

Hi,

I am trying to process a flat file feed. My date is in format of YYYYMMDD. The database column is of datatype "datetime". I have tried using all date related data types on FLAT FILE Connection Manager. I have also tried using Data Conversion Component. No luck so far!!

Any suggestion?

Thanks in advance,
-AnandTry the Derived Column Transformation.

This post doesn't solve exactly the same problem but its very very similar: http://blogs.conchango.com/jamiethomson/archive/2005/07/26/1867.aspx

-Jamie|||

The key point is, yyyymmdd cannot be casted to date type. (This has been bugged I believe, but feel free to log again it as I know I want it!). Using the Derived Column to crack it into format that can then be casted is the only route. I did test what was suppoted and what wasn't, but seem to have lost the packages for now....

I would also check you cracked format works for US/UK locales, as the flip-flop between the two can really mess you up. I like dd mmm yyyy myself, as no languages muck that up, not even VBScript! This may help as a start if you follow that format-

(MONTH(RowDate) == 1 ? "January" : MONTH(RowDate) == 2 ? "February" : MONTH(RowDate) == 3 ? "March" : MONTH(RowDate) == 4 ? "April" : MONTH(RowDate) == 5 ? "May" : MONTH(RowDate) == 6 ? "June" : MONTH(RowDate) == 7 ? "July" : MONTH(RowDate) == 8 ? "August" : MONTH(RowDate) == 9 ? "September" : MONTH(RowDate) == 10 ? "October" : MONTH(RowDate) == 11 ? "November" : MONTH(RowDate) == 12 ? "December" : "InvalidMonth")

|||Hi,

I have partial success with "Data Conversion". What should I do for the column which may contain '' or null?

One more thing: Is dt_dbdate in SSIS compatible with datetime in SQL Server 2005?

-Anand|||Hi,

Will this work in the "Derived Column"?

ISNULL([Maturity Date]) ? : (DT_DBDATE)(SUBSTRING(TRIM([Maturity Date]),5,2) + SUBSTRING(TRIM([Maturity Date]),6,2) + SUBSTRING(TRIM([Maturity Date]),1,4))|||I have resolved the issue using following "Derived Column" statement:

(TRIM([Maturity Date]) == "") ? NULL(DT_WSTR,8) : SUBSTRING(TRIM([Maturity Date]),5,2) + "/" + SUBSTRING(TRIM([Maturity Date]),7,2) + "/" + SUBSTRING(TRIM([Maturity Date]),1,4)

Cheers,
Anand

Big Smile

Date Conversion - Flat File - YYYYMMDD

Hi,

What is the new way to transform flat file dates into SQL datetime datatype.Being average user in SQL 2000 DTS I would simply use "Date Time String Transformation Properties" and transform the date into the format I need, in SSIS I haven't found an elegant way of doing this.

My thoughts are to use “data conversion” utilizing substring expressions…

Thanks the help

Bill

Use a derived column transformation to substring the date field and then concatenate the parts together. Once that's done cast it to a datetime field.

Something like:

(DT_DBTIMESTAMP)(substring([yourDateField],5,2) + "/" + substring([yourDateField],7,2) + "/" + substring([yourDateField],1,4))

|||

bmilstead,

In my case, I declared the metadata for the date columns in the flat file as DB_TIMESTAMP, and then used a derived column to filter invalid dates using an expression for the [Begin Date] Column

ISNULL([Begin Date]) || (DT_I4)DATEPART("yyyy",[Begin Date]) < 1753 || (DT_I4)DATEPART("yyyy",[Begin Date]) > 9999 ? NULL(DT_DBTIMESTAMP) : (DT_DBTIMESTAMP)[Begin Date]

Thanks

Subhash Subramanyam

|||

Subhash Subramanyam wrote:

bmilstead,

In my case, I declared the metadata for the date columns in the flat file as DB_TIMESTAMP, and then used a derived column to filter invalid dates using an expression for the [Begin Date] Column

ISNULL([Begin Date]) || (DT_I4)DATEPART("yyyy",[Begin Date]) < 1753 || (DT_I4)DATEPART("yyyy",[Begin Date]) > 9999 ? NULL(DT_DBTIMESTAMP) : (DT_DBTIMESTAMP)[Begin Date]

Thanks

Subhash Subramanyam

Right, but the format of the dates in the flat file are not DB_TIMESTAMP compatible. (YYYYMMDD)

DATE CONVERSION

How to convert DATE from one format to another.
datatype: datetime
original format: 2006-09-25 10:48:19.000
I need to convert to 2006-09-25
any TSQL function? I want to do this in a single SQL query. The above
date value can be NULL as well. If it is null, It should no do
anything.
Thanks.
Hi,
You could use CONVERT function.
select convert(char(10),getdate(),20)
Thanks
hari
SQL Server MVP
<krallabandi@.gmail.com> wrote in message
news:1158939304.788022.14330@.i42g2000cwa.googlegro ups.com...
> How to convert DATE from one format to another.
> datatype: datetime
> original format: 2006-09-25 10:48:19.000
> I need to convert to 2006-09-25
> any TSQL function? I want to do this in a single SQL query. The above
> date value can be NULL as well. If it is null, It should no do
> anything.
> Thanks.
>

Friday, February 17, 2012

DATE CONVERSION

How to convert DATE from one format to another.
datatype: datetime
original format: 2006-09-25 10:48:19.000
I need to convert to 2006-09-25
any TSQL function? I want to do this in a single SQL query. The above
date value can be NULL as well. If it is null, It should no do
anything.
Thanks.Hi,
You could use CONVERT function.
select convert(char(10),getdate(),20)
Thanks
hari
SQL Server MVP
<krallabandi@.gmail.com> wrote in message
news:1158939304.788022.14330@.i42g2000cwa.googlegroups.com...
> How to convert DATE from one format to another.
> datatype: datetime
> original format: 2006-09-25 10:48:19.000
> I need to convert to 2006-09-25
> any TSQL function? I want to do this in a single SQL query. The above
> date value can be NULL as well. If it is null, It should no do
> anything.
> Thanks.
>

DATE CONVERSION

How to convert DATE from one format to another.
datatype: datetime
original format: 2006-09-25 10:48:19.000
I need to convert to 2006-09-25
any TSQL function? I want to do this in a single SQL query. The above
date value can be NULL as well. If it is null, It should no do
anything.
Thanks.Hi,
You could use CONVERT function.
select convert(char(10),getdate(),20)
Thanks
hari
SQL Server MVP
<krallabandi@.gmail.com> wrote in message
news:1158939304.788022.14330@.i42g2000cwa.googlegroups.com...
> How to convert DATE from one format to another.
> datatype: datetime
> original format: 2006-09-25 10:48:19.000
> I need to convert to 2006-09-25
> any TSQL function? I want to do this in a single SQL query. The above
> date value can be NULL as well. If it is null, It should no do
> anything.
> Thanks.
>

Tuesday, February 14, 2012

Date (not datetime) datatype in 2005?

For some reason, I recall having read that SQL Server 2005 would support
a datatype that represented date, but not time. (This would be useful
for storing things like birthday, where you usually don't care about the
time of day). But I've got SQL Server 2005 installed, and there's no
such datatype to be found.

Is this something that might be released in a Service Pack, or is it
just not going to happen?Larry Bertolini wrote:
> For some reason, I recall having read that SQL Server 2005 would support
> a datatype that represented date, but not time. (This would be useful
> for storing things like birthday, where you usually don't care about the
> time of day). But I've got SQL Server 2005 installed, and there's no
> such datatype to be found.
> Is this something that might be released in a Service Pack, or is it
> just not going to happen?

DATE never made it to RTM. It's not in SP1 either.

Instead try:

CREATE TABLE tbl
(dt SMALLDATETIME NOT NULL CHECK
(DATEADD(DAY,DATEDIFF(DAY,0,dt),0)=dt), ...)

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||Larry Bertolini (bertolini.1@.osu.edu) writes:
> For some reason, I recall having read that SQL Server 2005 would support
> a datatype that represented date, but not time. (This would be useful
> for storing things like birthday, where you usually don't care about the
> time of day). But I've got SQL Server 2005 installed, and there's no
> such datatype to be found.
> Is this something that might be released in a Service Pack, or is it
> just not going to happen?

I definitely hope it's going to happen, but it's not going to happen in
a service pack!

Your recollection is not incorrect. The first beta of SQL 2005 did have a
date data type (and a time data type). However, this was dropped and
that might be just as well. I bet you expect a date type to be compatible
with datetime, work just like datetime with dateadd() and the like.
Instead the date type was implemented in the CLR, and had completely
different syntax and operations, and was not very compatible with
datetime.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97AAEF8EC2BAAYazorman@.127.0.0.1...
> Larry Bertolini (bertolini.1@.osu.edu) writes:
> > For some reason, I recall having read that SQL Server 2005 would support
> > a datatype that represented date, but not time. (This would be useful
> > for storing things like birthday, where you usually don't care about the
> > time of day). But I've got SQL Server 2005 installed, and there's no
> > such datatype to be found.
> > Is this something that might be released in a Service Pack, or is it
> > just not going to happen?
> I definitely hope it's going to happen, but it's not going to happen in
> a service pack!
> Your recollection is not incorrect. The first beta of SQL 2005 did have a
> date data type (and a time data type). However, this was dropped and
> that might be just as well. I bet you expect a date type to be compatible
> with datetime, work just like datetime with dateadd() and the like.
> Instead the date type was implemented in the CLR, and had completely
> different syntax and operations, and was not very compatible with
> datetime.

Argh, and just today I was wondering about this in 2005.

Oh well.

> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx

Thursday, March 29, 2012

date range query not returning anticipated results

Tuesday, March 27, 2012

date query when date field is char and mmyy format

Date Problem!

Date problem in SQL Server

Sunday, March 11, 2012

date formatting

Friday, February 24, 2012

Date Datatype Oddity - Ref "Like"

Date Datatype Oddity - Ref "Like"

Date datatype

Date datatype

Date Datatype

Date datatype

Sunday, February 19, 2012

Date created defualt value

Date Conversion throws truncation error

Date Conversion - Flat File - YYYYMMDD

DATE CONVERSION

Friday, February 17, 2012

DATE CONVERSION

DATE CONVERSION

Tuesday, February 14, 2012

Date (not datetime) datatype in 2005?

Date/Time Stamp

Blog Archive

About Me