March 31, 2010 at 6:45 am
Hello
I already ran trough the forums but found no satifiable answers.
I work for a logistical firm and were using a series of interfaces (programmed in VB6) to
check & process data coming from our clients. (The interfaces are part of a program that reasonably new).
When we get an order we have to check whether the lot in the files corresponds with a lot in the warehouse, we can check on the following values lot,lottable03,lottable05,sku,storer
lot <=Is the lot we want
lottable03<=Description of what kind of good it is.
lottable05<=Expiry Date, this is the date the good has to be gone out of the warehouse & or sold.
sku<=The item number, which is unique per storer.
storer<=A value to identify the firm that supplies the goods
lottable05 is converted to UTC before being stored in the database, a function provided by the forehand mentioned program allows this. This results in the following value being stored YYYY-DD-MM 22:00:00. We don't get the hours from our client so its stored as 10PM the day before.
Now comes the stinger since last Sunday when DST started this method started converting to YYYY-DD-MM 23:00:00, since the same function is used to convert the date for the checks this results in lots not found that were inputted last week before DTS.
So I like to know if i could make the conversion in SQL.
Thank you for reading this & taking your time giving me an answer.
If I wasn't clear on something or more explanation is required please ask.
March 31, 2010 at 7:06 am
Hi,
To convert a datetime value to the format YYYY-MM-DD hh:mm:ss you write:
declare @myDate datetime
set @myDate = getdate()
-- Convert date to string with format YYYY-MM-DD hh:mm:ss
select CONVERT(char(19), @myDate, 120)
Since you had some problem with the hours being wrong, you could use DATEADD to change the hours:
-- Set the hour-part of the date to 00
select DATEADD(hh, -DATEPART(hh, @myDate), @myDate)
I hope this answers (some of) your question(s)... 🙂
/Markus
March 31, 2010 at 12:05 pm
Here's a function I wrote to convert from central time to GMT (CST or CDT to UTC in other words) - might help:
Create FUNCTION [dbo].[udfConvertToGMT]
(@InputDate datetime)
RETURNS datetime
AS
/*
Script Date: 08/11/2009
Author: Beedle
Purpose: Converts from CST (or CDT if date is during daylight savings time) to GMT
*/
--DECLARE @InputDate datetime
--Select @InputDate = '10/4/1985 08:00:00'
BEGIN
DECLARE @converted_date datetime
DECLARE @DST_Start datetime
DECLARE @DST_End datetime
DECLARE @GMT_Offset_Destination int
-- Get start and end dates for daylight savingstime for the year in question
Select@DST_Start =Case when datepart(year,@InputDate) >= 2007 then
'3/1/'+convert(varchar,DATEPART(year, @InputDate))
when datepart(year,@InputDate) between 1986 and 2006 then
'4/1/'+convert(varchar,DATEPART(year, @InputDate))
Else -- Year prior to 1986
'4/30/'+convert(varchar,DATEPART(year, @InputDate))
End,
@DST_End =Case when datepart(year,@InputDate) >= 2007 then
'11/1/'+convert(varchar,DATEPART(year, @InputDate))
Else
'10/31/'+convert(varchar,DATEPART(year, @InputDate))
End
Select@DST_Start =
--2007 till present get second Sunday of March, 1986-2006 get first Sunday of April, prior to 1986 get last sunday in April
Case when datepart(year,@InputDate) >= 2007 then
dateadd(hour,8,dateadd(day,
((ceiling((cast(17 as decimal(8,2)) % datepart(dw,@DST_Start))/10) * 7) + 8) - datepart(dw,@DST_Start),
@DST_Start))
When datepart(year,@InputDate) between 1986 and 2006 then
dateadd(hour,8,dateadd(day,
((ceiling((cast(17 as decimal(8,2)) % datepart(dw,@DST_Start))/10) * 7) + 1) - datepart(dw,@DST_Start),
@DST_Start))
Else -- Year prior to 1986
dateadd(hour,8,dateadd(day,
0-(datepart(dw,@DST_Start)-1),
@DST_Start))
End,
--2007 till present get first Sunday of November, else get last Sunday of October
@DST_End =
Case when datepart(year,@InputDate) >= 2007 then
dateadd(second, -1,dateadd(hour,8,dateadd(day,
((ceiling((cast(17 as decimal(8,2)) % datepart(dw,@DST_End))/10) * 7) + 1) - datepart(dw,@DST_End),
@DST_End)))
Else
dateadd(second, -1,dateadd(hour,8,dateadd(day,
0-(datepart(dw,@DST_End)-1),
@DST_End)))
End,
@GMT_Offset_Destination = 360
RETURN Case when @InputDate BETWEEN @DST_Start AND @DST_End then
DATEADD(MINUTE, @GMT_Offset_Destination - 60, @InputDate)
Else
DATEADD(MINUTE, @GMT_Offset_Destination, @InputDate)
End
END
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply