|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Date issueI 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 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? Show quote "Ron Wallegie" <walle***@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 > 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 Show quote "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" <walle***@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 > > > > 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.... Show quote "ML" <M*@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? Show quote "Ron Wallegie" <walle***@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" <M*@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 > > 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. Show quote "Ron Wallegie" <walle***@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 > > 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 Show quote "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" <walle***@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 Show quote "Ron Wallegie" <walle***@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),getdate(),13)) select convert(datetime,convert(char(30),getdate(),21)) select convert(datetime,getdate(),10)  |
|||||||||||||||||||||||
Other interesting topics