January 20, 2005 at 11:37 am
Hi all,
can someone please assist a complete newbie in this date field problem ?
I have a field in a tble named DateT and this is input by the users via a Intranet site in the format 2:30. In the table it is stored as 1/1/1900 2:30:00 PM. If the user then decides to edit the time for whatever reason it is displayed as 1/1/1900 2:30:00 PM. In Access you could run a query to shorten it to 2:30 but I know nothing of Triggers, stored procedures or whatever in SQL server so which is the best way for a beginner to automastically convert/update the date from 1/1/1900 2:30:00 PM to 2:30 ?
Best regards,
Mitch..........
January 20, 2005 at 1:33 pm
This should do it :
SELECT CONVERT(VARCHAR(8),GetDate(),108)
if you don't want the seconds then you can add left(..., 5) to trim that too.
This solution however means that the user could not edit the field (if it's bound to a form). The solution to that problem is to select the whole date and bind it to a textbox. Then in the properties go in format / format and select "Hour, Short" (translated from french so it might be slightly different than that in english).
January 20, 2005 at 11:42 pm
Handling dates and times causes more grief than any other issue!!
When a user enters "2:30" I presume they mean 2:30 in the afternoon today! The "1/1/1900" is the default date for 0.
I would do it this way:
select DateT = DateAdd(hh,12, DateT)
select DateT = DateT + convert(datetime,convert(char(8),getdate(),112))
This will convert it to 2:30pm today. I guess a real SQL guru can probably
do this in one line!! 112 gives the date as yyyymmdd which is how SQL stores it in datetime datatypes.
See http://www.karaszi.com/SQLServer/info_datetime.asp for an excelent explanation of datetime datatypes.
Regards, Ian Scott
January 21, 2005 at 9:49 am
Thanks gents. But where do I paste the code
"select DateT = DateAdd(hh,12, DateT)
select DateT = DateT + convert(datetime,convert(char(8),getdate(),112))"
In a Trigger for that particular table ?
Mitch........
January 23, 2005 at 2:55 pm
You can create a trigger as follows:
create trigger TestDates_Insert
on TestDates
for Insert
as
declare @dte datetime
select @dte = DateT from inserted
if (@dte <= convert(datetime, '5:00')
and @dte >= convert(datetime, '1:00'))
begin
Update TestDates
set TestDates.DateT = convert(datetime, convert(char(8), getdate(), 112)) + dateadd(hh,12,Inserted.DateT)
from TestDates, Inserted
where TestDates.id = Inserted.id
end
GO
In the above example, id would be the primary key of your table (or a field that uniquely identifies the row.
A warning on the time issue, The above code assumes that times will only be "office hours" ie no later than 5:00pm (17:00). If you want to accept times between 6:00am and 6:00pm then the above trigger will not work, You really need to trap this at it's source.
If there is no date entered and you want to use current date & time to replace what has been entered, then the trigger can be simplified to:
create trigger TestDates_Insert
on TestDates
for Insert
as
declare @dte datetime
select @dte = DateT from inserted
if (@dte <= convert(datetime, '1900-01-02'))
begin
Update TestDates
set TestDates.DateT = getdate()
from TestDates, Inserted
where TestDates.id = Inserted.id
end
GO
The above relies on the fact that SQL starts dates from 1900-01-01.
As an aside, get into the habit of expressing dates yyyy-mm-dd to avoid any confusion. Eg is 10/1/2005 January 10th 2005 or October 1st 2005?
Regards, Ian Scott
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply