|
i don´t know what you have in mind but if you trigger a SP to add data to one table, edit in that SP a if/else statement that counts the table records and; 1) add a record or 2) the display a error info message to the user. 
|
|
|
|
|
Dear All,
I have a date values stored as Decimal in the SQL server 2005.
I need to convert these decimal values into Date in the format (yyyy-mm-dd).
Example: I need to convert 20080721 To 2008-07-21
I wrote the following statement for this purpose, but the result contains 00:00:00:00 in the end, and I think this is because I am using datetime function in the query.
select convert(datetime, cast(DayDate AS char(8))) AS Date
from TableName where DayDate = 20080721
Result from the previous query is: 2008-07-21 00:00:00:00, but I need it to be 2008-07-21 without 00:00:00:00
Kind Regards
OBarahmeh
|
|
|
|
|
hi,
well as far as my experience goes, whenever u user datetime in SQL Server it always appends a default time, -- even if u're not specifying it.
So I suggest u should stick to this result and try and improve the logic of ur front end to compensate to that.
Hope it helps,
Regards,
Rocky
Success is a ladder which you can't climb with your hands in your pockets.
|
|
|
|
|
Rocky# wrote: whenever u user datetime in SQL Server it always appends a default time
No it doesn't. You need to use GETDATE() or a fixed date if you don't supply a date into a non-nullable database field.
[EDIT]Rocky# is right - we are talking at cross purposes here, and he's entirely right to say that there is no date only type in SQL Servers up to 2005. 2K8 has a date type which stores the date only, but datetime does what it says on the tin and has the time portion as part of it.
My apologies for any confusion caused by my misreading of Rocky#'s post.[/EDIT]
modified on Wednesday, July 23, 2008 7:08 AM
|
|
|
|
|
my point is that datetime field always has a time part in it. try
Select Getdate()
or may be we're not on the same page at this point 
Rocky
Success is a ladder which you can't climb with your hands in your pockets.
|
|
|
|
|
 No, no, no. You're just plain wrong.
GETDATE() is a system function, not field data. It's no different in effect to you typing SELECT 3. This doesn't mean that every integer field defaults to 3. Tell you what, as a test, run the following script:
USE [<<database>>]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].Test(
[ID] [int] IDENTITY(1,1) NOT NULL,
[Text] NVARCHAR(20) NOT NULL,
[Created] [datetime] NOT NULL,
CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
)
GO
INSERT INTO Test([Text]) VALUES ('Hello')
GOGuess what. Created doesn't get a default value. What you do get is an error:
Msg 515, Level 16, State 2, Line 2
Cannot insert the value NULL into column 'Created', table 'database.dbo.Test'; column does not allow nulls. INSERT fails.
The statement has been terminated.
So no default value there then. Please, before you pass information like this along, will you please check to see if you are right?
[EDIT]Rocky# is right - we are talking at cross purposes here, and he's entirely right to say that there is no date only type in SQL Servers up to 2005. 2K8 has a date type which stores the date only, but datetime does what it says on the tin and has the time portion as part of it.
My apologies for any confusion caused by my misreading of Rocky#'s post.[/EDIT]
modified on Wednesday, July 23, 2008 7:12 AM
|
|
|
|
|
Pete O'Hanlon wrote: Guess what. Created doesn't get a default value. What you do get is an error:
what else would it give, trying to insert a null into a not null column,
try this
Insert into Test(text, created)
values ('my test', CAST('7/23/2008' as datetime))
The result looks like this on my end: 7/23/2008 12:00:00 AM
My point was simple, if u r getting a dateTime object, it will ALWAYS show the time, if u dont wanna see the time then cast it to varchar or something but datetime will always show u the time. here in the above query I'm not giving the time but its still comming. Thats the simple point I was talking abt. Pardon me if this doesnt satisfy the guys original question but my point was this alright! ![Rose | [Rose]](https://codeproject.global.ssl.fastly.net/script/Forums/Images/rose.gif)
I deeply respect ur place Pete, I dont mean any offense alright
Rocky
Success is a ladder which you can't climb with your hands in your pockets.
|
|
|
|
|
Crap. My apologies - I just reread your original post, and it seems you and I were talking about different things. So sorry - and I will amend my answers to reflect this.
I really shouldn't post answers late at night.
|
|
|
|
|
No problem Pete! 
Rocky
Success is a ladder which you can't climb with your hands in your pockets.
|
|
|
|
|
select substring(convert(varchar, convert(datetime, cast(DayDate AS char(8))),20),1,10) AS Date<br />
from TableName where DayDate = 20080721
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
I have a solution for your question hope this helps you out 
What you can do is that create a custom function in your Database, Code given below
Function:
*********
CREATE FUNCTION dbo.FormatDateTime
(
@dt DATETIME,
@format VARCHAR(16)
)
RETURNS VARCHAR(64)
AS
BEGIN
DECLARE @dtVC VARCHAR(64)
SELECT @dtVC = CASE @format
WHEN 'LONGDATE' THEN
DATENAME(dw, @dt)
+ ',' + SPACE(1) + DATENAME(m, @dt)
+ SPACE(1) + CAST(DAY(@dt) AS VARCHAR(2))
+ ',' + SPACE(1) + CAST(YEAR(@dt) AS CHAR(4))
WHEN 'LONGDATEANDTIME' THEN
DATENAME(dw, @dt)
+ ',' + SPACE(1) + DATENAME(m, @dt)
+ SPACE(1) + CAST(DAY(@dt) AS VARCHAR(2))
+ ',' + SPACE(1) + CAST(YEAR(@dt) AS CHAR(4))
+ SPACE(1) + RIGHT(CONVERT(CHAR(20),
@dt - CONVERT(DATETIME, CONVERT(CHAR(8),
@dt, 112)), 22), 11)
WHEN 'SHORTDATE' THEN
LEFT(CONVERT(CHAR(19), @dt, 0), 11)
WHEN 'SHORTDATEANDTIME' THEN
REPLACE(REPLACE(CONVERT(CHAR(19), @dt, 0),
'AM', ' AM'), 'PM', ' PM')
WHEN 'UNIXTIMESTAMP' THEN
CAST(DATEDIFF(SECOND, '19700101', @dt)
AS VARCHAR(64))
WHEN 'YYYYMMDD' THEN
CONVERT(CHAR(8), @dt, 112)
WHEN 'YYYY-MM-DD' THEN
CONVERT(CHAR(10), @dt, 23)
WHEN 'YYMMDD' THEN
CONVERT(VARCHAR(8), @dt, 12)
WHEN 'YY-MM-DD' THEN
STUFF(STUFF(CONVERT(VARCHAR(8), @dt, 12),
5, 0, '-'), 3, 0, '-')
WHEN 'MMDDYY' THEN
REPLACE(CONVERT(CHAR(8), @dt, 10), '-', SPACE(0))
WHEN 'MM-DD-YY' THEN
CONVERT(CHAR(8), @dt, 10)
WHEN 'MM/DD/YY' THEN
CONVERT(CHAR(8), @dt, 1)
WHEN 'MM/DD/YYYY' THEN
CONVERT(CHAR(10), @dt, 101)
WHEN 'DDMMYY' THEN
REPLACE(CONVERT(CHAR(8), @dt, 3), '/', SPACE(0))
WHEN 'DD-MM-YY' THEN
REPLACE(CONVERT(CHAR(8), @dt, 3), '/', '-')
WHEN 'DD/MM/YY' THEN
CONVERT(CHAR(8), @dt, 3)
WHEN 'DD/MM/YYYY' THEN
CONVERT(CHAR(10), @dt, 103)
WHEN 'HH:MM:SS 24' THEN
CONVERT(CHAR(8), @dt, 8)
WHEN 'HH:MM 24' THEN
LEFT(CONVERT(VARCHAR(8), @dt, 8), 5)
WHEN 'HH:MM:SS 12' THEN
LTRIM(RIGHT(CONVERT(VARCHAR(20), @dt, 22), 11))
WHEN 'HH:MM 12' THEN
LTRIM(SUBSTRING(CONVERT(
VARCHAR(20), @dt, 22), 10, 5)
+ RIGHT(CONVERT(VARCHAR(20), @dt, 22), 3))
ELSE
'Invalid format specified'
END
RETURN @dtVC
END
GO
Demo on Usage(Taking your own question as a sample):
*****************************************************
select dbo.FormatDateTime(convert(datetime, cast(DayDate AS char(8))),'YYYY-MM-DD') AS Date
from TableName where DayDate = 20080721
OutPut:
*******
2008-07-21
Usage of Function:
******************
DECLARE @now DATETIME
SET @now = GETDATE()
PRINT dbo.FormatDateTime(@now, 'LONGDATE')
PRINT dbo.FormatDateTime(@now, 'LONGDATEANDTIME')
PRINT dbo.FormatDateTime(@now, 'SHORTDATE')
PRINT dbo.FormatDateTime(@now, 'SHORTDATEANDTIME')
PRINT dbo.FormatDateTime(@now, 'UNIXTIMESTAMP')
PRINT dbo.FormatDateTime(@now, 'YYYYMMDD')
PRINT dbo.FormatDateTime(@now, 'YYYY-MM-DD')
PRINT dbo.FormatDateTime(@now, 'YYMMDD')
PRINT dbo.FormatDateTime(@now, 'YY-MM-DD')
PRINT dbo.FormatDateTime(@now, 'MMDDYY')
PRINT dbo.FormatDateTime(@now, 'MM-DD-YY')
PRINT dbo.FormatDateTime(@now, 'MM/DD/YY')
PRINT dbo.FormatDateTime(@now, 'MM/DD/YYYY')
PRINT dbo.FormatDateTime(@now, 'DDMMYY')
PRINT dbo.FormatDateTime(@now, 'DD-MM-YY')
PRINT dbo.FormatDateTime(@now, 'DD/MM/YY')
PRINT dbo.FormatDateTime(@now, 'DD/MM/YYYY')
PRINT dbo.FormatDateTime(@now, 'HH:MM:SS 24')
PRINT dbo.FormatDateTime(@now, 'HH:MM 24')
PRINT dbo.FormatDateTime(@now, 'HH:MM:SS 12')
PRINT dbo.FormatDateTime(@now, 'HH:MM 12')
PRINT dbo.FormatDateTime(@now, 'goofy')
Hope this will help you out , if it is reply back yo!!!
My email ID; iloveuzak@yahoo.com
Bye take care
Regards,
Zahed
ZAK
|
|
|
|
|
As there is no *DATE* data type in SQL 2005, you cannot store a date in the form that you want.
The only options you have to store a date (and time) are DATETIME and SMALLDATETIME (see Data Types[^])
If you need only the day part of a date I suggest you to use SMALLDATETIME ...
DECLARE @d DECIMAL(15,6) ;SET @d = 20080721.150825
SELECT
@d AS [as DECIMAL]
, CAST( LEFT(@d ,8) AS SMALLDATETIME ) AS [as SMALLDATETIME]
, CAST( LEFT(@d ,8) AS DATETIME ) AS [as DATETIME]
, CONVERT( NVARCHAR(10), CAST( LEFT(@d ,8) AS DATETIME ) , 121 ) AS [as NVARCHAR(10)]
There are 10 kinds of people in the world: those who understand binary and those who don't.
|
|
|
|
|
|
a more appropriate solution would be to use the date data type
SELECT CONVERT(VARCHAR(20),CONVERT(DATETIME,'20080819'),106)<br />
SELECT CONVERT(VARCHAR(20),CONVERT(DATETIME,'20080819'),103)<br />
SELECT CONVERT(VARCHAR(20),CONVERT(DATETIME,'20080819'),110)<br />
SELECT CONVERT(VARCHAR(20),CONVERT(DATETIME,'20080819'),111)<br />
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
hi
how much File size (data)and File size (log)in sqlserver2005?
|
|
|
|
|
|
Sorry, what a ridiculous question.
You either take the defaults (which will show you are the time how much space) or you set your own sizes.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
I want to connect my reports with northwind database with simple putting bindingsource control and want to add tables
through dataexpert
thanks
|
|
|
|
|
What have you done so far?
"The clue train passed his station without stopping." - John Simmons / outlaw programmer
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
|
|
|
|
|
|
And what problem are you having?
|
|
|
|
|
I don't think he is having trouble, just wants someone to do it for him.
"The clue train passed his station without stopping." - John Simmons / outlaw programmer
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
|
|
|
|
|
hello sir/mam
i am a student and i am still learning.
i have a problem with views.
i have a table name marksheet as
rollno. subject mo max
1001 english 70 100
1001 math 68 100
1001 history 23 50
1002 english 50 100
1002 math 87 100
1002 history 43 50
now i want to make view like this
rollno. english math history total
1001 70 68 23 253/250
1002 50 87 43 180/250
please help me to do my college assingment
i am using ms-sql server 2000
thank in advance
|
|
|
|
|
We don't do your homework for you. However, we will help you in the right direction.
So, what have you tried already?
Tip: Can you do this as a SELECT statement? If so, show it.
|
|
|
|
|
i have to make a assingment on school managment
i have completed it but excepts this .
when we have to generate marksheet it works fine .the order is
roll no.1001
subject max mo
english 100 60
math 100 78
history 100 76
but when i have to show report as a list
i dont know what to do.
roll no. english history math total
1001 60 78 76 214/300
1002 50 40 60 150/300
i have to show this in a report im using c# and sqlserver
i think it can be done by view but how i dont understand
please help me
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
