Converting datetime in a string

Hi there,

Can anyone help?

I am trying to convert datetime into a string in the following format


2003-10-09 13:23:15.967



Current syntax:



SELECT '"' + CAST(t.Stamp AS varchar(30)) + '"' AS 'Start'

Any one any ideas?

THANKYOU!!!!

Melanie :)Try this:

select convert(varchar,getdate(),121)sqlsql

Converting a SQL Query to a Web page

I have a web site, and want to create a query on it using my current SQl database information, can someone explain to me how this is done? Or just send me a link to where I can get the information.
ThanksI am not entirely sure what you mean. Do you wish to
execute a query from the web site to retrieve data from
the database or send an update query to the database? Or
something else? Also, which server side technology are
you using for the web site? (PHP, ASP, ASP.NET, etc)
David Grant
>--Original Message--
>I have a web site, and want to create a query on it
using my current SQL database information, can someone
explain to me how this is done? Or just send me a link
to where I can get the information.
>Thanks
>.
>

Convert to Julian Date

In Oracle, we can write query to convert current date to Julian date
SELECT TO_CHAR(TRUNC(SYSDATE),'J') FROM DUAL;
This returns the value 2453754
I think there is no function in SQL Server to convert to Julian date
How do I use the same logic in SQL Server?
MadhivananMadhivanan wrote:
> In Oracle, we can write query to convert current date to Julian date
> SELECT TO_CHAR(TRUNC(SYSDATE),'J') FROM DUAL;
> This returns the value 2453754
> I think there is no function in SQL Server to convert to Julian date
> How do I use the same logic in SQL Server?
> Madhivanan
Try this:
DECLARE @.dt DATETIME ;
SET @.dt = CURRENT_TIMESTAMP ;
SELECT
DATEDIFF(DAY,'17530101',@.dt) +2361330.5
+DATEDIFF(SECOND,DATEADD(DAY,
DATEDIFF(DAY,'17530101',@.dt),'17530101')
,@.dt)/86400.00
AS julian_date ;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||CORRECTION. Use SET @.dt = GETUTCDATE() instead of CURRENT_TIMESTAMP.
Julian dates are always expressed relative to UTC and not local time.
If you supply your own date and time you'll have to convert it to UTC
yourself.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Hi,
DECLARE @.JulianDate AS INT
select @.JulianDate = 2415021 + datediff(dd, convert(datetime,
'1900-01-01',103),getdate())
PRINT @.JulianDate
This will give the julian date and to get back the current data,
select dateadd(dd, @.JulianDate-2415021, convert(datetime, '1900-01-01',103
))
Thanks
Sree
"Madhivanan" wrote:

> In Oracle, we can write query to convert current date to Julian date
> SELECT TO_CHAR(TRUNC(SYSDATE),'J') FROM DUAL;
> This returns the value 2453754
> I think there is no function in SQL Server to convert to Julian date
> How do I use the same logic in SQL Server?
> Madhivanan
>|||Thans David,
You used the value 17530101. Is this the number of days between January
1, 4713 BC and January 1, 1900?
Sree,
What does the value 2415021 refer to?
Madhivanan|||In SQL Server the datatype DateTime can store dates in the range - January
1, 1753 through December 31, 9999
HTH. Ryan
"Madhivanan" <madhivanan2001@.gmail.com> wrote in message
news:1137583289.974927.92200@.g44g2000cwa.googlegroups.com...
> Thans David,
> You used the value 17530101. Is this the number of days between January
> 1, 4713 BC and January 1, 1900?
> Sree,
> What does the value 2415021 refer to?
> Madhivanan
>|||Madhivanan wrote:
> Thans David,
> You used the value 17530101. Is this the number of days between January
> 1, 4713 BC and January 1, 1900?
> Sree,
> What does the value 2415021 refer to?
> Madhivanan
'17530101' just represents January 1, 1753, which is just the earliest
date supported by SQL Server. I used that as a base date for
calculating the Julian date. 2361330.5 is the Julian date for midnight
on 1753-01-01.
In Sree's case 2415021 is the Julian date for 12 noon on 1900-01-01.
Sree's calculation ignores the time portion of the current time and
returns the Julian day number for 12 noon on that day.
As I explained in my correction, Julian dates refer to UTC (GMT) time
so it's not really correct to use GETDATE() or CURRENT_TIMESTAMP as the
input to this calculation. You can use GETUTCDATE instead. In practice
if you are only interested in the integer day number then whether you
choose to base it on local time or UTC may depend on your requirements
and on how the value is to be used.
Hope this helps.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Its the Julian Date equivalent to "1900-01-01 00:00:00.000".
Julian date is the number of days that have elapsed since "1900-01-01
00:00:00.000". So the logic here used is simple, add Julian equivalent of
"1900-01-01 00:00:00.000" ie "2415021" to number of days from "1900-01-01
00:00:00.000" to current data(getdate()).
Hope you got the idea.
Thanks,
Sree
"Madhivanan" wrote:

> Thans David,
> You used the value 17530101. Is this the number of days between January
> 1, 4713 BC and January 1, 1900?
> Sree,
> What does the value 2415021 refer to?
> Madhivanan
>|||Thanks David and Sree
How did you calculate the value 2361330.5 for the date 1753-01-01?
Madhivanan|||Madhivanan wrote:
> Thanks David and Sree
> How did you calculate the value 2361330.5 for the date 1753-01-01?
> Madhivanan
http://aa.usno.navy.mil/data/docs/JulianDate.html
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Convert Time?

How can I get the current time in 12H format?
I tried convert(nvarchar,GetDate(),108) changing the 108 to other things but
I cant seem to find the right one. Changing Regional Options in control
panel had no affect..
Thnaks
BUCbuc wrote:
> How can I get the current time in 12H format?
> I tried convert(nvarchar,GetDate(),108) changing the 108 to other
> things but I cant seem to find the right one. Changing Regional
> Options in control panel had no affect..
> Thnaks
> BUC
select convert(varchar(20), getdate(), 100)
David Gugick
Imceda Software
www.imceda.com|||109 should do it... mon dd yyyy hh:mi:ss:mmmAM (or PM)
Check Books Online under CONVERT for details.
Paul
"buc" wrote:

> How can I get the current time in 12H format?
> I tried convert(nvarchar,GetDate(),108) changing the 108 to other things b
ut
> I cant seem to find the right one. Changing Regional Options in control
> panel had no affect..
> Thnaks
> BUC
>
>

Convert date to year

Hi,
I'm using sql 2000, and I like to know how to convert current date to year
(eg. 2006).
Thanks,
Sarahhow about year() function?
select year(getdate())
alternatively
select datepart(year, getdate())
peter
"Sarah" <sguo@.coopervision.com> wrote in message
news:Ozv2yxQSGHA.4752@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I'm using sql 2000, and I like to know how to convert current date to year
> (eg. 2006).
> Thanks,
> Sarah
>|||like this
SELECT DATEPART(yyyy,GETDATE())
or like this
SELECT DATEPART(yyyy,CURRENT_TIMESTAMP)
http://sqlservercode.blogspot.com/|||select YEAR(getdate())
"Sarah" <sguo@.coopervision.com> wrote in message
news:Ozv2yxQSGHA.4752@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I'm using sql 2000, and I like to know how to convert current date to year
> (eg. 2006).
> Thanks,
> Sarah
>|||Dear all,
Thanks all for your quick response. Year function works fine.
Thanks so much again,
Sarah
"Sarah" <sguo@.coopervision.com> wrote in message
news:Ozv2yxQSGHA.4752@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I'm using sql 2000, and I like to know how to convert current date to year
> (eg. 2006).
> Thanks,
> Sarah
>

convert date issue in view

i have created a view to a table (90,000+ records) that i want to use to filter the table by the current year and month. the code looks like this:

CREATE VIEW [billy.bhuj].[bo current month]

AS SELECT [dbo].[bo].[recnum], [dbo].[bo].[queue],
[dbo].[bo].[queue_name], [dbo].[bo].[node],
[dbo].[bo].[interval], [dbo].[bo].[tot_calls],
[dbo].[bo].[calls_less_20_sec], [dbo].[bo].[calls_more_20_sec],
[dbo].[bo].[calls_abandon], [dbo].[bo].[abandon_before_20_sec],
[dbo].[bo].[abandon_after_20_sec],
[dbo].[bo].[queue_date],
month (convert (datetime,[dbo].[bo].[queue_date], 103)) as QDate,
year (convert (datetime,[dbo].[bo].[queue_date], 103)) as QDate1,
convert (datetime,[dbo].[bo].[queue_date], 103) as QDate2,
year (convert (datetime,(getdate()), 104)) as year1,
[dbo].[bo].[region], [dbo].[bo].[queue_type],
[dbo].[bo].[month],
[dbo].[bo].[unit],
[dbo].[bo].[service], [dbo].[bo].[reportable], [dbo].[bo].[source_dest],
[dbo].[bo].[file_name]

FROM [dbo].[bo]

Where (year (convert (datetime,[dbo].[bo].[queue_date], 104)))
= (year (convert (datetime,(getdate()), 104)))

the syntax checks fine, and without the "where" clause , i get all the original data returned no problem, so the "month", "year", and "convert" functions work fine. however, when i try to filter the data with the "where" clause above, i get about 15-20 lines of data returned and an error message referring to "Arithmetic overflow...". on their own in the "select" area the statements do what they should, but in the "where" statement they don't. sorry, i'm a big time newbie in sql, so any help would be appreciated.

hi,

are your trying to do something like a parameterized view

well if you are you should be creating stored procedure

rather than a view

regards,

joey

|||

sorry, my bad...

i re checked the results without the "where" clause, and i was getting an error code further down the result list! same Arithmetic Overflow" error! i removed all the "convert" lines and it runs okay.

bo.queue_date is a nvarchar. i am using "convert" to change it to a date field. i do not own the table where bo.queue_date originates.

so how do i convert from a nvarchar field to a date field, and then filter out the dates i don't want in the "where" clause? should i be using "cast" instead of "convert"? or could this be a result of improper data in the bo.queue_date of the original table?

i am not trying to pass a parameter, just filter the main table down to a more manageable size by filtering by current month and year. if a stored proc makes more sense to do this though, i could try that instead...

|||

How do you know if the values in queue_date can be successfully converted to datetime? Is it always in one particular format? Why is that column nvarchar instead of datetime or smalldatetime? You could do check like below and then convert to prevent the conversion error:

select ...

, year(t.q_dt)

, month(t.q_dt)

from (

select ...

, case isdate(bo.queue_date) when 1 then convert(datetime, bo.queue_date, 104) end as q_dt

from bo

) as t

where year(t.q_dt) = year(CURRENT_TIMESTAMP)

But I don't think this is the problem. You talked about arithmetic overflow error which is different. Is bo a table or view? If bo is a view then you need to check the SELECT statement of the bo to see if there are any expressions that can result in arithmetic overflow. Also, please post the exact error message when asking for help.