Dates in an update statement

Hi

This is really bugging me. All I want to do is add a date in an update statement but when I do it, its adding the wrong date e.g.

here is my update statement trying to update a date field

update tbl_xset [date]=01/02/2006where id = 1

when i do this the data comes back as 1900-01-01 00:00:00.000, am I doing something wrong, what is the correct way of doing this. Thanks.

How about changing that to:

update tbl_xset [date] ='01/02/2006'where id = 1

|||try using thisupdate tbl_x set [date]='01/02/2006' where id = 1-------do not forget to mark the post as answered if it helped you|||

thanks guys that worked

DateAdd function on 30th and 31st of month

Hi Everyone,
When I run the dateadd function adding months on 30th and 31st of any
months returns the same result. I am OK with that because logically it
is correct.
If I have to do a reverse lookup for the date, how can I do that.
Here is the code sample...
select dateadd(mm,6,'2006-05-30 00:00:00.000')
select dateadd(mm,6,'2006-05-31 00:00:00.000')
-- 2006-11-30 00:00:00.000
-- 2006-11-30 00:00:00.000
select dateadd(mm,-6,'2006-11-30 00:00:00.000')
select dateadd(mm,-6,'2006-12-01 00:00:00.000')
--2006-05-30 00:00:00.000
--2006-06-01 00:00:00.000
The problem I have is when I do a reverse lookup I am missing all the
records which got created on
2006-05-31 00:00:00.000.
Thanks in Advance...
Thanks,
GaneshGanesh wrote:
> Hi Everyone,
> When I run the dateadd function adding months on 30th and 31st of any
> months returns the same result. I am OK with that because logically it
> is correct.
> If I have to do a reverse lookup for the date, how can I do that.
> Here is the code sample...
> select dateadd(mm,6,'2006-05-30 00:00:00.000')
> select dateadd(mm,6,'2006-05-31 00:00:00.000')
> -- 2006-11-30 00:00:00.000
> -- 2006-11-30 00:00:00.000
> select dateadd(mm,-6,'2006-11-30 00:00:00.000')
> select dateadd(mm,-6,'2006-12-01 00:00:00.000')
> --2006-05-30 00:00:00.000
> --2006-06-01 00:00:00.000
> The problem I have is when I do a reverse lookup I am missing all the
> records which got created on
> 2006-05-31 00:00:00.000.
> Thanks in Advance...
> Thanks,
> Ganesh
>
This will show you the date of the last day of the month "six months ago":
SELECT DATEADD(dd, -1, DATEADD(mm, DATEDIFF(mm, 0, DATEADD(mm, -5,
GETDATE())), 0))
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Please don't multi-post.
"Ganesh" <babuganesh2000@.gmail.com> wrote in message
news:1164661068.378626.271490@.l12g2000cwl.googlegroups.com...
> Hi Everyone,
> When I run the dateadd function adding months on 30th and 31st of any
> months returns the same result. I am OK with that because logically it
> is correct.
> If I have to do a reverse lookup for the date, how can I do that.
> Here is the code sample...
> select dateadd(mm,6,'2006-05-30 00:00:00.000')
> select dateadd(mm,6,'2006-05-31 00:00:00.000')
> -- 2006-11-30 00:00:00.000
> -- 2006-11-30 00:00:00.000
> select dateadd(mm,-6,'2006-11-30 00:00:00.000')
> select dateadd(mm,-6,'2006-12-01 00:00:00.000')
> --2006-05-30 00:00:00.000
> --2006-06-01 00:00:00.000
> The problem I have is when I do a reverse lookup I am missing all the
> records which got created on
> 2006-05-31 00:00:00.000.
> Thanks in Advance...
> Thanks,
> Ganesh
>

DateAdd function on 30th and 31st of month

Hi Everyone,
When I run the dateadd function adding months on 30th and 31st of any
months returns the same result. I am OK with that because logically it
is correct.
If I have to do a reverse lookup for the date, how can I do that.
Here is the code sample...
select dateadd(mm,6,'2006-05-30 00:00:00.000')
select dateadd(mm,6,'2006-05-31 00:00:00.000')
-- 2006-11-30 00:00:00.000
-- 2006-11-30 00:00:00.000
select dateadd(mm,-6,'2006-11-30 00:00:00.000')
select dateadd(mm,-6,'2006-12-01 00:00:00.000')
--2006-05-30 00:00:00.000
--2006-06-01 00:00:00.000
The problem I have is when I do a reverse lookup I am missing all the
records which got created on
2006-05-31 00:00:00.000.
Thanks in Advance...
Thanks,
Ganesh
Ganesh wrote:
> Hi Everyone,
> When I run the dateadd function adding months on 30th and 31st of any
> months returns the same result. I am OK with that because logically it
> is correct.
> If I have to do a reverse lookup for the date, how can I do that.
> Here is the code sample...
> select dateadd(mm,6,'2006-05-30 00:00:00.000')
> select dateadd(mm,6,'2006-05-31 00:00:00.000')
> -- 2006-11-30 00:00:00.000
> -- 2006-11-30 00:00:00.000
> select dateadd(mm,-6,'2006-11-30 00:00:00.000')
> select dateadd(mm,-6,'2006-12-01 00:00:00.000')
> --2006-05-30 00:00:00.000
> --2006-06-01 00:00:00.000
> The problem I have is when I do a reverse lookup I am missing all the
> records which got created on
> 2006-05-31 00:00:00.000.
> Thanks in Advance...
> Thanks,
> Ganesh
>
This will show you the date of the last day of the month "six months ago":
SELECT DATEADD(dd, -1, DATEADD(mm, DATEDIFF(mm, 0, DATEADD(mm, -5,
GETDATE())), 0))
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Please don't multi-post.
"Ganesh" <babuganesh2000@.gmail.com> wrote in message
news:1164661068.378626.271490@.l12g2000cwl.googlegr oups.com...
> Hi Everyone,
> When I run the dateadd function adding months on 30th and 31st of any
> months returns the same result. I am OK with that because logically it
> is correct.
> If I have to do a reverse lookup for the date, how can I do that.
> Here is the code sample...
> select dateadd(mm,6,'2006-05-30 00:00:00.000')
> select dateadd(mm,6,'2006-05-31 00:00:00.000')
> -- 2006-11-30 00:00:00.000
> -- 2006-11-30 00:00:00.000
> select dateadd(mm,-6,'2006-11-30 00:00:00.000')
> select dateadd(mm,-6,'2006-12-01 00:00:00.000')
> --2006-05-30 00:00:00.000
> --2006-06-01 00:00:00.000
> The problem I have is when I do a reverse lookup I am missing all the
> records which got created on
> 2006-05-31 00:00:00.000.
> Thanks in Advance...
> Thanks,
> Ganesh
>

DateAdd function on 30th and 31st of month

Hi Everyone,
When I run the dateadd function adding months on 30th and 31st of any
months returns the same result. I am OK with that because logically it
is correct.
If I have to do a reverse lookup for the date, how can I do that.
Here is the code sample...
select dateadd(mm,6,'2006-05-30 00:00:00.000')
select dateadd(mm,6,'2006-05-31 00:00:00.000')
-- 2006-11-30 00:00:00.000
-- 2006-11-30 00:00:00.000
select dateadd(mm,-6,'2006-11-30 00:00:00.000')
select dateadd(mm,-6,'2006-12-01 00:00:00.000')
--2006-05-30 00:00:00.000
--2006-06-01 00:00:00.000
The problem I have is when I do a reverse lookup I am missing all the
records which got created on
2006-05-31 00:00:00.000.
Thanks in Advance...
Thanks,
GaneshGanesh wrote:
> Hi Everyone,
> When I run the dateadd function adding months on 30th and 31st of any
> months returns the same result. I am OK with that because logically it
> is correct.
> If I have to do a reverse lookup for the date, how can I do that.
> Here is the code sample...
> select dateadd(mm,6,'2006-05-30 00:00:00.000')
> select dateadd(mm,6,'2006-05-31 00:00:00.000')
> -- 2006-11-30 00:00:00.000
> -- 2006-11-30 00:00:00.000
> select dateadd(mm,-6,'2006-11-30 00:00:00.000')
> select dateadd(mm,-6,'2006-12-01 00:00:00.000')
> --2006-05-30 00:00:00.000
> --2006-06-01 00:00:00.000
> The problem I have is when I do a reverse lookup I am missing all the
> records which got created on
> 2006-05-31 00:00:00.000.
> Thanks in Advance...
> Thanks,
> Ganesh
>
This will show you the date of the last day of the month "six months ago":
SELECT DATEADD(dd, -1, DATEADD(mm, DATEDIFF(mm, 0, DATEADD(mm, -5,
GETDATE())), 0))
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Please don't multi-post.
"Ganesh" <babuganesh2000@.gmail.com> wrote in message
news:1164661068.378626.271490@.l12g2000cwl.googlegroups.com...
> Hi Everyone,
> When I run the dateadd function adding months on 30th and 31st of any
> months returns the same result. I am OK with that because logically it
> is correct.
> If I have to do a reverse lookup for the date, how can I do that.
> Here is the code sample...
> select dateadd(mm,6,'2006-05-30 00:00:00.000')
> select dateadd(mm,6,'2006-05-31 00:00:00.000')
> -- 2006-11-30 00:00:00.000
> -- 2006-11-30 00:00:00.000
> select dateadd(mm,-6,'2006-11-30 00:00:00.000')
> select dateadd(mm,-6,'2006-12-01 00:00:00.000')
> --2006-05-30 00:00:00.000
> --2006-06-01 00:00:00.000
> The problem I have is when I do a reverse lookup I am missing all the
> records which got created on
> 2006-05-31 00:00:00.000.
> Thanks in Advance...
> Thanks,
> Ganesh
>