datetime Issue

i have a datetime field that i'm just storing the date like 5/17/2005.
i need to add the time like 10:30 PM to it. Should I store the time in
another field and concatenate? Right now, i just have a calendar date picker
on user form and will add time drop down fields to handle time.
any suggestions?I would recommend storing both the Date and Time in the one column since
combined they represent a single instance in time. Let the GUI, business or
data
layers combine the data into a single DateTime value.
Thomas
"scott" <sbailey@.mileslumber.com> wrote in message
news:e4%23rIu$WFHA.2448@.TK2MSFTNGP12.phx.gbl...
>i have a datetime field that i'm just storing the date like 5/17/2005.
> i need to add the time like 10:30 PM to it. Should I store the time in ano
ther
> field and concatenate? Right now, i just have a calendar date picker on us
er
> form and will add time drop down fields to handle time.
> any suggestions?
>|||>> i have a datetime field [sic] that i'm just storing the date like
5/17/2005.<<
No, you are not. If it were a field, then you can put anything into
it, but a column (which is nothing like a field) has a data type, and
the DATETIME data type includes year, month, day, hour, minute, second
and fractional second.
Also, that string you showed has nothing whatsoevdr to do with the
storage of temporal data. That is display and display is a task for
the front end, not the database. You might also want to start using
ISO-8601 temporal formats instead of "local dialect" -- this Internet
thing might catch on, you might need to exchange data, avoid errors,
etc.|||> You might also want to start using
> ISO-8601 temporal formats instead of "local dialect"
That is interesting! Joe suggests using non-ANSI SQL compliant elements. IS
O 8601 is
'2004-05-23T14:12:23', and this format is not supported in ANSI SQL. In ANSI
SQL, you need to
replace the T with a blank, but that makes it language and datetime dependen
t in SQL Server.
Scott, for datetime format recommendations, I suggest you check out:
http://www.karaszi.com/SQLServer/info_datetime.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1116457963.195486.21040@.g44g2000cwa.googlegroups.com...
> 5/17/2005.<<
> No, you are not. If it were a field, then you can put anything into
> it, but a column (which is nothing like a field) has a data type, and
> the DATETIME data type includes year, month, day, hour, minute, second
> and fractional second.
> Also, that string you showed has nothing whatsoevdr to do with the
> storage of temporal data. That is display and display is a task for
> the front end, not the database. You might also want to start using
> ISO-8601 temporal formats instead of "local dialect" -- this Internet
> thing might catch on, you might need to exchange data, avoid errors,
> etc.
>

DateTime format

Hi in my table I have a field for storing date and datetime

i want to get date from that table using select query...

for eg select dt from tname...

Here i want to display only the date not with time.

And also time alone excluding with date...

ThanxHi ArunBala!

Convert function can be used for separating date and time from the datetime field. And for your reference use the link given below

DateTime Format Link
-----------
http://sqljunkies.com/HowTo/6676BEAE-1967-402D-9578-9A1C7FD826E5.scuk

Here I have used table name satuserpost, fieldname Ndate

Query for selecting the date only from a datetime field
---------------------
select convert(varchar(10),Ndate,105)'My Date' from satuserpost

Query for selecting Time from a datetime field
----------------------
select convert(varchar(10),Ndate,108)'My Time' from satuserpost

All the Best

With Regards
Vijay. R|||Hi
Thanx for your reply

I have problem with it..
That is
select convert(varchar(10),Ndate,105)'My Date' from satuserpost where ndate='2007-12-15'

The above query returns no results...

WIthout where condition it is working. I want to select particular date or time..

How to do so?

Quote:

Originally Posted by VijaySofist

Hi ArunBala!

Convert function can be used for separating date and time from the datetime field. And for your reference use the link given below

DateTime Format Link
-----------
http://sqljunkies.com/HowTo/6676BEAE-1967-402D-9578-9A1C7FD826E5.scuk

Here I have used table name satuserpost, fieldname Ndate

Query for selecting the date only from a datetime field
---------------------
select convert(varchar(10),Ndate,105)'My Date' from satuserpost

Query for selecting Time from a datetime field
----------------------
select convert(varchar(10),Ndate,108)'My Time' from satuserpost

All the Best

With Regards
Vijay. R

|||

Quote:

Originally Posted by arunbalait

Hi
Thanx for your reply

I have problem with it..
That is
select convert(varchar(10),Ndate,105)'My Date' from satuserpost where ndate='2007-12-15'

The above query returns no results...

WIthout where condition it is working. I want to select particular date or time..

How to do so?

your posted syntax is verifably correct and should be returning an italian (105) converted date type value as varchar
What does your data look like in SQL server when you return * from ndate do you have your field set as datetime datatype

select convert(varchar(10),Ndate,105) [My Date] from satuserpost where Ndate='2007-12-15'

Jim :)|||

Quote:

Originally Posted by Jim Doherty

your posted syntax is verifably correct and should be returning an italian (105) converted date type value as varchar
What does your data look like in SQL server when you return * from ndate do you have your field set as datetime datatype

select convert(varchar(10),Ndate,105) [My Date] from satuserpost where Ndate='2007-12-15'

Jim :)

Hi...

Thanks

Ya my data format is datetime datatype...

select convert(varchar(10),Ndate,105) [My Date] from satuserpost where Ndate='2007-12-15'

the above query gives 0 rows affected...

Help me plz... I want to take report on particular date... If i have given where condition it doesnt affect any values...

I want the result for

select username from mytable where dt='2007-12-18'

dt= field name in my table.. it is in datetime datatype..

thanx