|
Still an error:
The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart.
|
|
|
|
|
Ambertje wrote: I need my dates as string because I'm working with a Data warehouse DB.
Why would the date format be a problem in a Data Warehouse?
|
|
|
|
|
Are you looking for ISNULL(START_DATE , END_DATE) ?
|
|
|
|
|
Good morning to you all,
It's Start_Date and Start_Time that can be empty.
So I think ISNULL(Start_Date, Start_Time) is what I need.
How can I implement this in my code?
,Datediff(SECOND,
convert(datetime, [START_DATE] + ' ' + [START_TIME]),
convert(datetime, [END_DATE] + ' ' + END_TIME])) AS M_Time_Worked
Kind regards,
Ambertje
|
|
|
|
|
Ambertje wrote: So I think ISNULL(Start_Date, Start_Time) is what I need.
That is not how ISNULL works. You would need something like
ISNULL(Start_Date, EndDate) + ' ' + ISNULL(Start_Time, Endtime)
|
|
|
|
|
You are correct, but still it wont work like that.
Something was missing and after analasys I saw that the Start_Date field wasn't NULL but just empty.
So this is what I changed to make it work:
,Datediff(SECOND,
convert(datetime, ISNULL( NULLIF([START_DATE], '' ) , END_DATE) + ' ' + ISNULL( NULLIF([START_TIME], '' ) , END_TIME)),
convert(datetime, [END_DATE] + ' ' + [END_TIME])) AS M_Time_Worked
A big thank's to all for helping me and the responses where I learned from.
Kind regards,
Ambertje
|
|
|
|
|
You are correct but when I run this statement I get no values.
So I went on to try some more ways to make it work and this statement works:
, CASE WHEN [START_DATE] IS NULL OR [START_DATE] =
CAST(Replace(convert(date, END_DATE, 101),
CAST(Replace(convert(date, [START_DATE], 101),
But I can't integrate it in my DateDiff statement, can you help me please?
Ambertje
|
|
|
|
|
I have a little bit of SQL code and I am having trouble bringing it over to MSSQL. I don't even know where to being I know that the SELECT, FROM , GROUP BY and HAVING will be in the MSSSQL query.
However I know for a fact the 4th line will change into @param1 and @param2. If anyone would like to add some input into this would be nice.
Access Query
Select COUNT(') AS Result, Username AS UsernameInput, [Password]
FROM access
GROUP BY Username, [Password]
Having (COUNT(') = 1) AND (Username = ?) AND ([Password] = ?)
modified 24-Jan-15 16:04pm.
|
|
|
|
|
Change the single quote ' to an asterisk * in the count functions. eg count(*)
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Asalam Alaikum Friends;
need a help of ERD Fee Record keeper of whole year for a single student plz plz plz help
|
|
|
|
|
Please do not crosspost; use one forum only.
|
|
|
|
|
I'm still trying to work out what he is asking for! ERD, I think is Enterprise Resource something, I have no idea what an annual record keeper is in that context.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Google has: Entity Relationship Diagram. As to the rest, I suspect OP has not really thought the question out too well.
|
|
|
|
|
i need the name of the tables of fee management system database which store like below shap
Month | jan | feb | Mar|
------------------------
curren fee| 100 | 100 |
--------------------------
dues | 200 | 100 |
----------------------------
total | 300 | 200 |
--------------------------
paid | 200 | 50 |
-------------------------
balance | 100 | 150 |
---------------------------------------------
Description
---------------------------------------------------
admissionFee| tutionFee| examFee| otherFee | Total |
----------------------------------------------------
200 | 100 | 30 | 0 | 330 |
-----------------------------------------------------
sorry weak in presentation but hope this time you will understand
|
|
|
|
|
i need the name of the tables of fee management system database which store like below shap
Month | jan | feb | Mar|
------------------------
curren fee| 100 | 100 |
--------------------------
dues | 200 | 100 |
----------------------------
total | 300 | 200 |
--------------------------
paid | 200 | 50 |
-------------------------
balance | 100 | 150 |
---------------------------------------------
Description
---------------------------------------------------
admissionFee| tutionFee| examFee| otherFee | Total |
----------------------------------------------------
200 | 100 | 30 | 0 | 330 |
-----------------------------------------------------
sorry weak in presentation but hope this time you will understand
|
|
|
|
|
Since we have no idea what your "fee management system database" looks like, we cannot answer the question.
|
|
|
|
|
can i send u a picture sketch sir for better understanding
|
|
|
|
|
|
dost main ne jo sketch beja ta dar asal ye fee receipt ka style hai jo student ko diya jata hai school main to ese tarah main be try karta ho to moje tables our is ke attribute chaheya
|
|
|
|
|
Please post only in English in these forums.
|
|
|
|
|
Hi All
I am having sqlserver 2008R2 database, and in the table, there is a column "Text" with some string value.
Some of the strings are good (have the characters entered from keyboard" where some have values like "degree" symbol.
example:
Row 1 has string "Today temperature is 45Degrees C" where as Row 2 has string "Today temperature is 40°C
Row 2 has a symbol which is not entered through the keyboard. I want to filter such kind of symbols while querying.
Is there a way to query that way by filtering? If so, please provide me such query.
Thanks in advance,
Usha
Naina
|
|
|
|
|
That'd be rather slow; but you could loop through all chars, see if it falls within an expected range (say, ASCII upto 127), put that in a function, and call that from the query.
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
There are multiple ways to do it. i would suggest you should review your data to identify how many other invalid characters you have after this just use the simple REPLACE() function to remove those. Furthermore, if you are planing to create a function for this then you can look into this Link.
hope it helps.
|
|
|
|
|
Hello,
The code below works fine with SQL Server 2008
But it seems to be deleted from newer versions of SQL Server.
How can change it so it works fine for all versions ?
Thank you
IF EXISTS (SELECT * FROM sys.server_principals WHERE name = N'sac_manager')
exec sp_droplogin 'sac_manager';
exec sp_addlogin @loginame = N'sac_manager', @passwd = N'', @defdb = N'SAC_DB';
USE [SAC_DB]
exec sp_adduser 'sac_manager'
exec sp_addrolemember 'db_owner', 'sac_manager';
|
|
|
|
|
Which part?
There is no separate set of commands that work on "all" versions. You'll have to find the part that is deprecated and find some replacement.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
