Date issue

I have got an issue with dateformats. When i retrieve a date field from the
sql server it is noted as 02/10/2005 17:40:00. everything fine so far. I
want to compare it to the current time. This doesn't work. When i print the
current datetime i get " 4 Nov 2005 5:40PM". How can i set sql so it returns
the current date as 02/10/2005 17:40:00.
Your help is much appreciated.
Regards Ron WallegiePrint converts to a string. What do you get when you say SELECT GETDATE()?
Different, no?
Keep in mind that I am having problems understanding your date for the very
reason that you chose an ambiguous date format. I suggest using YYYYMMDD or
YYYY-MM-DDTHH:MM:SS. These are ISO standards and are truthfully the only
safe formats you can use to represent dates in SQL Server. Anything else
can be misinterpreted by the database depending on regional settings,
dateformat, language, etc.
What do you mean by "compare it to the current time"? Compare what? A
value in a table?
"Ron Wallegie" <wallegie@.hotmail.com> wrote in message
news:eWk8U$V4FHA.2432@.TK2MSFTNGP10.phx.gbl...
>I have got an issue with dateformats. When i retrieve a date field from the
>sql server it is noted as 02/10/2005 17:40:00. everything fine so far. I
>want to compare it to the current time. This doesn't work. When i print the
>current datetime i get " 4 Nov 2005 5:40PM". How can i set sql so it
>returns the current date as 02/10/2005 17:40:00.
> Your help is much appreciated.
> Regards Ron Wallegie
>|||If you want to compare two dates to see if they are the same.
SELECT
(CASE WHEN DATEDIFF( ms , myDateField , GETDATE() ) = 0 THEN 'Same' ELSE
'Different' END) AS DateCompare
FROM
myTable
If you're using SMALLDATETIME you can use DATEDIFF( mi , ... ), because it
is only accurate to a minute.
"Ron Wallegie" <wallegie@.hotmail.com> wrote in message
news:eWk8U$V4FHA.2432@.TK2MSFTNGP10.phx.gbl...
> I have got an issue with dateformats. When i retrieve a date field from
the
> sql server it is noted as 02/10/2005 17:40:00. everything fine so far. I
> want to compare it to the current time. This doesn't work. When i print
the
> current datetime i get " 4 Nov 2005 5:40PM". How can i set sql so it
returns
> the current date as 02/10/2005 17:40:00.
> Your help is much appreciated.
> Regards Ron Wallegie
>|||Try using the convert function below.
By using the Convert function. The syntax for using the convert function
is: CONVERT ( data_type [ ( length ) ] , expression [ , style ] )
select convert(varchar,DateColumn,100) --> Nov 4 2005 11:08AM
or
I recommend converting both the date from the table and getdate()
using the function below in military time.
select convert(varchar,DateColumn,120) --> 2005-11-04 11:10:00
Select *
from table
where convert(varchar,DateColumn,120)=convert(
varchar,getdate(),120)
KT
"Ron Wallegie" <wallegie@.hotmail.com> wrote in message
news:eWk8U$V4FHA.2432@.TK2MSFTNGP10.phx.gbl...
> I have got an issue with dateformats. When i retrieve a date field from
the
> sql server it is noted as 02/10/2005 17:40:00. everything fine so far. I
> want to compare it to the current time. This doesn't work. When i print
the
> current datetime i get " 4 Nov 2005 5:40PM". How can i set sql so it
returns
> the current date as 02/10/2005 17:40:00.
> Your help is much appreciated.
> Regards Ron Wallegie
>|||Hey Ron,
There are many ways to build up date strings, please google for them as MSDN
is quite limited in what it suggests. There is also a DB or Server setting
that controls this but I'm not sure what it is right now. Here are some
examples to get you started.
SELECT SUBSTRING(CONVERT(CHAR(12),GETDATE(),1),
1,8) + ' ' +
CONVERT(CHAR(12),GETDATE(),8)
select convert(smalldatetime,convert(char(12),g
etdate(),13))
select convert(datetime,convert(char(30),getdat
e(),21))
select convert(datetime,getdate(),10)|||Thx for your reply,
Hopefully this will help.
When printing getdate() i get " 4 Nov 2005 5:40PM". When retrieving the
table value i get 02/10/2005 17:40:00.
I created a script which works fine op a other server. When i do a select
getdate() on that server i get 02/10/2005 17:40:00 date format. How can i
change the format on the other server?
Regards Ron
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:exxqlFW4FHA.3292@.tk2msftngp13.phx.gbl...
> Print converts to a string. What do you get when you say SELECT
GETDATE()?
> Different, no?
> Keep in mind that I am having problems understanding your date for the
very
> reason that you chose an ambiguous date format. I suggest using YYYYMMDD
or
> YYYY-MM-DDTHH:MM:SS. These are ISO standards and are truthfully the only
> safe formats you can use to represent dates in SQL Server. Anything else
> can be misinterpreted by the database depending on regional settings,
> dateformat, language, etc.
> What do you mean by "compare it to the current time"? Compare what? A
> value in a table?
>
>
> "Ron Wallegie" <wallegie@.hotmail.com> wrote in message
> news:eWk8U$V4FHA.2432@.TK2MSFTNGP10.phx.gbl...
the
the
>|||Just to clairify...
SET DATEFORMAT DMY -- i am assuming it's 2nd oct '05
DECLARE @.ClientDate SMALLDATETIME , @.TimeNow SMALLDATETIME
SELECT @.ClientDate = '02/10/2005 17:40:00' , @.TimeNow = GETDATE()
SELECT
@.ClientDate
, @.TimeNow
, (CASE
WHEN DATEDIFF( mi , @.ClientDate , @.TimeNow ) = 0 THEN 'Same'
ELSE 'Different'
END) AS DateCompare
"Rebecca York" <rebecca.york {at} 2ndbyte.com> wrote in message
news:436b8e34$0$140$7b0f0fd3@.mistral.news.newnet.co.uk...
> If you want to compare two dates to see if they are the same.
> SELECT
> (CASE WHEN DATEDIFF( ms , myDateField , GETDATE() ) = 0 THEN 'Same'
ELSE
> 'Different' END) AS DateCompare
> FROM
> myTable
> If you're using SMALLDATETIME you can use DATEDIFF( mi , ... ), because it
> is only accurate to a minute.
>
> "Ron Wallegie" <wallegie@.hotmail.com> wrote in message
> news:eWk8U$V4FHA.2432@.TK2MSFTNGP10.phx.gbl...
> the
> the
> returns
>|||Why do you think you need to format dates to compare them? What exactly are
you trying to do?
ML|||When starting a procedure iwant to pass all the records in a table before
the getdate() value. After the procedure stops it deletes all value before
the getdat value.
I know there is a db setting which controls the format you get when rinting
getdate(). I don't know how...
"ML" <ML@.discussions.microsoft.com> wrote in message
news:0F8F8DE5-4774-491B-A86E-41381F563B9F@.microsoft.com...
> Why do you think you need to format dates to compare them? What exactly
are
> you trying to do?
>
> ML|||Please stop thinking about the format. ***Format is irrelevant.*** You are
dealing with datetime values here, not strings. Not sure why you need to
print or select the date at all.
DECLARE @.dt SMALLDATETIME
SET @.dt = CURRENT_TIMESTAMP
-- "pass" all the rows, I have no idea what this means!
-- SELECT, maybe?
SELECT cols FROM table WHERE dt_column <= @.dt
DELETE table WHERE dt_column <= @.dt
ok?
"Ron Wallegie" <wallegie@.planet.nl> wrote in message
news:usyM%23bW4FHA.3460@.TK2MSFTNGP12.phx.gbl...
> When starting a procedure iwant to pass all the records in a table before
> the getdate() value. After the procedure stops it deletes all value before
> the getdat value.
> I know there is a db setting which controls the format you get when
> rinting
> getdate(). I don't know how...
> "ML" <ML@.discussions.microsoft.com> wrote in message
> news:0F8F8DE5-4774-491B-A86E-41381F563B9F@.microsoft.com...
> are
>