(SQL Server 2000, SP3a)
Hello all!
If I have a query like:
select * from MyTable where MyDateField < '10/01/2003'
Does SQL Server implicitly convert that date string to a DATETIME datatype, or is it
implicitly converting all records' MyDateField to a string datatype? Since the VARCHAR(X)
is likely longer than the 8-byte datetime field, I suspect the latter, but how could I
find out for certain?
I suppose I could eliminate the ambiguity by explicitly converting:
select * from MyTable where MyDateField < convert(datetime, '10/01/2003')
Thanks for any help you can provide!
John PetersonJohn,
It follows the precedency rule...
http://msdn.microsoft.com/library/en-us/tsqlref/ts_da-db_2js5.asp?frame=true
-oj
RAC v2.2 & QALite!
http://www.rac4sql.net
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:OHF$SjbkDHA.1084@.tk2msftngp13.phx.gbl...
> (SQL Server 2000, SP3a)
> Hello all!
> If I have a query like:
> select * from MyTable where MyDateField < '10/01/2003'
> Does SQL Server implicitly convert that date string to a DATETIME
datatype, or is it
> implicitly converting all records' MyDateField to a string datatype?
Since the VARCHAR(X)
> is likely longer than the 8-byte datetime field, I suspect the latter, but
how could I
> find out for certain?
> I suppose I could eliminate the ambiguity by explicitly converting:
> select * from MyTable where MyDateField < convert(datetime, '10/01/2003')
> Thanks for any help you can provide!
> John Peterson
>|||Thanks, oj!
As I read this, that seems to suggest that the lower precedence string (varchar)
'10/01/2003' would be implicitly converted to the higher precedence datetime. In essence,
it would be equivalent to the second statement in my original post.
Thanks again for the information. I'll keep that link handy! :-)
"oj" <nospam_ojngo@.home.com> wrote in message
news:udw67nbkDHA.3700@.TK2MSFTNGP11.phx.gbl...
> John,
> It follows the precedency rule...
> http://msdn.microsoft.com/library/en-us/tsqlref/ts_da-db_2js5.asp?frame=true
>
> --
> -oj
> RAC v2.2 & QALite!
> http://www.rac4sql.net
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:OHF$SjbkDHA.1084@.tk2msftngp13.phx.gbl...
> > (SQL Server 2000, SP3a)
> >
> > Hello all!
> >
> > If I have a query like:
> >
> > select * from MyTable where MyDateField < '10/01/2003'
> >
> > Does SQL Server implicitly convert that date string to a DATETIME
> datatype, or is it
> > implicitly converting all records' MyDateField to a string datatype?
> Since the VARCHAR(X)
> > is likely longer than the 8-byte datetime field, I suspect the latter, but
> how could I
> > find out for certain?
> >
> > I suppose I could eliminate the ambiguity by explicitly converting:
> >
> > select * from MyTable where MyDateField < convert(datetime, '10/01/2003')
> >
> > Thanks for any help you can provide!
> >
> > John Peterson
> >
> >
>|||Hi John!
Just be aware that SQL7 did it differently. SQL7 converted the "data side" to the "column side".
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"John Peterson" <j0hnp@.comcast.net> wrote in message news:udwiCubkDHA.1656@.tk2msftngp13.phx.gbl...
> Thanks, oj!
> As I read this, that seems to suggest that the lower precedence string (varchar)
> '10/01/2003' would be implicitly converted to the higher precedence datetime. In essence,
> it would be equivalent to the second statement in my original post.
> Thanks again for the information. I'll keep that link handy! :-)
>
> "oj" <nospam_ojngo@.home.com> wrote in message
> news:udw67nbkDHA.3700@.TK2MSFTNGP11.phx.gbl...
> > John,
> >
> > It follows the precedency rule...
> >
> > http://msdn.microsoft.com/library/en-us/tsqlref/ts_da-db_2js5.asp?frame=true
> >
> >
> > --
> > -oj
> > RAC v2.2 & QALite!
> > http://www.rac4sql.net
> >
> >
> > "John Peterson" <j0hnp@.comcast.net> wrote in message
> > news:OHF$SjbkDHA.1084@.tk2msftngp13.phx.gbl...
> > > (SQL Server 2000, SP3a)
> > >
> > > Hello all!
> > >
> > > If I have a query like:
> > >
> > > select * from MyTable where MyDateField < '10/01/2003'
> > >
> > > Does SQL Server implicitly convert that date string to a DATETIME
> > datatype, or is it
> > > implicitly converting all records' MyDateField to a string datatype?
> > Since the VARCHAR(X)
> > > is likely longer than the 8-byte datetime field, I suspect the latter, but
> > how could I
> > > find out for certain?
> > >
> > > I suppose I could eliminate the ambiguity by explicitly converting:
> > >
> > > select * from MyTable where MyDateField < convert(datetime, '10/01/2003')
> > >
> > > Thanks for any help you can provide!
> > >
> > > John Peterson
> > >
> > >
> >
> >
>
Showing posts with label datetime. Show all posts
Showing posts with label datetime. Show all posts
Tuesday, March 20, 2012
Question about GroupBY and Aggregate functions.
Ok I know this is totally a noob question but I'm working with a table
of the following structure
ID (int), FK (int), Date (datetime)
I would like to select the ID & Max(Date) grouped by the FK. I don't
want to group by the ID but I want it included in the result set.
If I do a simple select like
select ID, FK, Max(Date) from tbl group by FK, ID
I get a result set that includes discreet dates for each ID, not the
max date for a given FK having this ID.
for example
ID FK Date
1 100 1/1/2006
2 100 1/2/2006
3 150 1/1/2006
4 150 1/2/2006
w/ the previous sql returns
1 100 1/1/2006
2 100 1/2/2006
3 150 1/1/2006
4 150 1/2/2006
What I would want is something like this
2 100 1/2/2006
4 150 1/2/2006
Thanks in advance.
S*untested*
select t1.ID,t1.FK,t1.Date
from tbl t1
inner join(
select FK, Max(Date)
from tbl
group by FK ) t2(FK,Date) on t2.FK=t1.FK and t2.Date=t1.Date
Note that if there are multiple IDs sharing the same
maximum date, the query will return both IDs.|||Thanks, I think that works, but is there any way to filter duplicate
dates? Perhaps a distinct on the subquery?
Also is there any way to do it without performing a date comparison?
That's what makes this particular solution work, but wouldn't that be
fairly costly from a resource perspective?|||
> Thanks, I think that works, but is there any way to filter duplicate
> dates? Perhaps a distinct on the subquery?
Not sure I understand your question. If your sample expected
results aren't what you really want (because they share
the same date), can you post some more information.
> Also is there any way to do it without performing a date comparison?
> That's what makes this particular solution work, but wouldn't that be
> fairly costly from a resource perspective?
You can't get away from doing some sort of date comparison
here since your requirements are based on the maximum date.
of the following structure
ID (int), FK (int), Date (datetime)
I would like to select the ID & Max(Date) grouped by the FK. I don't
want to group by the ID but I want it included in the result set.
If I do a simple select like
select ID, FK, Max(Date) from tbl group by FK, ID
I get a result set that includes discreet dates for each ID, not the
max date for a given FK having this ID.
for example
ID FK Date
1 100 1/1/2006
2 100 1/2/2006
3 150 1/1/2006
4 150 1/2/2006
w/ the previous sql returns
1 100 1/1/2006
2 100 1/2/2006
3 150 1/1/2006
4 150 1/2/2006
What I would want is something like this
2 100 1/2/2006
4 150 1/2/2006
Thanks in advance.
S*untested*
select t1.ID,t1.FK,t1.Date
from tbl t1
inner join(
select FK, Max(Date)
from tbl
group by FK ) t2(FK,Date) on t2.FK=t1.FK and t2.Date=t1.Date
Note that if there are multiple IDs sharing the same
maximum date, the query will return both IDs.|||Thanks, I think that works, but is there any way to filter duplicate
dates? Perhaps a distinct on the subquery?
Also is there any way to do it without performing a date comparison?
That's what makes this particular solution work, but wouldn't that be
fairly costly from a resource perspective?|||
> Thanks, I think that works, but is there any way to filter duplicate
> dates? Perhaps a distinct on the subquery?
Not sure I understand your question. If your sample expected
results aren't what you really want (because they share
the same date), can you post some more information.
> Also is there any way to do it without performing a date comparison?
> That's what makes this particular solution work, but wouldn't that be
> fairly costly from a resource perspective?
You can't get away from doing some sort of date comparison
here since your requirements are based on the maximum date.
Question about formatting a date
I have a date string that is an integer 20040119 and I converted it to a varchar and then to date format-CONVERT(datetime, CAST(my_date AS varchar(8))) AS my_date
My result is 2003-12-31 00:00:00.000. How can I now format this string so I don't get the time string at the end?
ThanksYou already did.|||Maybe I didn't explain good enough. I want to drop all the zeros at the end and just show the date and not the time.
Thanks|||Something like this:
select RIGHT(CONVERT(char(10), CONVERT(datetime, CAST(my_date AS varchar(8))),103),10) AS my_date|||select convert(char(10), convert(datetime, '20040119'), 101)|||Thanks alot Avatar. You left off a right parenthesis, but that was the least of my worries. It works just like I wanted. Thanks again.|||Avatar,
I don't suppose you could explain real quick how that works?
Thanks.|||Im glad its work for you..
Here is my explanation:
1) CAST(my_date AS varchar(8)):
Convert my_date from DATETIME to VARCHAR(8) because I'm asuming that the COLUMN is DATETIME.
2) CONVERT(datetime, CAST(my_date AS varchar(8))),103):
Transform the VARCHAR(8) from Step 1 to a DATETIME with a format 'dd/mm/yyyy' (103), to convert a datetime column.
3) CONVERT(char(10), CONVERT(datetime, CAST(my_date AS varchar8))),103) :
Now transform the DATETIME from Step 2 to a VARCHAR (once again) because the function RIGHT only works with VARCHARS.
4)RIGHT(CONVERT(char(10), CONVERT(datetime, CAST(my_date AS varchar8))),103),10):
Finally the RIGHT functions takes the first 10 characters of the string.
I hope I explain well. ;)|||I got everything up to the right function. I thought the right function returns the specified number of characters from the right end of the character string?
Thanks again for your help and patience!!!|||Originally posted by exdter
I got everything up to the right function. I thought the right function returns the specified number of characters from the right end of the character string?
Thanks again for your help and patience!!!
Oppss..!
Now I'm looking better.. the RIGHT function is not necessary.
The finaly version of the QUERY is like this:
SELECT (CONVERT(char(10),CONVERT(datetime, CAST(getdate() as varchar(12)) ),103))
Because the last CONVERT function transform the datetime to a char(10)... without the use of RIGHT truncate the '2002-01-19 00:00:00.000' date to a '19/01/2004'.
Ahh.. by the way:
The LEFT or RIGHT function cuts a string by the LEFT or RIGHT respectabely... In this case RIGHT('19/01/2004',10) is no use :P
What a mess.. :D|||Thanks a whole lot!!!|||...In other words, this should have worked to begin with:
select convert(char(10), convert(datetime, '20040119'), 101)
The only difference is that your data is coming (as you said) in INTEGER data type, the the function above should have looked like this:
select convert(char(10), convert(datetime, cast(20040119 as char(8))), 101)
101 - MM/DD/YYYY
103 - DD/MM/YYYY
Take your pick!|||Yeah, gotta wonder why you don't just use rdjabarov's simple conversion. Shorter is better in programming.
My result is 2003-12-31 00:00:00.000. How can I now format this string so I don't get the time string at the end?
ThanksYou already did.|||Maybe I didn't explain good enough. I want to drop all the zeros at the end and just show the date and not the time.
Thanks|||Something like this:
select RIGHT(CONVERT(char(10), CONVERT(datetime, CAST(my_date AS varchar(8))),103),10) AS my_date|||select convert(char(10), convert(datetime, '20040119'), 101)|||Thanks alot Avatar. You left off a right parenthesis, but that was the least of my worries. It works just like I wanted. Thanks again.|||Avatar,
I don't suppose you could explain real quick how that works?
Thanks.|||Im glad its work for you..
Here is my explanation:
1) CAST(my_date AS varchar(8)):
Convert my_date from DATETIME to VARCHAR(8) because I'm asuming that the COLUMN is DATETIME.
2) CONVERT(datetime, CAST(my_date AS varchar(8))),103):
Transform the VARCHAR(8) from Step 1 to a DATETIME with a format 'dd/mm/yyyy' (103), to convert a datetime column.
3) CONVERT(char(10), CONVERT(datetime, CAST(my_date AS varchar8))),103) :
Now transform the DATETIME from Step 2 to a VARCHAR (once again) because the function RIGHT only works with VARCHARS.
4)RIGHT(CONVERT(char(10), CONVERT(datetime, CAST(my_date AS varchar8))),103),10):
Finally the RIGHT functions takes the first 10 characters of the string.
I hope I explain well. ;)|||I got everything up to the right function. I thought the right function returns the specified number of characters from the right end of the character string?
Thanks again for your help and patience!!!|||Originally posted by exdter
I got everything up to the right function. I thought the right function returns the specified number of characters from the right end of the character string?
Thanks again for your help and patience!!!
Oppss..!
Now I'm looking better.. the RIGHT function is not necessary.
The finaly version of the QUERY is like this:
SELECT (CONVERT(char(10),CONVERT(datetime, CAST(getdate() as varchar(12)) ),103))
Because the last CONVERT function transform the datetime to a char(10)... without the use of RIGHT truncate the '2002-01-19 00:00:00.000' date to a '19/01/2004'.
Ahh.. by the way:
The LEFT or RIGHT function cuts a string by the LEFT or RIGHT respectabely... In this case RIGHT('19/01/2004',10) is no use :P
What a mess.. :D|||Thanks a whole lot!!!|||...In other words, this should have worked to begin with:
select convert(char(10), convert(datetime, '20040119'), 101)
The only difference is that your data is coming (as you said) in INTEGER data type, the the function above should have looked like this:
select convert(char(10), convert(datetime, cast(20040119 as char(8))), 101)
101 - MM/DD/YYYY
103 - DD/MM/YYYY
Take your pick!|||Yeah, gotta wonder why you don't just use rdjabarov's simple conversion. Shorter is better in programming.
Monday, March 12, 2012
Question about datetime field
Dear all,
I am quite confused when dealing with the date and time in SQL tables.
e.g. I have a table myTable and there is a field myDate. The type of myDate
is "datetime". It shows the date as 2005-10-25 00:00:00.000, for example,
in SQL Query Analyzer.
However, if I want to choose the records by the following SQL statement:
select * from myTable where myDate < getdate() - 1
Current date is Oct 25, therefore getdate() - 1 should retrieve the records
with myDate up to Oct 23. (Since myDate should be smaller than Oct 24) But i
t
also retrieves the records with myDate = 2005-10-24 00:00:00.000 . I know th
e
time factor also counts. But I don't want this to be counted. I am quite
confused. Any suggestion to get rid of the time factor?
Thanks a million.
IvanOne method to remove the time portion is with CONVERT. For example:
SELECT *
FROM myTable
WHERE myDate < CONVERT(char(8), GETDATE() - 1, 112)
Hope this helps.
Dan Guzman
SQL Server MVP
"Ivan" <Ivan@.discussions.microsoft.com> wrote in message
news:6026FA8F-6B3A-4975-90F6-894FD65FD844@.microsoft.com...
> Dear all,
> I am quite confused when dealing with the date and time in SQL tables.
> e.g. I have a table myTable and there is a field myDate. The type of
> myDate
> is "datetime". It shows the date as 2005-10-25 00:00:00.000, for example,
> in SQL Query Analyzer.
> However, if I want to choose the records by the following SQL statement:
> select * from myTable where myDate < getdate() - 1
> Current date is Oct 25, therefore getdate() - 1 should retrieve the
> records
> with myDate up to Oct 23. (Since myDate should be smaller than Oct 24) But
> it
> also retrieves the records with myDate = 2005-10-24 00:00:00.000 . I know
> the
> time factor also counts. But I don't want this to be counted. I am quite
> confused. Any suggestion to get rid of the time factor?
> Thanks a million.
> Ivan|||Dear Dan,
Thanks for your help! It's very helpful.
Ivan
"Dan Guzman" wrote:
> One method to remove the time portion is with CONVERT. For example:
> SELECT *
> FROM myTable
> WHERE myDate < CONVERT(char(8), GETDATE() - 1, 112)
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ivan" <Ivan@.discussions.microsoft.com> wrote in message
> news:6026FA8F-6B3A-4975-90F6-894FD65FD844@.microsoft.com...
>
>
I am quite confused when dealing with the date and time in SQL tables.
e.g. I have a table myTable and there is a field myDate. The type of myDate
is "datetime". It shows the date as 2005-10-25 00:00:00.000, for example,
in SQL Query Analyzer.
However, if I want to choose the records by the following SQL statement:
select * from myTable where myDate < getdate() - 1
Current date is Oct 25, therefore getdate() - 1 should retrieve the records
with myDate up to Oct 23. (Since myDate should be smaller than Oct 24) But i
t
also retrieves the records with myDate = 2005-10-24 00:00:00.000 . I know th
e
time factor also counts. But I don't want this to be counted. I am quite
confused. Any suggestion to get rid of the time factor?
Thanks a million.
IvanOne method to remove the time portion is with CONVERT. For example:
SELECT *
FROM myTable
WHERE myDate < CONVERT(char(8), GETDATE() - 1, 112)
Hope this helps.
Dan Guzman
SQL Server MVP
"Ivan" <Ivan@.discussions.microsoft.com> wrote in message
news:6026FA8F-6B3A-4975-90F6-894FD65FD844@.microsoft.com...
> Dear all,
> I am quite confused when dealing with the date and time in SQL tables.
> e.g. I have a table myTable and there is a field myDate. The type of
> myDate
> is "datetime". It shows the date as 2005-10-25 00:00:00.000, for example,
> in SQL Query Analyzer.
> However, if I want to choose the records by the following SQL statement:
> select * from myTable where myDate < getdate() - 1
> Current date is Oct 25, therefore getdate() - 1 should retrieve the
> records
> with myDate up to Oct 23. (Since myDate should be smaller than Oct 24) But
> it
> also retrieves the records with myDate = 2005-10-24 00:00:00.000 . I know
> the
> time factor also counts. But I don't want this to be counted. I am quite
> confused. Any suggestion to get rid of the time factor?
> Thanks a million.
> Ivan|||Dear Dan,
Thanks for your help! It's very helpful.
Ivan
"Dan Guzman" wrote:
> One method to remove the time portion is with CONVERT. For example:
> SELECT *
> FROM myTable
> WHERE myDate < CONVERT(char(8), GETDATE() - 1, 112)
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ivan" <Ivan@.discussions.microsoft.com> wrote in message
> news:6026FA8F-6B3A-4975-90F6-894FD65FD844@.microsoft.com...
>
>
Question about datetime field
Dear all,
I am quite confused when dealing with the date and time in SQL tables.
e.g. I have a table myTable and there is a field myDate. The type of myDate
is "datetime". It shows the date as 2005-10-25 00:00:00.000, for example,
in SQL Query Analyzer.
However, if I want to choose the records by the following SQL statement:
select * from myTable where myDate < getdate() - 1
Current date is Oct 25, therefore getdate() - 1 should retrieve the records
with myDate up to Oct 23. (Since myDate should be smaller than Oct 24) But it
also retrieves the records with myDate = 2005-10-24 00:00:00.000 . I know the
time factor also counts. But I don't want this to be counted. I am quite
confused. Any suggestion to get rid of the time factor?
Thanks a million.
IvanOne method to remove the time portion is with CONVERT. For example:
SELECT *
FROM myTable
WHERE myDate < CONVERT(char(8), GETDATE() - 1, 112)
Hope this helps.
Dan Guzman
SQL Server MVP
"Ivan" <Ivan@.discussions.microsoft.com> wrote in message
news:6026FA8F-6B3A-4975-90F6-894FD65FD844@.microsoft.com...
> Dear all,
> I am quite confused when dealing with the date and time in SQL tables.
> e.g. I have a table myTable and there is a field myDate. The type of
> myDate
> is "datetime". It shows the date as 2005-10-25 00:00:00.000, for example,
> in SQL Query Analyzer.
> However, if I want to choose the records by the following SQL statement:
> select * from myTable where myDate < getdate() - 1
> Current date is Oct 25, therefore getdate() - 1 should retrieve the
> records
> with myDate up to Oct 23. (Since myDate should be smaller than Oct 24) But
> it
> also retrieves the records with myDate = 2005-10-24 00:00:00.000 . I know
> the
> time factor also counts. But I don't want this to be counted. I am quite
> confused. Any suggestion to get rid of the time factor?
> Thanks a million.
> Ivan|||Dear Dan,
Thanks for your help! It's very helpful.
Ivan
"Dan Guzman" wrote:
> One method to remove the time portion is with CONVERT. For example:
> SELECT *
> FROM myTable
> WHERE myDate < CONVERT(char(8), GETDATE() - 1, 112)
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ivan" <Ivan@.discussions.microsoft.com> wrote in message
> news:6026FA8F-6B3A-4975-90F6-894FD65FD844@.microsoft.com...
> > Dear all,
> >
> > I am quite confused when dealing with the date and time in SQL tables.
> >
> > e.g. I have a table myTable and there is a field myDate. The type of
> > myDate
> > is "datetime". It shows the date as 2005-10-25 00:00:00.000, for example,
> > in SQL Query Analyzer.
> > However, if I want to choose the records by the following SQL statement:
> >
> > select * from myTable where myDate < getdate() - 1
> >
> > Current date is Oct 25, therefore getdate() - 1 should retrieve the
> > records
> > with myDate up to Oct 23. (Since myDate should be smaller than Oct 24) But
> > it
> > also retrieves the records with myDate = 2005-10-24 00:00:00.000 . I know
> > the
> > time factor also counts. But I don't want this to be counted. I am quite
> > confused. Any suggestion to get rid of the time factor?
> > Thanks a million.
> >
> > Ivan
>
>
I am quite confused when dealing with the date and time in SQL tables.
e.g. I have a table myTable and there is a field myDate. The type of myDate
is "datetime". It shows the date as 2005-10-25 00:00:00.000, for example,
in SQL Query Analyzer.
However, if I want to choose the records by the following SQL statement:
select * from myTable where myDate < getdate() - 1
Current date is Oct 25, therefore getdate() - 1 should retrieve the records
with myDate up to Oct 23. (Since myDate should be smaller than Oct 24) But it
also retrieves the records with myDate = 2005-10-24 00:00:00.000 . I know the
time factor also counts. But I don't want this to be counted. I am quite
confused. Any suggestion to get rid of the time factor?
Thanks a million.
IvanOne method to remove the time portion is with CONVERT. For example:
SELECT *
FROM myTable
WHERE myDate < CONVERT(char(8), GETDATE() - 1, 112)
Hope this helps.
Dan Guzman
SQL Server MVP
"Ivan" <Ivan@.discussions.microsoft.com> wrote in message
news:6026FA8F-6B3A-4975-90F6-894FD65FD844@.microsoft.com...
> Dear all,
> I am quite confused when dealing with the date and time in SQL tables.
> e.g. I have a table myTable and there is a field myDate. The type of
> myDate
> is "datetime". It shows the date as 2005-10-25 00:00:00.000, for example,
> in SQL Query Analyzer.
> However, if I want to choose the records by the following SQL statement:
> select * from myTable where myDate < getdate() - 1
> Current date is Oct 25, therefore getdate() - 1 should retrieve the
> records
> with myDate up to Oct 23. (Since myDate should be smaller than Oct 24) But
> it
> also retrieves the records with myDate = 2005-10-24 00:00:00.000 . I know
> the
> time factor also counts. But I don't want this to be counted. I am quite
> confused. Any suggestion to get rid of the time factor?
> Thanks a million.
> Ivan|||Dear Dan,
Thanks for your help! It's very helpful.
Ivan
"Dan Guzman" wrote:
> One method to remove the time portion is with CONVERT. For example:
> SELECT *
> FROM myTable
> WHERE myDate < CONVERT(char(8), GETDATE() - 1, 112)
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ivan" <Ivan@.discussions.microsoft.com> wrote in message
> news:6026FA8F-6B3A-4975-90F6-894FD65FD844@.microsoft.com...
> > Dear all,
> >
> > I am quite confused when dealing with the date and time in SQL tables.
> >
> > e.g. I have a table myTable and there is a field myDate. The type of
> > myDate
> > is "datetime". It shows the date as 2005-10-25 00:00:00.000, for example,
> > in SQL Query Analyzer.
> > However, if I want to choose the records by the following SQL statement:
> >
> > select * from myTable where myDate < getdate() - 1
> >
> > Current date is Oct 25, therefore getdate() - 1 should retrieve the
> > records
> > with myDate up to Oct 23. (Since myDate should be smaller than Oct 24) But
> > it
> > also retrieves the records with myDate = 2005-10-24 00:00:00.000 . I know
> > the
> > time factor also counts. But I don't want this to be counted. I am quite
> > confused. Any suggestion to get rid of the time factor?
> > Thanks a million.
> >
> > Ivan
>
>
Question about datetime field
Dear all,
I am quite confused when dealing with the date and time in SQL tables.
e.g. I have a table myTable and there is a field myDate. The type of myDate
is "datetime". It shows the date as 2005-10-25 00:00:00.000, for example,
in SQL Query Analyzer.
However, if I want to choose the records by the following SQL statement:
select * from myTable where myDate < getdate() - 1
Current date is Oct 25, therefore getdate() - 1 should retrieve the records
with myDate up to Oct 23. (Since myDate should be smaller than Oct 24) But it
also retrieves the records with myDate = 2005-10-24 00:00:00.000 . I know the
time factor also counts. But I don't want this to be counted. I am quite
confused. Any suggestion to get rid of the time factor?
Thanks a million.
Ivan
One method to remove the time portion is with CONVERT. For example:
SELECT *
FROM myTable
WHERE myDate < CONVERT(char(8), GETDATE() - 1, 112)
Hope this helps.
Dan Guzman
SQL Server MVP
"Ivan" <Ivan@.discussions.microsoft.com> wrote in message
news:6026FA8F-6B3A-4975-90F6-894FD65FD844@.microsoft.com...
> Dear all,
> I am quite confused when dealing with the date and time in SQL tables.
> e.g. I have a table myTable and there is a field myDate. The type of
> myDate
> is "datetime". It shows the date as 2005-10-25 00:00:00.000, for example,
> in SQL Query Analyzer.
> However, if I want to choose the records by the following SQL statement:
> select * from myTable where myDate < getdate() - 1
> Current date is Oct 25, therefore getdate() - 1 should retrieve the
> records
> with myDate up to Oct 23. (Since myDate should be smaller than Oct 24) But
> it
> also retrieves the records with myDate = 2005-10-24 00:00:00.000 . I know
> the
> time factor also counts. But I don't want this to be counted. I am quite
> confused. Any suggestion to get rid of the time factor?
> Thanks a million.
> Ivan
|||Dear Dan,
Thanks for your help! It's very helpful.
Ivan
"Dan Guzman" wrote:
> One method to remove the time portion is with CONVERT. For example:
> SELECT *
> FROM myTable
> WHERE myDate < CONVERT(char(8), GETDATE() - 1, 112)
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ivan" <Ivan@.discussions.microsoft.com> wrote in message
> news:6026FA8F-6B3A-4975-90F6-894FD65FD844@.microsoft.com...
>
>
I am quite confused when dealing with the date and time in SQL tables.
e.g. I have a table myTable and there is a field myDate. The type of myDate
is "datetime". It shows the date as 2005-10-25 00:00:00.000, for example,
in SQL Query Analyzer.
However, if I want to choose the records by the following SQL statement:
select * from myTable where myDate < getdate() - 1
Current date is Oct 25, therefore getdate() - 1 should retrieve the records
with myDate up to Oct 23. (Since myDate should be smaller than Oct 24) But it
also retrieves the records with myDate = 2005-10-24 00:00:00.000 . I know the
time factor also counts. But I don't want this to be counted. I am quite
confused. Any suggestion to get rid of the time factor?
Thanks a million.
Ivan
One method to remove the time portion is with CONVERT. For example:
SELECT *
FROM myTable
WHERE myDate < CONVERT(char(8), GETDATE() - 1, 112)
Hope this helps.
Dan Guzman
SQL Server MVP
"Ivan" <Ivan@.discussions.microsoft.com> wrote in message
news:6026FA8F-6B3A-4975-90F6-894FD65FD844@.microsoft.com...
> Dear all,
> I am quite confused when dealing with the date and time in SQL tables.
> e.g. I have a table myTable and there is a field myDate. The type of
> myDate
> is "datetime". It shows the date as 2005-10-25 00:00:00.000, for example,
> in SQL Query Analyzer.
> However, if I want to choose the records by the following SQL statement:
> select * from myTable where myDate < getdate() - 1
> Current date is Oct 25, therefore getdate() - 1 should retrieve the
> records
> with myDate up to Oct 23. (Since myDate should be smaller than Oct 24) But
> it
> also retrieves the records with myDate = 2005-10-24 00:00:00.000 . I know
> the
> time factor also counts. But I don't want this to be counted. I am quite
> confused. Any suggestion to get rid of the time factor?
> Thanks a million.
> Ivan
|||Dear Dan,
Thanks for your help! It's very helpful.
Ivan
"Dan Guzman" wrote:
> One method to remove the time portion is with CONVERT. For example:
> SELECT *
> FROM myTable
> WHERE myDate < CONVERT(char(8), GETDATE() - 1, 112)
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ivan" <Ivan@.discussions.microsoft.com> wrote in message
> news:6026FA8F-6B3A-4975-90F6-894FD65FD844@.microsoft.com...
>
>
Question about datetime
Hi,
I have a table in SQL Server 2000, a field StatDate datatype datatime, 8 all
null true
If the user do not enter a date in the text box it is returning a error type
mismatch ever though the field is set to allow Nulls.
What can I do to correct this
Thanks
DibCan you post the datetime format you are using while inserting the data?
Anith|||Check the code that generates an insert from the empty
text box. My guess: the insert is trying to put the empty
string '' into a datetime column. The empty string is not
a valid datetime string.
Steve Kass
Drew University
Dib wrote:
>Hi,
>I have a table in SQL Server 2000, a field StatDate datatype datatime, 8 al
l
>null true
>If the user do not enter a date in the text box it is returning a error typ
e
>mismatch ever though the field is set to allow Nulls.
>What can I do to correct this
>Thanks
>Dib
>
>|||There is no format, however when the form loads if the field is blank I am
inserting "" empty string to the textbox,
if you think this is the problem how can I correct it, When in Debug Mode
the textbox is an "" empty string.
Thanks
Dib
"Steve Kass" <skass@.drew.edu> wrote in message
news:uCEVMAMKFHA.2728@.TK2MSFTNGP10.phx.gbl...
> Check the code that generates an insert from the empty
> text box. My guess: the insert is trying to put the empty
> string '' into a datetime column. The empty string is not
> a valid datetime string.
> Steve Kass
> Drew University
> Dib wrote:
>
all
type|||I don't know. If there is a newsgroup specific to the particular front
end you are using to create this form and connect it with the database,
that might be a better place to ask. If you want a SQL Server solution,
the best I can suggest is to insert into a varchar-type column in a
staging table or use an instead of trigger to capture the form
information and generate an insert after turning '' into NULL. But
neither of these is as good a solution finding a way to create the
correct insert statement. I would expect most front-end programs to
have some kind of control that corresponds more directly to SQL Server
datetime than just a string/text control.
SK
Dib wrote:
>There is no format, however when the form loads if the field is blank I am
>inserting "" empty string to the textbox,
>if you think this is the problem how can I correct it, When in Debug Mode
>the textbox is an "" empty string.
>Thanks
>Dib
>"Steve Kass" <skass@.drew.edu> wrote in message
>news:uCEVMAMKFHA.2728@.TK2MSFTNGP10.phx.gbl...
>
>all
>
>type
>
>
>|||Thanks
Dib
"Steve Kass" <skass@.drew.edu> wrote in message
news:ePzRSXMKFHA.2764@.tk2msftngp13.phx.gbl...
> I don't know. If there is a newsgroup specific to the particular front
> end you are using to create this form and connect it with the database,
> that might be a better place to ask. If you want a SQL Server solution,
> the best I can suggest is to insert into a varchar-type column in a
> staging table or use an instead of trigger to capture the form
> information and generate an insert after turning '' into NULL. But
> neither of these is as good a solution finding a way to create the
> correct insert statement. I would expect most front-end programs to
> have some kind of control that corresponds more directly to SQL Server
> datetime than just a string/text control.
> SK
> Dib wrote:
>
am
8|||You cannot insert "" into a datefield. What I usually do is create a stored
procedure to do the insert with input variables defined that have a default
value of Null. Then from the form, I only send those fields which have a
value. The insert statment in the SP will then give the value of Null to any
variables not sent and insert a Null value into the table for that field.
If you don't want to use an SP, have the form specifically send Null as the
value for the field.
"Dib" wrote:
> Hi,
> I have a table in SQL Server 2000, a field StatDate datatype datatime, 8 a
ll
> null true
> If the user do not enter a date in the text box it is returning a error ty
pe
> mismatch ever though the field is set to allow Nulls.
> What can I do to correct this
> Thanks
> Dib
>
>
I have a table in SQL Server 2000, a field StatDate datatype datatime, 8 all
null true
If the user do not enter a date in the text box it is returning a error type
mismatch ever though the field is set to allow Nulls.
What can I do to correct this
Thanks
DibCan you post the datetime format you are using while inserting the data?
Anith|||Check the code that generates an insert from the empty
text box. My guess: the insert is trying to put the empty
string '' into a datetime column. The empty string is not
a valid datetime string.
Steve Kass
Drew University
Dib wrote:
>Hi,
>I have a table in SQL Server 2000, a field StatDate datatype datatime, 8 al
l
>null true
>If the user do not enter a date in the text box it is returning a error typ
e
>mismatch ever though the field is set to allow Nulls.
>What can I do to correct this
>Thanks
>Dib
>
>|||There is no format, however when the form loads if the field is blank I am
inserting "" empty string to the textbox,
if you think this is the problem how can I correct it, When in Debug Mode
the textbox is an "" empty string.
Thanks
Dib
"Steve Kass" <skass@.drew.edu> wrote in message
news:uCEVMAMKFHA.2728@.TK2MSFTNGP10.phx.gbl...
> Check the code that generates an insert from the empty
> text box. My guess: the insert is trying to put the empty
> string '' into a datetime column. The empty string is not
> a valid datetime string.
> Steve Kass
> Drew University
> Dib wrote:
>
all
type|||I don't know. If there is a newsgroup specific to the particular front
end you are using to create this form and connect it with the database,
that might be a better place to ask. If you want a SQL Server solution,
the best I can suggest is to insert into a varchar-type column in a
staging table or use an instead of trigger to capture the form
information and generate an insert after turning '' into NULL. But
neither of these is as good a solution finding a way to create the
correct insert statement. I would expect most front-end programs to
have some kind of control that corresponds more directly to SQL Server
datetime than just a string/text control.
SK
Dib wrote:
>There is no format, however when the form loads if the field is blank I am
>inserting "" empty string to the textbox,
>if you think this is the problem how can I correct it, When in Debug Mode
>the textbox is an "" empty string.
>Thanks
>Dib
>"Steve Kass" <skass@.drew.edu> wrote in message
>news:uCEVMAMKFHA.2728@.TK2MSFTNGP10.phx.gbl...
>
>all
>
>type
>
>
>|||Thanks
Dib
"Steve Kass" <skass@.drew.edu> wrote in message
news:ePzRSXMKFHA.2764@.tk2msftngp13.phx.gbl...
> I don't know. If there is a newsgroup specific to the particular front
> end you are using to create this form and connect it with the database,
> that might be a better place to ask. If you want a SQL Server solution,
> the best I can suggest is to insert into a varchar-type column in a
> staging table or use an instead of trigger to capture the form
> information and generate an insert after turning '' into NULL. But
> neither of these is as good a solution finding a way to create the
> correct insert statement. I would expect most front-end programs to
> have some kind of control that corresponds more directly to SQL Server
> datetime than just a string/text control.
> SK
> Dib wrote:
>
am
8|||You cannot insert "" into a datefield. What I usually do is create a stored
procedure to do the insert with input variables defined that have a default
value of Null. Then from the form, I only send those fields which have a
value. The insert statment in the SP will then give the value of Null to any
variables not sent and insert a Null value into the table for that field.
If you don't want to use an SP, have the form specifically send Null as the
value for the field.
"Dib" wrote:
> Hi,
> I have a table in SQL Server 2000, a field StatDate datatype datatime, 8 a
ll
> null true
> If the user do not enter a date in the text box it is returning a error ty
pe
> mismatch ever though the field is set to allow Nulls.
> What can I do to correct this
> Thanks
> Dib
>
>
Subscribe to:
Posts (Atom)