Day of the week

Hi group ,
Please let me know which formule is usefull for calculate the day of week
based in a date.
my date is datetime.
day = convert(int,MyDate) % 7
is't working
Look up DATENAME & DATEPART functions in SQL Server Books Online.
Anith
|||SELECT DATENAME(dw, getdate())
|||But I need the number of the week. thanks you anyway.
Best regard
MArio
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:BBA8C3BE-0086-49B4-B109-843FEEBED93D@.microsoft.com...
> SELECT DATENAME(dw, getdate())
|||? number of the week, or day of the week? Your message and subject don't
agree.
In any case, as Anith suggested, look at DATENAME and DATEPART in Books
Online.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Mario Reiley" <mreiley@.cantv.net> wrote in message
news:#gi0eLENEHA.2640@.TK2MSFTNGP12.phx.gbl...
> But I need the number of the week. thanks you anyway.
|||I like this formula:
(@.@.DATEFIRST + DATEPART(dw, date) ) % 7
It is always
0 on Sunday
1 on Monday
2 on Monday
up to
6 on Friday

Day of the week

Hi group ,
Please let me know which formule is usefull for calculate the day of week
based in a date.
my date is datetime.
day = convert(int,MyDate) % 7
is't workingLook up DATENAME & DATEPART functions in SQL Server Books Online.
--
Anith|||But I need the number of the week. thanks you anyway.
Best regard
MArio
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:BBA8C3BE-0086-49B4-B109-843FEEBED93D@.microsoft.com...
> SELECT DATENAME(dw, getdate())|||? number of the week, or day of the week? Your message and subject don't
agree.
In any case, as Anith suggested, look at DATENAME and DATEPART in Books
Online.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Mario Reiley" <mreiley@.cantv.net> wrote in message
news:#gi0eLENEHA.2640@.TK2MSFTNGP12.phx.gbl...
> But I need the number of the week. thanks you anyway.sql

Day of the week

Hi group ,
Please let me know which formule is usefull for calculate the day of week
based in a date.
my date is datetime.
day = convert(int,MyDate) % 7
is't workingLook up DATENAME & DATEPART functions in SQL Server Books Online.
Anith|||SELECT DATENAME(dw, getdate())|||But I need the number of the week. thanks you anyway.
Best regard
MArio
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:BBA8C3BE-0086-49B4-B109-843FEEBED93D@.microsoft.com...
> SELECT DATENAME(dw, getdate())|||? number of the week, or day of the week? Your message and subject don't
agree.
In any case, as Anith suggested, look at DATENAME and DATEPART in Books
Online.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Mario Reiley" <mreiley@.cantv.net> wrote in message
news:#gi0eLENEHA.2640@.TK2MSFTNGP12.phx.gbl...
> But I need the number of the week. thanks you anyway.|||I like this formula:
(@.@.DATEFIRST + DATEPART(dw, date) ) % 7
It is always
0 on Sunday
1 on Monday
2 on Monday
up to
6 on Friday

Datetime problem

an existing application sends server an sql string like
insert into dbo.test (c1, c2) values (1, '22.1.2006 10:10:10')
where c1 is an int, and c2 is a datetime field. This command returns an erro
r.
Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an
out-of-range datetime value.
The statement has been terminated.
when I change that command like following
SET DATEFORMAT dmy
insert into dbo.test (c1, c2) values (1, '22.1.2006 10:10:10')
it works fine.
I want to set server always accepts dates im dmy format.
What can I do for this.
Thanks in advanceCould you instead pass dates in the following format? It always works:
YYYYMMDD HH:MM:SS
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Levent Helvacioglu" <Levent Helvacioglu@.discussions.microsoft.com> wrote in
message news:813C2E24-BF6A-417C-97A6-567004845256@.microsoft.com...
> an existing application sends server an sql string like
> insert into dbo.test (c1, c2) values (1, '22.1.2006 10:10:10')
> where c1 is an int, and c2 is a datetime field. This command returns an
error.
> Server: Msg 242, Level 16, State 3, Line 1
> The conversion of a char data type to a datetime data type resulted in an
> out-of-range datetime value.
> The statement has been terminated.
> when I change that command like following
> SET DATEFORMAT dmy
> insert into dbo.test (c1, c2) values (1, '22.1.2006 10:10:10')
> it works fine.
> I want to set server always accepts dates im dmy format.
> What can I do for this.
> Thanks in advance|||that way requires application change. Actually there is lots of data in dmy
format. When server changed to SQL 2000, application get following error
message from server. It was work fine with previous version SQL server, but
not SQL 2000
"Narayana Vyas Kondreddi" wrote:

> Could you instead pass dates in the following format? It always works:
> YYYYMMDD HH:MM:SS
> --
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Levent Helvacioglu" <Levent Helvacioglu@.discussions.microsoft.com> wrote
in
> message news:813C2E24-BF6A-417C-97A6-567004845256@.microsoft.com...
> error.
>
>|||This might shine some light on the problem: http://www.karaszi.com/SQLServer/in...
datetime.asp, more
specifically rl]
Tibor Karaszi, SQL Server MVP
[url]http://www.karaszi.com/sqlserver/default.asp" target="_blank">http://www.karaszi.com/SQLServer/in...ver/default.asp
http://www.solidqualitylearning.com/
"Levent Helvacioglu" <LeventHelvacioglu@.discussions.microsoft.com> wrote in
message
news:D5CB6259-F990-49CA-A7E1-FE263CFF1335@.microsoft.com...
> that way requires application change. Actually there is lots of data in dm
y
> format. When server changed to SQL 2000, application get following error
> message from server. It was work fine with previous version SQL server, bu
t
> not SQL 2000
> "Narayana Vyas Kondreddi" wrote:
>|||When I set logins default language by enterpirse manager, it runs normal.
Thanks for help :)
"Tibor Karaszi" wrote:

> This might shine some light on the problem: http://www.karaszi.com/SQLServer/in...o_datetime.asp, more
> specifically /url]
> --
> Tibor Karaszi, SQL Server MVP
> [url]http://www.karaszi.com/sqlserver/default.asp" target="_blank">http://www.karaszi.com/SQLServer/in...ver/default.asp
> http://www.solidqualitylearning.com/
>
> "Levent Helvacioglu" <LeventHelvacioglu@.discussions.microsoft.com> wrote i
n message
> news:D5CB6259-F990-49CA-A7E1-FE263CFF1335@.microsoft.com...
>|||Create INSTEAD OF trigger on your table and reformat an input in it.
"Levent Helvacioglu" <Levent Helvacioglu@.discussions.microsoft.com> wrote in
message news:813C2E24-BF6A-417C-97A6-567004845256@.microsoft.com...
> an existing application sends server an sql string like
> insert into dbo.test (c1, c2) values (1, '22.1.2006 10:10:10')
> where c1 is an int, and c2 is a datetime field. This command returns an
> error.
> Server: Msg 242, Level 16, State 3, Line 1
> The conversion of a char data type to a datetime data type resulted in an
> out-of-range datetime value.
> The statement has been terminated.
> when I change that command like following
> SET DATEFORMAT dmy
> insert into dbo.test (c1, c2) values (1, '22.1.2006 10:10:10')
> it works fine.
> I want to set server always accepts dates im dmy format.
> What can I do for this.
> Thanks in advance

datetime function with no time component?

Hi All,

When I compare dates but I want to ignore the time within the datetime I find myself doing this:

CONVERT(int, CONVERT(char(8), @.MyDate, 112))

style 112 is yyyymmdd

int is very predictable for comparisons, and performs well too.

It works but it is not readable, especially if you have several of these expressions in the same WHERE clause or CASE stmt. I also tried a udf but that has its own reusability problems across dbs and projects.

Is there a cleaner way to do this with a system function?

Carl

If you just want to compare dates, ignoring times, you could use the datediff function:

WHERE datediff( day, MyFirstDate, MyOtherDateTime ) = 0

For example:

Code Snippet

SELECT
Match = CASE
WHEN datediff( day, '2007/07/07 08:45 AM', getdate() ) = 0
THEN 'Match -Same Day'
ELSE 'Bummer! -No Match'
END,
NoMatch = CASE
WHEN datediff( day, '2007/07/06 08:45 AM', getdate() ) = 0
THEN 'Same Day'
ELSE 'Different Day'
END

Match NoMatch
-- -
Match -Same Day Different Day

DATEDIFF(), using the 'day' parameter, verifies that the two values are the same date IF there is NO difference [ = 0 ].

|||

Thanks Arnie,

For = and != logic, this is cleaner.

Not much of an improvement in readability for >, < , !>, and !< type comparisons

Carl

|||

And not too good for performance either.

While using the datediff() process 'looks' good, or as you said, 'cleaner', performance, related to other methods, can be disasterous. It will require at 'best', a clustered index scan. Actually, unless there is an index on the datetime column, it has to scan the entire table -which is what a 'clustered index scan' really is.

Compare that with the second option, my preferred method, of using date values in the criteria.

Code Snippet

USE Northwind
GO

SELECT *
FROM Orders
WHERE datediff( day, OrderDate, '1996/08/27' ) = 0

SELECT *
FROM Orders
WHERE ( OrderDate >= '1996/08/27'
AND OrderDate < '1996/08/28'
)

If you examine the execution plans, you will notice the method using the datediff() takes 19 times as long to execute since it has to scan the entire table.

Datetime datatype conversion to int hhmmyy format

Is there a way of converting a datetime data type in the form [DD/MM/YYYY HH:MM:SS] to an integer containing just the time in the form [HHMMSS].SELECT REPLACE((CONVERT(VARCHAR,GETDATE(),24)),':','')

This converts it to a varchar. You actually don't want to convert it to integer as it will drop off the leading zeros.

datetime convertion in SqlServer 2000

SqlServer 2000 with SP3. RepTime is a datetime in TableA. I ran the two commands beblow:

SELECT distinct CAST([RepTime] AS INT) FROM TableA

39004
39002
39003

select max(RepTime),min(RepTime) from TableA

2006-10-16 10:36:03.940 2006-10-13 17:32:00.080

From 2006-10-13 to 2006-10-16, there are four days. But I got three distinct int from it. Anyone knows ?

Maybe you have two entries having the same date but different times?

DECLARE @.test AS TABLE (dat datetime);

INSERT into @.test values ('2006-12-19')
INSERT into @.test values ('2006-12-20')
INSERT into @.test values (GETDATE())

-- This retrieves 3 columns
SELECT DISTINCT dat FROM @.test

-- This retrieves 2 columns
SELECT DISTINCT CAST(dat AS INT) FROM @.test

That's because converting to INT don't care of Hours/Minutes/Seconds, just using the Days to separate.

|||

Very interesting while dig in to your issue..

SQL Server Converts the date using the following logic...

Declare @.Date as Datetime
Select @.Date = '2006-10-16 10:00:00'
select Cast(@.Date as Int) -- Result : 39004

Select @.Date = '2006-10-16 11:59:59'
select Cast(@.Date as Int) -- Result : 39004

Select @.Date = '2006-10-16 12:00:00'
select Cast(@.Date as Int) -- Result : 39005 (expected 39004)

Select @.Date = '2006-10-16 23:00:00'
select Cast(@.Date as Int) -- Result : 39005 (expected 39004)

Select @.Date = '2006-10-17 10:00:00'
select Cast(@.Date as Int) -- Result : 39005

Understood, the Integer cast will take one day from 12:00:00 PM to 11:59:59 AM (Strange Buddy = Bcs, after 12AM the value will be >= 39004.5 when the float number converted to integer it will round off the value...)

To overcome this issue use the following query..

SELECT distinct CAST(Convert(Datetime,Convert(Varchar,[RepTime],101)) AS INT) FROM TableA

Here we are omiting the Time field completly..

|||

Lucky P wrote:

That's because converting to INT don't care of Hours/Minutes/Seconds, just using the Days to separate.

Nope... Time is big concern here Lucky P

|||

You're completely right...

When converting to a Decimal Value, the Date '2006-10-16 12:00:00' is retrieved as 39004.5, which is rounded up to 39005 when converting it to INT.....

I made the mistake because im running the query before 12:00

|||

Yes absolutly it is round off issue... ..

So we can overcome this issue by the following query ..

SELECT distinct Cast(Round(CAST([RepTime] AS Float),0,2) as INT) FROM TableA

|||I got it. I made the same mistake too. Thanks to you all!|||

I use this one now:

SELECT distinct floor(cast([RepTime] as float)) FROM TableA

Datetime Conversion Question

If I have a variables that are declared as

@.Start_Year int

@.Start_Month int

How do I convert those variables to datetime format of

MM/1/YYYY

If what you want is datetime:

select convert(datetime, convert(varchar(2), @.start_month)

+ '/1/' + convert(char(4), @.start_year))

If what you want is a formated string in which the month contains a potential leading zero:

select right('0' + convert(varchar(2), @.start_month), 2) +

'/1/' + convert(char(4), @.start_year)

DATEADD with 8 byte int?

I have a bigint column called "MillisecondsSince1970" that I need to convert to a date - SSIS is erroring out when I use DATEADD with the 8 byte int (if I use 4 byte it works but the column is bigger than 4 byte). The error is really lame:

[Derived Column [79]] Error: The "component "Derived Column" (79)" failed because error code 0xC0049067 occurred, and the error row disposition on "output column "Date" (100)" specifies failure on error. An error occurred on the specified object of the specified component.

Anyone have a way around it... a VB.NET equivalent of DATEADD or something else I can do?

Why not do it in stages? Convert milliseconds to say, days, and then add that to your date via dateadd. Then take the remaining milliseconds and add to the new date field. For each step, cast to INT and then pass into dateadd.

So, 12636598775 milliseconds = 146 days, 22198775 milliseconds

In a derived column, for instance:
days = (DT_I4)(DT_NUMERIC, 30, 8)([millisecond_column] / 1000 / 60 / 60 / 24)

In a second derived column:
newdatecolumn = dateadd("d", [days], [date_column])
finaldatecolumn = dateadd("ms", (DT_I4)([millisecond_column] - ([days] * 24 * 60 * 60 * 1000)),[newdatecolumn])|||Phil that is an excellent idea... I kept trying ways to do it in one object - didn't think about doing it like that. (FYI VB.NET doesn't have milliseconds as a DateInterval so you can't do it in script without doing something like phil suggests)