|
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
|
|
|
|
|
Do you have static number of subjects?
Are only those subjects: english history math or you have and any other subjects?
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
no all subjects are dynamic
they can be increased or decreased
|
|
|
|
|
Sounds like you should do some research on PIVOT and UNPIVOT operators.
Mika
|
|
|
|
|
Hi Monu Khan,
ZAK here... ya i do understand your problem... But i strongly suggest you to learn atlest basics of SQL and also have handson training before you come to conclusion that you don't know how....
The question you asked is a bignners levfel one.. This time iam answering you.. But remember "The more you learn... The more you grow..."
Creating View:
**************
CREATE VIEW studentView
AS
SELECT rollno, english, math, history, total/250 AS NetTotal FROM marksheet
Executing View:
***************
Execute studentView
Try this out monu khan
All the best!!!
ZAK
|
|
|
|
|
hi. I am developing an IS and I was wondering on how I can load the heavy amount of data in a more efficient way. I was asked to also integrate an algorithm but I don't know where to put it and what to use. The program does much more in SQL queries than C# algorithmic codes. If you know what I mean. To explain it further, if I have about 1 million data entries in my database. How can I possibly load them in a jiffy?
|
|
|
|
|
The quickest way to load them is bcp, but I doubt you will load 1 million records 'in a jiffy' what ever method you use 
Bob
Ashfield Consultants Ltd
|
|
|
|
|
what's BCP? 
|
|
|
|
|
Its a bulk loading utility. Ty BOL or Google. Its the fastest way t get records from a file into a SQL Server database.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
hello everybody in this forum
i would like to know what is the appropriate solution for the current case in hand
i have two tables one called userinformation and the other called accountinformation
userinformation
================
id int not null primary key ,
firstname varchar(50) not null,
lastname varchar(50) not null,
email varchar(50) not null
accountinformation
===================
id int not null primary key ,
username varchar(50) not null,
password varchar(50) not null
now there is a relationship between the userinformation table and the accountinformation table (one to one) relationship where id in the userinformation refers to the id in the other table (accountinformation) now there are problems when inserting data into both tables like the following
The INSERT statement conflicted with the FOREIGN KEY constraint "LeftHalfHasMatchingRight". The conflict occurred in database "Sandbox", table "dbo.RightHalf", column 'PairID'.
The statement has been terminated.
now i tried to solve this problem by creating a stored procedure and within that stored procedure i created a transaction in it there is two insert statements like the following
<br />
create procedure adduser (@first varchar(50) , @last varchar(50) , @email varchar(50) , @user varchar(50) ,@pass varchar(50))<br />
as<br />
<br />
begin tran<br />
<br />
insert into userinformation (firstname , lastname , email ) values ('bla','bla','bla')<br />
insert into accountinformation (username , password ) values ('bla','bla')<br />
<br />
<br />
commit<br />
Go<br />
it works just fine but if i tried to insert single record in one of the two tables and try to execute this stored procedure with a new different records for these tables it gives me the same error message because there is a record in the userinformation without referential integrity into the other table which is the accountinformation table .
So please tell me how can i solve this current problem in hand
thank you very much
Human knowledge belongs to the world.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
![Rose | [Rose]](https://codeproject.global.ssl.fastly.net/script/Forums/Images/rose.gif)
